Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Stored procedure Case- End Case

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

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Tue May 15, 2007 9:06 pm    Post subject: Stored procedure Case- End Case
Reply with quote

Hi all,

I am trying some cases in stored procedure CASE , END-CASE statement.

Is it possible to enter an SQL query inside a WHEN clause.

For example

Code:
select  Name,
case
when year='2006' then
select year from db2t.student_mark where year='2006' fetch first 1 rows only;

else
'2005'
end as year
from db2t.student_table
fetch first 10 rows only;


I tried this, but got an error and I couldn't get any example with an SQL statement.

Appreciate your help on this,

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

wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue May 15, 2007 11:07 pm    Post subject: Re: Stored procedure Case- End Case
Reply with quote

What error did you get? I don't think a ';' at the end of first WHEN is needed.
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Wed May 16, 2007 1:12 pm    Post subject:
Reply with quote

Hi,

This is Not possible, as the case function doesn't recognise the inner query. I think you must have got an error like invalid select expected some other variable.

I have tried to use it long back and failed, instead i have changed the query.


Cheers
Ashwin
Back to top
View user's profile Send private message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Wed May 16, 2007 7:13 pm    Post subject: Re: Stored procedure Case- End Case
Reply with quote

Hi Wanderer, Ashwin,

Thanks for your replies.

Wanderer,

I tried to remove the ";" but again I got the same. When I try to run in QMF, I get syntax error. Whereas when I just use a constant in place of the query it works fine.

Ashwin,
I thought the same, but I see lot of examples in books with "UPDATE" SQL statement. I haven't tried one, but I would think that if we can use UPDATE, why not SELECT?

Daphne
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu May 17, 2007 3:41 am    Post subject: Re: Stored procedure Case- End Case
Reply with quote

The CASE that you have used inside a SELECT statement, is a CASE expression. This is not the CASE procedural statement where ';' is needed at the end of THEN.

And, when you use CASE expressions, the thing that follows a THEN or ELSE is a result-expression containing arithmatic, date or other such operands. Not a statement like SELECT or UPDATE.

When you say that you have seen CASE with THEN followed by UPDATE, those must have been CASE procedural statements. But even in procedural CASE statements, the only SELECT that is allowed is a SELECT INTO(not a normal SELECT).

hope it helps.
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
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Is possible to call subprogram from c... cmsmoon COBOL Programming 3 Fri Aug 05, 2016 6:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us