If you try to use function TO_NUMBER in SQL/400 you will get "Token TO_NUMBER was not valid". I didn't find way to to convert a string to a numeric in SQL/400 so I don't use SQL/400 for that. If someone know way it is interesting. Andrew Sereda -----Original Message----- From: Jromeh@aol.com [SMTP:Jromeh@aol.com] Sent: 31 yiaa?y 1998 a. 19:39 To: MIDRANGE-L@midrange.com 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. 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.
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact