CODEWITHSUNDEEP
sqlt-sql
Raehef
Raehef

Reputation: 1

Case statement and OR in SQL

Can someone please advise on the below? I have a number of fields which I would like to use and combine these to get the data in one column. I am using the following case statement but am not getting the results I expect.

CASE
WHEN m.u_hearing = 1 THEN 'Hearing'
WHEN m.u_learning_reading_diff = 1 THEN 'Learning or reading Difficulty'
WHEN m.u_long_term_ill = 1 THEN 'Long Term Illness'
WHEN m.u_mental_illness = 1 THEN 'Mental Illness'
WHEN m.u_mobility = 1 THEN 'Mobility'
WHEN m.u_physical_coordination = 1 THEN 'Physical Co-ordination'
WHEN m.u_physical_dis = 1 THEN 'Physical Disability'
WHEN m.u_red_physical_cap = 1 THEN 'Reduced_physical_capacity'
WHEN m.u_speech = 1 THEN 'Speech'
WHEN m.u_vision = 1 THEN 'Vision'
WHEN m.u_other_dis = 1 THEN 'Other_Disability'
WHEN (m.u_hearing = 1 AND (m.u_learning_reading_diff = 1 OR (m.u_speech = 1))) THEN 'Multiple'

It is the last statement that is not giving the result I would like as if there are multiple fields with Yes then I would like multiple returned but it seems it is picking the first case.

Upvotes: 0

Views: 84

Answers (3)

Abhishek Gurjar
Abhishek Gurjar

Reputation: 7476

From what I know you cannot use your first statement again in another "when" like your

WHEN m.u_hearing = 1 THEN 'Hearing'

Above condition try to use else if it is working and feasible for you in end.

Else 'Multiple'

Upvotes: 0

Ryan Gillooly
Ryan Gillooly

Reputation: 315

When using CASE statements, they will function in the order you have specified.

So if m.u_Hearing = 1 then it will stop at the first line and not reach the bottom one.

This will work for what you require, however list it in the order you prefer.

CASE
WHEN (m.u_hearing = 1 AND (m.u_learning_reading_diff = 1 OR (m.u_speech = 1))) THEN 'Multiple'
WHEN m.u_hearing = 1 THEN 'Hearing'
WHEN m.u_learning_reading_diff = 1 THEN 'Learning or reading Difficulty'
WHEN m.u_long_term_ill = 1 THEN 'Long Term Illness'
WHEN m.u_mental_illness = 1 THEN 'Mental Illness'
WHEN m.u_mobility = 1 THEN 'Mobility'
WHEN m.u_physical_coordination = 1 THEN 'Physical Co-ordination'
WHEN m.u_physical_dis = 1 THEN 'Physical Disability'
WHEN m.u_red_physical_cap = 1 THEN 'Reduced_physical_capacity'
WHEN m.u_speech = 1 THEN 'Speech'
WHEN m.u_vision = 1 THEN 'Vision'
WHEN m.u_other_dis = 1 THEN 'Other_Disability'

Upvotes: 0

juergen d
juergen d

Reputation: 204854

Put the "hardest" condition first

case WHEN m.u_hearing = 1 AND (m.u_learning_reading_diff = 1 OR m.u_speech = 1) THEN 'Multiple' 
     WHEN m.u_hearing = 1 THEN 'Hearing' ...

Because a case stops at the first condition that is true

Upvotes: 1

Related Questions