how to detect an undefined data with sql ? - IBM AS400

This is a discussion on how to detect an undefined data with sql ? - IBM AS400 ; Hello, in a sql, how could I detect an undefined data ? For example, a integer data containing spaces or null values ? Something like : select * from myfile where mynumber = null select * from myfile where mynumber ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: how to detect an undefined data with sql ?

  1. how to detect an undefined data with sql ?

    Hello,

    in a sql, how could I detect an undefined data ?
    For example, a integer data containing spaces or null values ?

    Something like :
    select * from myfile where mynumber = null
    select * from myfile where mynumber is undefined
    select * from myfile where mynumber not numeric
    ....

    Thanks

    Jean-Claude


  2. Re: how to detect an undefined data with sql ?

    NULL is valid data and is easy:
    Select * from myfile where myfield IS NULL

    Strictly speaking, an integer could contain blanks, I think, since a blank
    is a X'40' so an integer field with a blank in the right byte would be
    decimal 64.

    You might try this for bad numeric data, i.e., packed or zoned, but it may
    not work in all cases:

    select * from myfile where decimal(char(myfield),9,0) <> myfield

    where myfield is a numeric 9,0 field.

    Hopefully this is a one-time cleanup and you've taken steps to eliminate
    keep future bad data.

    Sam

    "Jean-Claude" wrote in message
    news:46deeeba$0$5081$ba4acef3@news.orange.fr...
    Hello,

    in a sql, how could I detect an undefined data ?
    For example, a integer data containing spaces or null values ?

    Something like :
    select * from myfile where mynumber = null
    select * from myfile where mynumber is undefined
    select * from myfile where mynumber not numeric
    ....

    Thanks

    Jean-Claude



  3. Re: how to detect an undefined data with sql ?

    If your file is defined with SQL (CREATE TABLE) it will not contain
    any invalid numeric values. Because the values are checked (internally
    in the table) as soon as a row will be inserted. Invalid numeric
    values (and blanks are invalid) get rejected. (It is even not possible
    to insert invalid numeri values with CPYF and *NOCHK).

    In a DDS described file there may be invalid numeric variables (but
    only when CPYF with *NOCHK was used).

    NULL values can be used, but are outside the valid range. NULL values
    are stored in a so called NULL flag list and must be checked
    separately, that's why = will not work. To detect NULL-Values just use
    IS NULL in the where condition. (as Sam already suggested)

    If you want to change a NULL-Value to whatever default value, just use
    the scalar function COALESCE.

    Birgitta

    On 5 Sep., 20:00, "Jean-Claude" wrote:
    > Hello,
    >
    > in a sql, how could I detect an undefined data ?
    > For example, a integer data containing spaces or null values ?
    >
    > Something like :
    > select * from myfile where mynumber = null
    > select * from myfile where mynumber is undefined
    > select * from myfile where mynumber not numeric
    > ...
    >
    > Thanks
    >
    > Jean-Claude




  4. Re: how to detect an undefined data with sql ?

    Selection against a column with bad data, to check for what rows have
    bad data, is not possible because equivalence is indeterminate. The
    result of the type casting is a mapping error, and that results in a
    selection error. Selection is terminated for a mapping error regardless
    that the test is for non-equivalence in the given example; the query
    engine does not analyze the test to determine intent, even if it might
    seem intuitively obvious and logical to us humans :-)

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    Saml wrote:
    > <>
    > You might try this for bad numeric data, i.e., packed or zoned, but it may
    > not work in all cases:
    >
    > select * from myfile where decimal(char(myfield),9,0) <> myfield
    >
    > where myfield is a numeric 9,0 field.
    > <>


+ Reply to Thread