Case report in JOIN

advertisements

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;


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;