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
 

 

Can we use SUBQUERY in the Case Statement ?

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

New User


Joined: 23 Aug 2006
Posts: 93
Location: chennai

PostPosted: Fri Mar 30, 2007 5:43 pm    Post subject: Can we use SUBQUERY in the Case Statement ?
Reply with quote

Hi All,

Can we use SUBQUERY in the Case Statement ?

Example:

CASE
WHEN (SELECT COUNT(MEDAL) FROM Table
= 10 THEN 'GOOD '
WHEN (SELECT COUNT(MEDAL) FROM Table
= 5 THEN 'POOR'

END
AS Rank
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Apr 02, 2007 2:32 am    Post subject:
Reply with quote

Hello,

An easy thing to do is to put this in some code and give it a try. Have you done so yet?

If you do try it and post your result, others will benefit from your experience.
Back to top
View user's profile Send private message
ramfrom84

New User


Joined: 23 Aug 2006
Posts: 93
Location: chennai

PostPosted: Mon Apr 02, 2007 3:29 pm    Post subject: Re: Can we use SUBQUERY in the Case Statement ?
Reply with quote

Hi All,
I try with sample code but it is showing the below error

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "SELECT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <expression> BETWEEN IS LIKE NULL NOT

I tested for sub query seperately. it is working properly , When i combine with the CASE statement it showing error . thus i asked it is possible to have subquery with CASE Statement.

Anybody know plz help me...

Thanks very much for ur interest
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Mon Apr 02, 2007 8:08 pm    Post subject:
Reply with quote

Please post the query here
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Apr 02, 2007 9:26 pm    Post subject:
Reply with quote

Hello,

The query is posted above. icon_smile.gif
Back to top
View user's profile Send private message
wanderer

Active User


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

PostPosted: Tue Apr 03, 2007 5:29 am    Post subject: Re: Can we use SUBQUERY in the Case Statement ?
Reply with quote

ramfrom84,

A subselect query can not be used as a search condition in the searched CASE statement like you have posted above, as per the manual.

But may be the following will be a useful alternative.

Code:
SELECT
          CASE
              WHEN TABLE1.CNT= 10 THEN 'GOOD '
              WHEN TABLE1.CNT= 5 THEN 'POOR'
           END AS RANK
FROM (SELECT COUNT(MEDAL) AS CNT FROM Table ) TABLE1


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

New User


Joined: 23 Aug 2006
Posts: 93
Location: chennai

PostPosted: Tue Apr 03, 2007 12:17 pm    Post subject: Re: Can we use SUBQUERY in the Case Statement ?
Reply with quote

Hi All,
I mention the query in the beginning , Thank friend for ur reply. But anybody conform that Sub query cannot be used in the CASE(WHEN) Statement , Since i have seen Sub Query used in the CASE(ELSE) Statement.


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

Active User


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

PostPosted: Tue Apr 03, 2007 2:13 pm    Post subject: Re: Can we use SUBQUERY in the Case Statement ?
Reply with quote

It would be nice if you could paste that query you have seen where subquery used in CASE ELSE.
Back to top
View user's profile Send private message
ramfrom84

New User


Joined: 23 Aug 2006
Posts: 93
Location: chennai

PostPosted: Tue Apr 03, 2007 8:19 pm    Post subject: Re: Can we use SUBQUERY in the Case Statement ?
Reply with quote

It is big Query for easy understanding i mention as simple.


ELSE (
SELECT Column_name FROM Table1 where condition
)
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Apr 03, 2007 8:48 pm    Post subject:
Reply with quote

Hello,

Seems straight forward - you can issue a query as part of the ELSE, but cannot as part of the WHEN. ELSE requires imperative statements - WHEN requires conditional operators.

You could also issue the SELECT before the CASE and then test for what you want. Personally, i'd do the SELECT away from the CASE. Is there some reason to do multiple selects with the WHENs? In the posted example, the count is all that is needed.
Back to top
View user's profile Send private message
wanderer

Active User


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

PostPosted: Wed Apr 04, 2007 2:40 am    Post subject: Re: Can we use SUBQUERY in the Case Statement ?
Reply with quote

ramfrom84 wrote:
It is big Query for easy understanding i mention as simple.


ELSE (
SELECT Column_name FROM Table1 where condition
)



Even if this is possible, it is still not going to help you in the query in your first post where you want to use a subselect as a search string in WHEN.

Secondly, although the manual doesn't explicitly say that subselects can not be used as result-expressions following THEN and ELSE, unlike WHEN, an "expression" is still not a "statement"(as per my understanding)
Examples of expressions would be
function, constant, column name, host variable, CASE, CAST, special register etc.

So I doubt a SELECT can be used following either THEN or ELSE.

I tried using it in a sample query and it didn't work. You can try too by writing your sample query and if works, please post the complete query.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Apr 04, 2007 2:53 am    Post subject:
Reply with quote

Hello W,

Did you try a CASE that only had an SQL query after the ELSE?

I'm not identified as a DB2 "user" (yet), so i can't run my own test (yet icon_smile.gif ).
Back to top
View user's profile Send private message
wanderer

Active User


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

PostPosted: Wed Apr 04, 2007 3:13 am    Post subject:
Reply with quote

Yes, I did. And it is as follows,

Code:
SELECT CASE                                             
    WHEN A.COL1 = 2 THEN 9                               
    ELSE (SELECT B.COL1 FROM TABLE1 B WHERE B.COL1 = 1) 
    END                                                 
FROM TABLE1 A                                           


And I get,

Code:
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "B". SOME SYMBOLS THAT MIGHT BE
         LEGAL ARE: + ) -   


So I doubt even this is workable.
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
This topic is locked: you cannot edit posts or make replies. Updating value of key using rewrite s... ParminderKumar COBOL Programming 4 Thu Aug 04, 2016 12:46 am


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