AW: select query misbehaving?

2005-02-23 Thread Thomas Rupp 
If the LOCATION isn't set then it is NULL.
So your select should look like:
select * from volhistory where (type='BACKUPINCR' or
type='BACKUPFULL') and location IS NULL

HTH
Thomas Rupp

-Ursprüngliche Nachricht-
Von: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] Im Auftrag von Warren, 
Matthew (Retail)
Gesendet: Mittwoch, 23. Februar 2005 11:49
An: ADSM-L@VM.MARIST.EDU
Betreff: select query misbehaving?


Hallo TSM'ers.


Am I going mad, or shouldn't this actually work?

tsm: CARSINGTONselect * from volhistory where (type='BACKUPINCR' or
type='BACKUPFULL')

 DATE_TIME   UNIQUE  TYPEBACKUP_SERIES
BACKUP_OPERATION  VOLUME_SEQ  DEVCLASSVOLUME_NAME
LOCATIONCOMMAND
--  ---  --  -
  --  --  --
--  --
2005-02-180  BACKUPFULL   1460
0   1  ULTRIUM2SW0130
   20:04:57.00
2005-02-180  BACKUPFULL   1461
0   1  ULTRIUM2_PHNX   43
   21:00:26.00
2005-02-190  BACKUPFULL   1462
0   1  ULTRIUM2SW0111
   20:04:29.00
2005-02-190  BACKUPFULL   1463
0   1  ULTRIUM2_PHNX   P20100
   21:08:38.00
2005-02-200  BACKUPFULL   1464
0   1  ULTRIUM2S20225
   20:03:40.00
2005-02-200  BACKUPFULL   1465
0   1  ULTRIUM2_PHNX   P20306
   22:34:30.00
2005-02-210  BACKUPFULL   1466
0   1  ULTRIUM2S20411
   20:03:45.00
2005-02-210  BACKUPFULL   1467
0   1  ULTRIUM2_PHNX   P20209
   21:07:10.00
2005-02-220  BACKUPFULL   1468
0   1  ULTRIUM2SW0077
   18:42:35.00
2005-02-220  BACKUPFULL   1469
0   1  ULTRIUM2_PHNX   20
   20:26:48.00

tsm: CARSINGTONselect * from volhistory where (type='BACKUPINCR' or
type='BACKUPFULL') and location!='VAULT'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

tsm: CARSINGTONselect * from volhistory where (type='BACKUPINCR' or
type='BACKUPFULL') and location not like '%VAULT%'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.


Surely the last two should return the same as the first one in this
case?
_-'-_
  -|-




___ Disclaimer Notice __
This message and any attachments are confidential and should only be read by 
those to whom they are addressed. If you are not the intended recipient, please 
contact us, delete the message from your computer and destroy any copies. Any 
distribution or copying without our prior permission is prohibited.

Internet communications are not always secure and therefore Powergen Retail 
Limited does not accept legal responsibility for this message. The recipient is 
responsible for verifying its authenticity before acting on the contents. Any 
views or opinions presented are solely those of the author and do not 
necessarily represent those of Powergen Retail Limited. 

Registered addresses:

Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4 
8LG.
Registered in England and Wales No: 3407430

Telephone +44 (0) 2476 42 4000
Fax +44 (0) 2476 42 5432

AW: select query misbehaving?

2005-02-23 Thread Thomas Rupp 
I'm no SQL guru but ...
!= 'VAULT' is a content comparison and would return records with blanks
or a string of length 0 as well.
IS NULL checks for a datafield that has never been set. You don't check
the content you check a characteristic.

So to be really sure to get all records that are != 'VAULT' you have to
check for both.

select * from volhistory where (type='BACKUPINCR' or
type='BACKUPFULL') and (location!='VAULT' or location IS NULL)

Thomas Rupp