CODEWITHSUNDEEP
sql-server
alaa nadder
alaa nadder

Reputation: 1

MS SQL case then statement with more than one column

I want to return multiple column in the case when .... then col1 ,col2 , col3 or use * .

my query is :

 select a.param1, case
              when b.param1 is not null then  b.loc_ID , 'july'
              when c.param1 is not null then c.loc_ID , 'may'
              else ''
              end
  from [dbo].[table1] a
  left join [dbo].[USERS_7_30] b
  on a.param1 = b.pram1
  left join [dbo].[USERS_6_30] c
  on a.param1 = c.pram1

I get this response:

Msg 102, Level 15, State 1, Line 147 Incorrect syntax near ','.

Upvotes: 0

Views: 963

Answers (2)

Thom A
Thom A

Reputation: 95554

That's not how a CASE expression works. A CASE expression can only return single scalar value. You'll have to use multiple expressions:

SELECT a.param1,
       CASE WHEN b.param1 IS NOT NULL THEN b.loc_ID WHEN c.param1 IS NOT NULL THEN c.loc_ID ELSE '' END AS loc_id,
       CASE WHEN b.param1 IS NOT NULL THEN 'July' WHEN c.param1 IS NOT NULL THEN 'May' ELSE '' END AS loc_id
FROM [dbo].[table1] a
     LEFT JOIN [dbo].[USERS_7_30] b ON a.param1 = b.pram1
     LEFT JOIN [dbo].[USERS_6_30] c ON a.param1 = c.pram1;

Your ELSE only had 1 return value, so I assumed '' should be returned for both columns.

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

you need to use separate case when statement

select a.param1, 
              case
              when b.param1 is not null then b.loc_ID 
              when c.param1 is not null then c.loc_ID 
              else ''
              end,
              case
              when b.param1 is not null then 'july'
              when c.param1 is not null then 'may'
              else ''
              end
  from [dbo].[table1] a
  left join [dbo].[USERS_7_30] b
  on a.param1 = b.pram1
  left join [dbo].[USERS_6_30] c
  on a.param1 = c.pram1

Upvotes: 1

Related Questions