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
|