Assign Null to a column in a CTE

Question

I want to assign a NULL to a column based on whether another column is NULL. Both of these columns are from the result of the previous CTE, and it seems I can't get it to work.

Here is my code:

select 
    off_rent_start_date, 
    days_off_rent,
    case 
        when days_off_rent IS NULL
            then off_rent_start_date NULL
    end
from 
    FinalData

Raw data looks like this:

off_rent_start_date	days_off_rent
2023-11-30	NULL

Expected result should look like this:

off_rent_start_date	days_off_rent
NULL	NULL

Answer 1

It looks like you need something like here:

Select 
  Case When days_off_rent IS NULL  -- this is condition that could be True or False
       Then NULL                   -- When True return NULL
  Else off_rent_start_date         -- Else (False) return value from column off_rent_start_date
  End as off_rent_start_date,      -- this is column alias (label) for the resultset
  days_off_rent
From FinalData

CASE expression works sequentialy from top to bottom. First WHEN condition satisfied returns THEN value and exit CASE. If none of them saatisfied returns the value from ELSE and if there is no ELSE defined (it is optional) returns NULL.

Having that in mind - the same result you could get using reversed logic - when days_off_rent IS NOT NULL fetch off_rent_start_date otherwise NULL.

Select 
  Case When days_off_rent IS NOT NULL 
       Then off_rent_start_date 
  End as off_rent_start_date,    -- there is no ELSE - so it results as NULL if WHEN condition is not satisfied
  days_off_rent
From FinalData