|
View previous topic :: View next topic
|
| Author |
Message |
ramfrom84
New User
Joined: 23 Aug 2006
Posts: 93
Location: chennai
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
ramfrom84
New User
Joined: 23 Aug 2006
Posts: 93
Location: chennai
|
|
|
|
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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
| Please post the query here |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
The query is posted above.  |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
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 |
|
|
ramfrom84
New User
Joined: 23 Aug 2006
Posts: 93
Location: chennai
|
|
|
|
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 |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
| It would be nice if you could paste that query you have seen where subquery used in CASE ELSE. |
|
| Back to top |
|
|
ramfrom84
New User
Joined: 23 Aug 2006
Posts: 93
Location: chennai
|
|
|
|
It is big Query for easy understanding i mention as simple.
ELSE (
SELECT Column_name FROM Table1 where condition
) |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 ). |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
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 |
|
|
|
|
