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