Re: testing a char field for numeri chars in SQL

You may not be out of luck.  After all, this is the AS/400 we are talking
about, the _Ultimate DataBase Machine_! :^)

Anything you define in a physical can be redefined in a logical.  If you
create a new logical over this physical, you can create these 3 positions
as a separate field, or even 3 separate fields if you wish.  I once had to
do this over a 6 long packed field which contained a date in the format
MMDDYY.  It's been a very long time, but, based on memory...

Give your new logical a new record format name that is different from the
physical file's record format.  Basically you are creating a new record
format definition for this file.

List all the fields from the physical file's record format (length &
decimal positions not necessary) in the same order as in the physical. 
Otherwise you won't be able to reference these fields when using this
logical.

In my case, for the packed numeric field, I needed to redefine the MMDDYY
field as the same length and number of decimals, but _Signed_.  Also, I
used the RENAME function and gave it a new name.  For an alpha field, you
probably don't have to rename.

At the bottom of your list of fields, but before your keys, add your new
field(s). 
Using a new field name, define as 3, with 'Use' set to I (input only, you
will not be able to directly update this new field).  
Use the substring function to break your 3 bytes out of the composite
field, like this: 
SST(compositename 2 3)

After that, you can reference this field directly with SQL or OPNQRYF or
whatever.  You can also use it as a key field.

You could use the OPNQRYF cmd's mapfld abilities, but if you think that
your SQL statement is unwieldy, then probably you wouldn't like the OPNQRYF
equivalent.

HTH,
kk
___________________
Kathleen Kostuck   
pager (414) 402-0820       fax (414) 495-4986
kkostuck@execpc.com
AS400 Solutions
___________________

----------
> From: James W. Kilgore <qappdsn@ibm.net>
> To: MIDRANGE-L@midrange.com
> Subject: Re: testing a char field for numeri chars in SQL
> Date: Thursday, April 09, 1998 1:37 AM
> 
> 
> 
> Qile wrote:
> 
> > Hello,
> >
> > I need to be able to test the second, third, & fourth characters of an
alpha
> > field to determine if they are numeric or not. E.g. I want to test for
the
> > format xnnnxxxxx.
> >
> 
> Paul,
> 
> My first gut reaction is that you are out of luck.
> 
> It appears that your data base has violated rule #1 of normalization and
has a
> composite field.  IMO it's easier to concatenate than it is to parse. 
Had the
> file been defined differently the 3 numeric positions would have been a
separate
> field.  After all they do appear to have some intrinsic meaning or
validity rules
> and should have been defined accordingly.
> 
> Sorry :(
> 
> +---
> | 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
> +---
+---
| 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
+---