CODEWITHSUNDEEP
sqlsql-server
J. Davidson
J. Davidson

Reputation: 3307

Using multiple case statements in select query

Hi I have a following query that checks for code to determine when was it entered or viewed. 

        declare @timestamp datetime;
        select
          case @timestamp
          when a.updatedDate =1760 then 'Entered on' +a.updatedDate
          when a.updatedDate=1710 then  'Viewed on' +a.updatedDate
          else 'Last Updated on'+ a.updatedDate
          end 
         from t_mainTable a
         where a.id=@Id;

When i try to run this query it gives me error 

Msg 102, Level 15, State 1, Procedure p_xxxx, line 40
Incorrect syntax near '='.

There is some syntex error in the when lines. Please let me know how to correct this Thanks

Upvotes: 4

Views: 127336

Answers (1)

Laurence
Laurence

Reputation: 10976

There are two ways to write case statements, you seem to be using a combination of the two

case a.updatedDate
    when 1760 then 'Entered on' + a.updatedDate
    when 1710 then 'Viewed on' + a.updatedDate
    else 'Last Updated on' + a.updateDate
end

or

case 
    when a.updatedDate = 1760 then 'Entered on' + a.updatedDate
    when a.updatedDate = 1710 then 'Viewed on' + a.updatedDate
    else 'Last Updated on' + a.updateDate
end

are equivalent. They may not work because you may need to convert date types to varchars to append them to other varchars.

Upvotes: 20

Related Questions