RE: SQL string conversion -- MIDRANGE-L

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
+---
application/ms-tnef