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