How can I use CASE clause twice in SQL Query

Question

I am trying to apply two conditions in one SQL Query.

(select DISTINCT (
        CASE WHEN (
         ABC.GemUserID = '99' ) 
         OR ABC.GemUserID != '99'
            THEN 'Yes'
    ELSE 'No'
        END)) AS AllWell

This gives me output as "Yes" where as the CASE is true only for 1 file like below :

Current Result:

99 , Yes
99 , Yes
99 , Yes

Expected Result:

99 , No
99 , No
99 , Yes

I am using the below query but the SQL Query Intellisence is identifying it as wrong.

Wrong Query:

(select DISTINCT (
    CASE WHEN ( ABC.GEMUserID  = '99' THEN 'Yes' else 'No'
    CASE WHEN ( ABC.GEMUserID != '99' THEN 'No'  else 'Yes'

    END)) AS AllWell

After fixing the above Wrong Query:

(select DISTINCT 
        (CASE WHEN  ABC.GemUserID  = '99' THEN 'Yes' else 'No' END), 
        (CASE WHEN  ABC.GemUserID != '99' THEN 'No'  else 'Yes' END))
         AS AllWell

But I am getting error:

Msg 116, Level 16, State 1, Line 17 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How to fix this?

Use `CASE WHEN` "internally" `CASE WHEN 1 = 1 THEN 0 ELSE CASE WHEN 2 = 2 THEN 2 ELSE 0 END END` — Fabio, Sep 04 '17 at 15:49
What is even your intended result? The base expression would be `CASE ABC.GEMUserID WHEN '99' THEN 'Yes' ELSE 'No' END AS AllWell`. Anything else could be derived from that. — Jeroen Mostert, Sep 04 '17 at 15:51
Unnecessary parentheses, a superfluous `WHEN` part (if you remove the parentheses and the second `CASE` you get at least the syntax right), and `GEMUserID` is a string containing numbers??? Or why do you compare with a string `'99'`? — Thorsten Kettner, Sep 04 '17 at 16:25
I edit my question and tried to be more detail. I am really sorry for not being clear first. Please see and help me to get rid of this error. — Learning_Learning, Sep 04 '17 at 16:27
After your edit: `You cannot get `99 , Yes` thrice. `DISTINCT` removes duplicates. Still all parantheses are superfluous and make no sense. `GemUserID` will always either equal or not equal '99'. Only NULL would be an exception. So why do you expect two `No`? Are there tow records with NULL in your table? What about Arix solution? How is it different from what you want? Have you even tried it? — Thorsten Kettner, Sep 04 '17 at 16:30
@spyr0, yes my intention was to reproduce users which are = GEMUserId and users which are != GEMUserId. I am trying to understand my mistake . — Learning_Learning, Sep 04 '17 at 16:31
As to your error: THis is obviously a subquery that is supposed to return one column. But you return two: `CASE WHEN ABC.GemUserID = '99' THEN 'Yes' else 'No' END` with no name and `CASE WHEN ABC.GemUserID != '99' THEN 'No' else 'Yes' END` which you call AllWell. And the parentheses are wrong. Remove them. — Thorsten Kettner, Sep 04 '17 at 16:32
So to start with: Why do you use `DISTINCT`? What is your intention? — Thorsten Kettner, Sep 04 '17 at 16:33
@ThorstenKettner , thanks much for your response. Yes you are right , there are 2 NULL values in my table. I have just renamed NULL as No. So I want the same to reflect in this SQL. — Learning_Learning, Sep 04 '17 at 16:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/153632/discussion-between-thorsten-kettner-and-dbon). — Thorsten Kettner, Sep 04 '17 at 16:34

score 5 · Answer 1 · answered Sep 04 '17 at 15:50

5

select distinct is -- itself -- part of SQL syntax. The distinct is not a function. It should not be followed by parentheses. So, if I understand your question:

select DISTINCT 
       ( CASE WHEN ABC.GEMUserID = '99' THEN 'Yes' else 'No' END),
       ( CASE WHEN ABC.GEMUserID <> '99' THEN 'No'  else 'Yes' END) as AllWell

Do you plan on giving the first column a name?

answered Sep 04 '17 at 15:50

Gordon Linoff

1,242,037
58
646
786

Thanks a lot for the response. I have edited my question and tried to be clear . I have made up the query as you pointed out . But that throws the error may be my mistake . Kindly hint me what am I doing wrong. Thanks. – Learning_Learning Sep 04 '17 at 16:28

score 2 · Answer 2 · answered Sep 04 '17 at 16:02

2

select DISTINCT 
CASE WHEN  ABC.GEMUserID  = '99' THEN 'Yes' 
     ELSE 'No' -- This is automatically When ABC.GEMUserID <> '99'
END AS AllWell

answered Sep 04 '17 at 16:02

Arnine

123
6

Thanks a lot @Arix . Please see I have edited my question and my clear request. – Learning_Learning Sep 04 '17 at 16:29
Can you give context about how you are using this? You have two columns being returned and one of them is a number... 99 and the other is 'Yes' or 'No'.. Are you trying to randomise the yes and no's? – Arnine Sep 04 '17 at 20:16

score 0 · Answer 3 · answered Sep 04 '17 at 19:46

0

According to the error, your query is a subquery (probably behind IN?) in a larger SQL command. Therefore, it is not possible for such subquery to return more than one column.

answered Sep 04 '17 at 19:46

Radim Bača

10,646
1
19
33

score 0 · Answer 4 · answered Sep 04 '17 at 23:51

So your first query, you've said:

CASE WHEN userID = 99 OR userID != 99

In other words:

CASE WHEN 1=1

This is why it returns yes for everything (not sure what the difference between your current and expected result should be considering that the userID is 99 for all rows).

For your erroneous query, seems you're returning that select in the middle of another select (since you alias it at the end). Due to that, you cannot return more than one column in your nested select. You do not need the second CASE statement, simply change your query to:

(select DISTINCT
    CASE WHEN ABC.GemUserID = '99' THEN 'Yes' Else 'No' End) AS AllWell

Assuming that you hold the missing pieces to the query such as the FROM.

How can I use CASE clause twice in SQL Query

4 Answers4