Null values affecting case logic

• I am having a problem in the following query.
• In the AR column there are some null values due to the left join of the wbr_global.raw_station_extended_attribute.
• To fix this I added a case to default to 0 if it is null.
• The issue this is causing is in my next case statement for assinging a station size.
• All the null values are pulling in as “ds small” depsite their volume I am pulling in.
• For example, the centroid stations in the picture orignially had null as the ar value but now have zero but has small pulling in when x-small should be assinged based on the sizing logic.

SELECT
    station,

    CASE 
        WHEN ar IS NULL THEN '0'
        ELSE ar
    END AS ar,

    del_date,
    sum( volume ) / 7 AS volume_ref,
    
    DATE_PART("week", del_date) AS week_num,
    DATE_PART("year", del_date) AS year,
    
    CASE 
        WHEN volume_ref between     0 AND 20000 AND ar <> 'YES' THEN 'ds x-small'
        WHEN volume_ref between 20000 AND 36000 AND ar <> 'YES' THEN 'ds small'
        WHEN volume_ref between 36000 AND 42000 AND ar <> 'YES' THEN 'ds standard'
        WHEN volume_ref between 42000 AND 72000 AND ar <> 'YES' THEN 'ds large'
        WHEN volume_ref                 > 72000                 THEN 'ds x-large'
        WHEN                                        ar =  'YES' THEN 'ds x-large'
        ELSE                                                         'ds small'
    END AS station_ref

FROM
    prophecy_na.na_topology_lrp
    LEFT JOIN wbr_global.raw_station_extended_attribute ON 
        prophecy_na.na_topology_lrp.station   = 
        wbr_global.raw_station_extended_attribute.ds 
WHERE
    week_num IN ( 16, 20, 40, 48 )
GROUP BY
    na_topology_lrp.station,
    raw_station_extended_attribute.ar,
    na_topology_lrp.del_date;

raw_station_extended_attribute table

na_topology_lrp table

Answer

I believe that the highlighted row has a station_ref of ‘ds small’ because of the ELSE statement at the end of the 2nd case statement. Further, you are using multiple ‘ar’ references here – ‘ar’ is used to reference the column both before and after processing the 1st CASE statement.

Try this:

SELECT
    station,

    CASE 
        WHEN ar IS NULL THEN '0'
        ELSE ar
    END AS ar,

    del_date,
    sum( volume ) / 7 AS volume_ref,
    
    DATE_PART("week", del_date) AS week_num,
    DATE_PART("year", del_date) AS year,
    
    CASE 
        WHEN volume_ref between     0 AND 20000 AND REPLACE(ar,NULL,'0') <> 'YES' THEN 'ds x-small'
        WHEN volume_ref between 20000 AND 36000 AND REPLACE(ar,NULL,'0') <> 'YES' THEN 'ds small'
        WHEN volume_ref between 36000 AND 42000 AND REPLACE(ar,NULL,'0') <> 'YES' THEN 'ds standard'
        WHEN volume_ref between 42000 AND 72000 AND REPLACE(ar,NULL,'0') <> 'YES' THEN 'ds large'
        WHEN volume_ref                 > 72000                 THEN 'ds x-large'
        WHEN                                        REPLACE(ar,NULL,'0') =  'YES' THEN 'ds x-large'
        ELSE                                                         'ds small'
    END AS station_ref

FROM
    prophecy_na.na_topology_lrp
    LEFT JOIN wbr_global.raw_station_extended_attribute ON 
        prophecy_na.na_topology_lrp.station   = 
        wbr_global.raw_station_extended_attribute.ds 
WHERE
    week_num IN ( 16, 20, 40, 48 )
GROUP BY
    na_topology_lrp.station,
    raw_station_extended_attribute.ar,
    na_topology_lrp.del_date;