Re: SQL string conversion

["Simon Coulter" <shc@xxxxxxxxxxxxxxxxx>]

//--- forwarded letter -------------------------------------------------------
> X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.3 (16)
> Date: Wed, 28 Jan 98 10:01:29 +1100
> From: "Mark Lazarus" <mlazarus@ttec.com>
> To: MIDRANGE-L@midrange.com
> Reply-To: MIDRANGE-L@midrange.com
> Subject: SQL string conversion

> 
>  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?
> 
>  -mark
> 

I don't believe SQL/400 supports TO_NUMBER although some other SQL dialects do. 
 You can however 
accomplish the task with OPNQRYF using the MAPFLD keyword:

        MAPFLD((NEWFLD '%sst(DATA 15 7)' *ZONED 7))

Then you can use the mapped field in the selection clause:

        QRYSLT('*MAPFLD/NEWFLD > 100')

Once the ODP has been built you can move NEWFLD to the target database field or 
host variable and 
perform the update.  The update can be performed with native I/O or SQL/400.

Isn't it great! An integrated database so I can use more than one way to skin 
the proverbial cat.

Note that because OPNQRYF supports this function the query engine also supports 
it therefore 
3rd-party tools which use a Query Definition Template (QDT) such as ASC Sequel 
should also be able 
to support this.  I'd guess that TO_NUMBER is not an ISO SQL function and 
therefore the AS/400 does 
not support it.

Regards,
Simon Coulter.

//----------------------------------------------------------
// FlyByNight Software         AS/400 Technical Specialists
// Phone: +61 3 9419 0175      Mobile: +61 3 0411 091 400
// Fax:   +61 3 9419 0175      E-mail: shc@flybynight.com.au
// 
// Windoze should not be open at Warp speed.
 

+---
| 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
+---