CODEWITHSUNDEEP
sql
Encryption
Encryption

Reputation: 1897

Complex SQL CASE WHEN

I am trying to do a SELECT using a CASE statement, and I 'm getting errors that do not make sense. My statement appears to adhere to any SQL syntax rules but I'm getting the good ol' "the multi part identifier to_do.[item_id] AND to_do.[item] cannot be bound. This does not make sense because the column names are correct with respect to the table columns.

Here is the SQL:

SELECT      to_do.[item_id],
            to_do.[item] = CASE to_do.encounter_id WHEN 0 THEN CONVERT(NVARCHAR, e.[date], 101 ) + ' - ' + p.[first_name] + ' ' + p.[last_name] + ' - ' + to_do.[item] AS to_do.[item] ELSE to_do.[item] END,
            to_do.[complete],
            to_do.[encounter_id],
            e.[date],
            p.[last_name] + ', ' + p.[first_name] AS [p_name]
FROM        [dbo].[to_do] LEFT OUTER JOIN
            [dbo].[encounter] e ON
                to_do.[encounter_id] = e.[encounter_id] LEFT OUTER JOIN 
            [dbo].[patients] p ON
                e.[mrn] = p.[mrn]
WHERE       to_do.[user_id] = @user_id

Any ideas what I'm doing wrong here ? Based off previous experience I should be able to use CASE like this but maybe I've just been staring at it too long and don't see the obvious. Thanks!

Upvotes: 1

Views: 496

Answers (3)

Martin Smith
Martin Smith

Reputation: 453152

You can't use a table prefix in a column alias to_do.[item] = CASE ... is presumably intended to be [item] = CASE ...

(Also you have a rogue AS in the middle of the CASE itself that needs removing)

Upvotes: 5

dmc
dmc

Reputation: 2684

The AS {alias} needs to go after the END. I'd rewrite as follows:

SELECT      to_do.[item_id],
            CASE to_do.encounter_id WHEN 0 THEN CONVERT(NVARCHAR, e.[date], 101 ) + ' - ' + p.[first_name] + ' ' + p.[last_name] + ' - ' + to_do.[item] ELSE to_do.[item] END AS [item] , 
...

Upvotes: 5

antlersoft
antlersoft

Reputation: 14786

Your AS is in the wrong place-- try

            to_do.[item] = CASE to_do.encounter_id WHEN 0 THEN CONVERT(NVARCHAR, e.[date], 101 ) + ' - ' + p.[first_name] + ' ' + p.[last_name] + ' - ' + to_do.[item] ELSE to_do.[item] END AS to_do.[item] ,

Upvotes: 1

Related Questions