Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Using Work Variables in COALESCE

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
PL1user

New User


Joined: 26 Nov 2008
Posts: 13
Location: UK

PostPosted: Wed Dec 17, 2008 8:31 pm    Post subject: Using Work Variables in COALESCE
Reply with quote

Hi,

I am trying to use COLAESCE in one of my select SQLs. I would explain the problem i am facing with an example,

Code:
SELECT COALESCE(MAX(MARK),100)           
FROM   <DB2TABLE>
WHERE  NAME = :WS_NAME


The above SQL would work fine. It would return either MAX(MARK) or 100.

My question is, if I have 100 in a working storage variable(e.g. WS_SCORE), would I be able to do something like,

Code:
SELECT COALESCE(MAX(MARK),:WS_SCORE)           
FROM   <DB2TABLE>
WHERE  NAME = :WS_NAME


I think the above SQL would not work, as I get SQLCODE -171. But can anyone let me know if I can pass :WS_SCORE like above or in any other way using COALESCE?

Thanks.
Back to top
View user's profile Send private message

ikumar

New User


Joined: 02 Aug 2005
Posts: 81

PostPosted: Thu Dec 18, 2008 11:15 am    Post subject: Reply to: Using Work Variables in COALESCE
Reply with quote

Why don't you try it using host variable instead of WS variable...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Need help how to pass the ESP variabl... Venkichaitu JCL & VSAM 12 Thu Nov 02, 2017 3:53 am
No new posts Fileaid 2 commands instream work. In ... descann Compuware & Other Tools 2 Tue May 16, 2017 3:31 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 8 Wed Jan 25, 2017 8:24 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 0 Wed Jan 25, 2017 8:23 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 0 Wed Jan 25, 2017 8:21 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us