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" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > Internet e-mail: [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> > Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> > 09/02/2003 05:35 > Please respond to "ADSM: Dist Stor Manager" > > To: [EMAIL PROTECTED] > 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 >