Ogrish Man Ogrish Man - 7 months ago 23
SQL Question

Can I use CASE statement in a JOIN condition?

The following image is a part from Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between

sys.partitions
and
sys.allocation_units
depends on the value of
sys.allocation_units.type
. So to join them together I would write something similar to this:

SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3)
THEN a.container_id = p.hobt_id
WHEN a.type IN (2)
THEN a.container_id = p.partition_id
END


But the upper code gives a syntax error. I guess that's because the
CASE
statement.
Anyone can help to explain a little?

Thanks!




Add error message:


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


this is the image

Answer

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)