November 13, 2013 at 2:38 pm
I have query where i am using case statement and having issues. A person could select two options and if they select two options i do not want both output instead just THEN 'TWO ITEMS SELECTED' and when only only selection picked then output the corresponding result.
SELECT ID,
LAST_NAME,
FIRST_NAME,
CASE WHEN EXISTS (SELECT count(SELECTION) FROM ITEMS) >= 2 THEN 'Two Items selected'
WHEN SELECTON = -1 THEN 'A'
WHEN SELECTON = -2 THEN 'B'
WHEN SELECTON = -3 THEN 'C'
WHEN SELECTON = -4 THEN 'D'
WHEN SELECTON = -5 THEN 'E'
END AS item_selected
output would be like:
last_name first_name item_selected
------------ ------------- ----------------------
doe john Two Items selected
Not like:
last_name first_name item_selected
------------ ------------- ------------------
doe john a
doe john b
November 13, 2013 at 2:56 pm
There's something wrong with your query. You're comparing a boolean value (EXISTS()) against an integer value.
To get better help, please post DDL for tables involved and sample data in the way of INSERT INTO statements. For information on how to do this, check the article linked in my signature.
November 13, 2013 at 4:17 pm
Something like below. If the LAST_NAME and FIRST_NAME are in the same table (shouldn't be, from a design standpoint), then you can remove the outer query and join and uncomment the LAST_NAME and FIRST_NAME references in the inner query:
SELECT
i_sel.ID, n.LAST_NAME, n.FIRST_NAME, i_sel.item_selected
FROM (
SELECT i.ID,
--LAST_NAME,
--FIRST_NAME,
CASE WHEN COUNT(i.SELECTION) >= 2 THEN 'Two Items selected'
WHEN MAX(i.SELECTION) = -1 THEN 'A'
WHEN MAX(i.SELECTION) = -2 THEN 'B'
WHEN MAX(i.SELECTION) = -3 THEN 'C'
WHEN MAX(i.SELECTION) = -4 THEN 'D'
WHEN MAX(i.SELECTION) = -5 THEN 'E'
END AS item_selected
FROM ITEMS i
GROUP BY i.ID --, LAST_NAME, FIRST_NAME
) AS i_sel
INNER JOIN NAMES n ON
n.ID = i.ID
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply