ADSM-L

SQL query behaviour - then

2005-06-15 10:40:19
Subject: SQL query behaviour - then
From: Matthew Large <Matthew.Large AT AVIVA DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Wed, 15 Jun 2005 15:41:06 +0100
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.