I think you may be coming up against redirection issues with the '>'
symbol..

It looks like the shell is 'consuming' the >0 from the command line as
it thinks you want to redrect info to a file called >0.  I'm not sure in
windows if it can be escaped or quoted to avoid the issue.



-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Matthew Large
Sent: Wednesday, June 15, 2005 3:41 PM
To: ADSM-L AT VM.MARIST DOT EDU
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.



___________________________ 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. 

Powergen Retail Ltd is authorised and regulated by the Financial Services 
Authority for the sale and service of general insurance products.

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