Re: SQL string conversion

Andrew:

Are you using Advanced Systems Concepts Sequel, or SQL/400?  Sequel supports
using ZONED to convert a character string to a number, SQL/400 does not.  My
comments that you quoted were about Sequel.

Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -
Bubba Makes Wheels :)
The opinions expressed may not be my employer's unless I'm sufficiently
persuasive...

-----Original Message-----
From: Andrew Sereda <avs@fuib.com>
To: 'MIDRANGE-Lmidrangecom' <MIDRANGE-L@midrange.com>
Date: Wednesday, February 04, 1998 1:07 PM
Subject: RE: SQL string conversion


>Dave
>After tring to use function ZONED (e. g. select ZONED('123') ... from ...
or
> select * from ... where ZONED('123')>0 ) I got error
>message :
>
> Message . . . . :   ZONED use not valid.
> Cause . . . . . :   A nonnumeric operand has been specified for the
arithmetic
>   function or operator ZONED. The function or operator requires numeric
>   operands.  The operand of DIGITS can be any numeric type except
>   floating-point.
> Recovery  . . . :   Ensure all operands of function or operator ZONED are
>   numeric.  If the function is DIGITS, ensure the argument is not
>   floating-point.  Correct the operands.  Try the request again.
>So I can not use ZONED for SQL string conversion.
>Also from Book-DB2/400 SQL Reference V3R1
>         3.2.51  ZONED
>You can see
> The ZONED function returns a zoned decimal representation of a number.
>The first argument must be a number
>
>Andrew Sereda
>First Ukrainian International Bank
>-----Original Message-----
>From: Dave Shaw [SMTP:dshaw1@InfoAve.Net]
>Sent: 2 oaa?aey 1998 a. 19:15
>To: MIDRANGE-L@midrange.com
>Subject: Re: SQL string conversion
>
>-----Original Message-----
>From: Jromeh@aol.com <Jromeh@aol.com>
>To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
>Date: Saturday, January 31, 1998 2:15 PM
>Subject: Re: SQL string conversion
>
>
>>Mark Lazarus asked...
>>
>>> Is there a way to convert a string to a numeric in SQL/400?  I have a
>file
>>>that is being uploaded from a PC.  The initial DB that it is being
>uploaded
>>>to has a single field.  The area in the record is being pre-screened to
>>>make sure it is numeric.
>>
>>> What I wanted to do is something like this:  SELECT * WHERE VAL( SUBST(
>>>DATA, 15, 7 ) ) > 100.  Or assign it to a numeric field an on update.  I
>>>realize that there is no VAL function.  Is there an equivalent function
or
>>>workaround?
>>
>>The TO_NUMBER function does exactly this, it's covered on p84-85 of
>>"Teach Yourself SQL in 21 Days" (Stevens, et al, SAMS Publishing, 2nd ed,
>>1997). The book uses Access, Personal Oracle7, MS Query, and ODBC for
>>examples.
>>
>>Never heard of such a thing in SQL/400, but get asked often and sure
>>could use it at times, especially when dealing with data from other
>>platforms.
>>
>>Is it there in ASC's SEQUEL, anyone? Seem to remember more control over
>>some things like this, though it's almost 3 years now since using it.
>
>
>ASC's Sequel doesn't support the TO_NUMBER function as such, but it does
>provide the ability to do this task.  There is a ZONED function which
>converts a (normally character) expression into an AS/400 zoned number,
>which can then be converted to packed or integer or floating point.  It's
>all very AS/400-eccentric and decidedly non-portable, of course.  It works
>great, though - we've used it several times.
>
>Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -
>Bubba Makes Wheels :)
>The opinions expressed may not be my employer's unless I'm sufficiently
>persuasive...
>
>
>root
>+---
>| This is the Midrange System Mailing List!
>| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
>| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator:
david@midrange.com
>+---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---