Converting IIF to CASE Statement (Error in THEN & ELSE Statements)

Question

This is the query in IIF format.

SELECT [userinfo].[dp_url]
    ,[message_threads].[thread_id]
    ,[userinfo].[fname]
    ,[userinfo].[lname]
    ,[userinfo].[profile_id]
FROM [message_threads]
LEFT JOIN [userinfo] ON (IIF([message_threads].[from] <> 2, [userinfo].[profile_id] = [message_threads].[from], [userinfo].[profile_id] = [message_threads].[to]))
WHERE (IIF([message_threads].[from] <> 2, [to] = 2, [from] = 2))
ORDER BY [last_updated_on] DESC

This is what I have made with CASE statement so far.

SELECT [userinfo].[dp_url]
    ,[message_threads].[thread_id]
    ,[userinfo].[fname]
    ,[userinfo].[lname]
    ,[userinfo].[profile_id]
FROM [message_threads]
LEFT JOIN [userinfo] ON (
        CASE 
            WHEN ([message_threads].[from] <> 2)
                THEN ([userinfo].[profile_id] = [message_threads].[from])
            ELSE ([userinfo].[profile_id] = [message_threads].[to])
            END
        )
WHERE (
        CASE 
            WHEN ([message_threads].[from] <> 2)
                THEN ([to] = 2)
            ELSE ([from] = 2)
            END
        )
ORDER BY [last_updated_on] DESC

I'm getting the following error:

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

It's very necessary to use equal to (=).

Any help is appreciated!

Have a look at this: http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition. Your CASE needs to return 1 or 0 and then the comparison becomes an AND — GinjaNinja, May 23 '16 at 14:25
@HoneyBadger I am new to the `CASE` statements. I thought that's how you condense the conditions. — Ishaan Patel, May 23 '16 at 15:48

score 1 · Accepted Answer · answered May 23 '16 at 14:27

CASE is an expression that returns a value, not used for program flow, so you could just rearrange your cases to:

LEFT JOIN [userinfo]
    ON [userinfo].[profile_id] = (
        CASE 
            WHEN ([message_threads].[from] <> 2)
                THEN ([message_threads].[from])
            ELSE ([message_threads].[to])
            END
    )
WHERE 2 = (
    CASE 
        WHEN ([message_threads].[from] <> 2)
            THEN ([to])
        ELSE ([from])
        END
    )

An alternative would be to convert it to boolean conditions:

LEFT JOIN [userinfo] ON 
    ([message_threads].[from] <> 2 AND [userinfo].[profile_id] = [message_threads].[from])
    OR 
    ([message_threads].[from] = 2 AND [userinfo].[profile_id] = [message_threads].[to])

and similarly for WHERE

score 0 · Answer 2 · answered May 23 '16 at 14:30

Try not to use conditionals for comparisons. Normally, AND and OR are quite sufficient:

SELECT . . .
FROM [message_threads] LEFT JOIN
     [userinfo]
     ON ([message_threads].[from] <> 2 AND [userinfo].[profile_id] = [message_threads].[from]) OR
        ([message_threads].[from] = 2 AND [userinfo].[profile_id] = [message_threads].[to])
WHERE 2 IN ([from], [to])

Note: I simplified the WHERE. If it is oversimplified, you can use:

WHERE ([message_threads].[from] <> 2 AND [to] = 2) OR
      ([message_threads].[from] - 2 AND [from] = 2)

I also strongly recommend table aliases for readability:

SELECT . . .
FROM message_threads mt LEFT JOIN
     userinfo ui
     ON (mt.[from] <> 2 AND ui.profile_id = mt.[from]) OR
        (mt.[from] = 2 AND ui.profile_id = mt.[to])
WHERE 2 IN ([from], [to])

Also, you should use reserved words such as from and to as column names.

Thank you for your answer. It makes more sense to use the simplified `WHERE` clause instead. I'm still getting to know SQL as we speak, I was unaware of the `IN` clause too! :o — Ishaan Patel, May 23 '16 at 15:46

Converting IIF to CASE Statement (Error in THEN & ELSE Statements)

2 Answers2