Case Statement in JOIN

I have this query:

Select b.building_pk, bil.building_fk, bil.BillingAccountStatus_fk, b.ACTL_TNT_CT 
From [DB].[Schema].Building b (nolock)
left Join [DB].[Schema].Billing bil (nolock) on bil.building_fk = b.building_pk
join ##GlobalTempTable1 tt (nolock) on tt.Building_fk = b.building_pk
Order by b.building_pk; 

It works fine. But I want to override NULLs in the result set in columns bil.building_fk and bil.BillingAccountStatus_fk as not everything in Billing tbl exists in building table.

So wrote the following query below, but getting this error message.

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

Please assist.

SELECT b.building_pk, bil.building_fk As [BLD Key from Billing], bil.BillingAccountStatus_fk, b.ACTL_TNT_CT As [ActualTenantCount] 
FROM [DB].[Schema].Building b (nolock)
join ##GlobalTempTable1 tt (nolock) ON tt.Building_fk = b.building_pk
left Join [DB].[Schema].Billing bil (nolock) ON
CASE
    WHEN bil.building_fk IS NOT NULL 
        THEN bil.building_fk = b.building_pk AND bil.BillingAccountStatus_fk = bil.BillingAccountStatus_fk
    WHEN bil.building_fk IS NULL
        THEN bil.building_fk = NULL AND bil.BillingAccountStatus_fk = 'Not in Billing'
    END
ORDER BY b.building_pk;

Answers


It's hard to tell what you are trying to do because it's very wrong. I think, though, that you want to keep your FROM the same and do the CASE statement up into the SELECT.

Select b.building_pk, CASE WHEN bil.Building_fk IS NULL THEN 'Not in Billing' ELSE bil.building_fk END as building_fk, bil.BillingAccountStatus_fk, b.ACTL_TNT_CT 
From [DB].[Schema].Building b (nolock)
left Join [DB].[Schema].Billing bil (nolock) on bil.building_fk = b.building_pk
join ##GlobalTempTable1 tt (nolock) on tt.Building_fk = b.building_pk
Order by b.building_pk; 

Or, better yet, use COALESCE() here:

Select b.building_pk, COALESCE(bil.Building_fk,'Not in Billing') as building_fk, bil.BillingAccountStatus_fk, b.ACTL_TNT_CT 
From [DB].[Schema].Building b (nolock)
left Join [DB].[Schema].Billing bil (nolock) on bil.building_fk = b.building_pk
join ##GlobalTempTable1 tt (nolock) on tt.Building_fk = b.building_pk
Order by b.building_pk; 

I am guessing that you are trying to do something like this:

SELECT b.building_pk, bil.building_fk As [BLD Key from Billing], bil.BillingAccountStatus_fk, b.ACTL_TNT_CT As [ActualTenantCount] 
FROM [DB].[Schema].Building b (nolock)
join ##GlobalTempTable1 tt (nolock) ON tt.Building_fk = b.building_pk
left Join [DB].[Schema].Billing bil (nolock) ON
    (bil.building_fk IS NOT NULL AND 
        (bil.building_fk = b.building_pk AND bil.BillingAccountStatus_fk = bil.BillingAccountStatus_fk))
    OR (bil.building_fk IS NULL AND
        (bil.building_fk = NULL AND bil.BillingAccountStatus_fk = 'Not in Billing'))
ORDER BY b.building_pk;

I'll leave the misguided use of nolock for another day ...


CASE expressions can't be used to set which criteria you use for a JOIN, they can be used on either side of the comparison operator in JOIN criteria, but I don't think you need that, I think you just need a LEFT JOIN and a COALESCE() in your SELECT:

SELECT b.building_pk
     , bil.building_fk As [BLD Key from Billing]
     , COALESCE(bil.BillingAccountStatus_fk,'Not in Billing')
     , b.ACTL_TNT_CT As [ActualTenantCount] 
FROM [DB].[Schema].Building b 
join ##GlobalTempTable1 tt 
   ON tt.Building_fk = b.building_pk
left Join [DB].[Schema].Billing bil 
   ON bil.building_fk = b.building_pk
ORDER BY b.building_pk;

Edit: Noticed BillingAccountStatus_fk was coming from bil on both sides, so removed that from the JOIN criteria.


Need Your Help

Sort LinkedHashmap of Array in ascending and descending order - Java

java arrays sorting linkedhashmap

How do I sort a LinkedHashMap of int arrays, by having the first element in the array sorted in descending order, and the second element sorted in ascending order?

change index inside a for loop

java for-loop indexing

Am I able to change the index inside the for loop in java? For example: