Andy,

Thanks for the explanation. I was totally unaware
of this behaviour and I assumed that it was sufficient
to test for the string.
Thanks again.

Kind regards,

Alexander

----- Original Message -----
From: "Andrew Raibeck" <storman AT US.IBM DOT COM>
To: <ADSM-L AT VM.MARIST DOT EDU>
Sent: Tuesday, September 02, 2003 5:04 PM
Subject: Re: Select on contact field doesn't find results


> Yes, this is correct SQL behavior.
>
> If you examine the COLUMNS table for this column, you will see that the
> CONTACT field supports null values:
>
> ============================================================
> tsm: STORMAN>select * from columns
>              where tabname='NODES' and colname='CONTACT'
>
>    TABSCHEMA: ADSM
>      TABNAME: NODES
>      COLNAME: CONTACT
>        COLNO: 6
> INDEX_KEYSEQ:
>  INDEX_ORDER:
>     TYPENAME: VARCHAR
>       LENGTH: 255
>        SCALE: 0
>        NULLS: TRUE
>      REMARKS: Contact
>
> tsm: STORMAN>
> ============================================================
>
> It is important to note that nulls do NOT represent zero or empty strings;
> rather, they represent unknown data. Because the data is unknown, a
> comparison involving a null column evaluates to an unknown condition,
> which is treated as "false". Thus the comparison of a null contact field
> to 'Alex%' results in "unknown", so no rows are returned.
>
> To test for null columns, use the "is null" or "is not null" operators,
> like this:
>
> ============================================================
> tsm: STORMAN>select node_name,contact from nodes
>              where node_name like 'BLAAT%' and
>              (contact not like 'Alex%' or contact is null)
>
> NODE_NAME              CONTACT
> ------------------     ------------------
> BLAAT2
>
> tsm: STORMAN>
> ============================================================
>
> Regards,
>
> Andy
>
> Andy Raibeck
> IBM Software Group
> Tivoli Storage Manager Client Development
> Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
> Internet e-mail: storman AT us.ibm DOT com
>
> The only dumb question is the one that goes unasked.
> The command line is your friend.
> "Good enough" is the enemy of excellence.
>
>
>
>
> Alexander Verkooijen <alexander AT SARA DOT NL>
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
> 09/02/2003 05:35
> Please respond to "ADSM: Dist Stor Manager"
>
>         To:     ADSM-L AT VM.MARIST DOT EDU
>         cc:
>         Subject:        Select on contact field doesn't find results
>
>
> Hello all,
>
> (I've searched the list archive and the server readme's
> to see if this a known problem but I can't find anything)
>
> Does anybody understand why the second select doesn't
> find the node BLAAT2 ?
> I must be missing something, but I've been staring
> at this for quite some time and I can't see what I'm
> doing wrong.
>
> tsm: XXXXXX>select node_name,contact from nodes where node_name like
> 'BLAAT%'
>
> NODE_NAME              CONTACT
> ------------------     ------------------
> BLAAT1                 Alexander
> BLAAT2
>
>
>
> tsm: XXXXXX>select node_name,contact from nodes where node_name like
> 'BLAAT%' \
> cont> and contact not like 'Alex%'
> ANR2034E SELECT: No match found using this criteria.
> ANS8001I Return code 11.
>
>
> Regards,
>
>
> Alexander
>
> ------------------------------------------------------------
> Alexander Verkooijen
> Senior Systems Programmer
> High Performance Computing
> SARA Computing & Networking Services
>