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
 
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm

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