SQL query behaviour

----- Forwarded by Matthew Large/CS/Aviva on 15/06/2005 16:14 -----

Matthew Large/CS/Aviva
15/06/2005 15:56

To
ADSM-L AT VM.MARIST DOT EDU
cc

Subject
SQL query behaviour - fixed





Right,

I see that the 'greater than zero' >0 seems to have disappeared, and a
file called 0 has appeared in my admin folder.
So it's redirected the concatenated output to the file 0 and continues,
when it finds 'then' and finds it a bit ridiculous.

I've discovered that if I encase from the 'select' to the end of the
redirection it seems to function.

Apologies if this was not interesting new news.

----- Forwarded by Matthew Large/CS/Aviva on 15/06/2005 15:48 -----

Matthew Large/CS/Aviva
15/06/2005 15:41

To
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
cc

Subject
SQL query behaviour - then





Hi,

I seem to be having some trouble getting results from select statements
issued from a batch script

Here're the results from TSM - it's quite happy..

tsm: TSML003A>select case  when sum(physical_mb) >0 then sum(physical_mb)
else 0
 end from occupancy where stgpool_name='APPS_DISKPOOL'
0.00

But when this same command issued from a batch script

set id=largem
set pw=...
set login=c:\progra~1\tivoli\tsm\baclient\dsmadmc -id=%id% -pa=%pw%

%login% select case when sum(physical_mb) >0 then sum(physical_mb) else 0
end from occupancy where stgpool_name='APPS_DISKPOOL' > tsmdisk.log

I get a...

IBM Tivoli Storage Manager
Command Line Administrative Interface - Version 5, Release 2, Level 2.0
(c) Copyright by IBM Corporation and other(s) 1990, 2003. All Rights
Reserved.

Session established with server TSML003A: Windows
  Server Version 5, Release 2, Level 0.0
  Server date/time: 15-06-2005 15:29:49  Last access: 15-06-2005 09:54:44

ANS8000I Server command: 'select case when sum(physical_mb) then
sum(physical_mb) else 0 end from occupancy where
stgpool_name='APPS_DISKPOOL''
ANR2904E Unexpected SQL key word token - 'THEN'.

                                           |
         ..................................V............................
         select case when sum(physical_mb) then sum(physical_mb) else 0


Since queries which result in a decimal 0 often produce extraneous text,
I've tried to convert the 'null' into an integer 0.

Is there any reason why my batch script fails like this?

Thanks
Matthew



Aviva plc
Registered Office: St. Helen's, 1 Undershaft, London EC3P 3DQ
Registered in England Number 02468686
www.aviva.com

This message and any attachments are confidential.
If you are not the intended recipient, please telephone
or e-mail the sender and delete this message and any
attachment from your system. Also, if you are not the
intended recipient you must not copy this message or
attachment or disclose the contents to any other person.