Re: SQL string conversion

[Jromeh@xxxxxxx]

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.

A related, ugly problem: 
Extracting a packed or signed value embedded in a large character field 
by a HLL program using a data structure. Can get a character string 
representing the number out, but with (too) many function calls, could be 
possible with one. Then apply TO_NUMBER, and your vendor package with the 
"interesting" database is fully queryable. Should it be this hard?

MAP_PACKED and MAP_SIGNED functions sure would help here.

Jerome

P.S. Recently received a post saying buds are out on trees, it's spring 
already in NOLA! 

P.P.S. Desiring an advance listen to the varied sounds of New Orleans? 
Point your RealPlayer to www.wwoz.org. Sounds great from up nawth. And 
when you're there, check out 90.7.


/* -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- */
/* Jerome Hughes                        jromeh@aol.com                 */
/* InterPro Consulting Services         www.interproinc.com            */ 
/* Rosemont IL USA                      these are my own opinions only */
/* OMNI User Communications Director    www.omniuser.org               */
/* -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- -=- */
"With the breakdown of the Medieval system, the gods of Chaos, Lunacy, 
and Bad Taste gained ascendancy." -- written on a Big Chief tablet by 
Ignatius J. Reilly, in John Kennedy Toole's _A Confederacy of Dunces_


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