Re: SQL string conversion

["Simon Coulter" <shc@xxxxxxxxxxxxxxxxx>]

//--- forwarded letter -------------------------------------------------------
> X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.3 (16)
> Date: Mon, 02 Feb 98 10:45:33 +1100
> From: "Mark Lazarus" <mlazarus@ttec.com>
> To: MIDRANGE-L@midrange.com
> Reply-To: MIDRANGE-L@midrange.com
> Subject: Re: SQL string conversion

> 
> Simon,
> 
> At 12:57 AM 2/2/98 +1100, you wrote:
> 
> >>  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?
> 
> >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! 
> 
>  Yes it is!  I didn't know you can use an OPNQRYF ODP w/ SQL/400.  Thanks!
> 
>  -mark 
> 
 
Mark,

That's not quite what I said.  My suggestion is to use OPNQRYF to present the 
data to the program in 
the form requested.  I would use native I/O to read the OPNQRYF ODP.  I believe 
the update is to a 
second file (a proper database file) for which the updates can be done with 
native I/O or SQL/400.

My understanding of the original append is that PC data is being dumped into a 
flat file which is 
processed and written to a DB2/400 file.

While Query/400 will use an already open ODP (which can cause immense 
confusion) I'm pretty sure SQL 
ignores the shared override. It does acknowledge the OVRDBF command but ignores 
the SHARE parameter.

Note that the OPNQRYF *ZONED operator is different from the SQL ZONED function. 
 The *ZONED operator 
will accept character data provided the length of the character data is the 
same as the zoned 
representation and the character data can be interpreted as numbers.  

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