How to exclude null value rows from the result set of CASE statement oracle SQL-CodePudding

I am trying to exclude the rows with null values from Units_shorted column (from the CASE clause) but not finding a way.

SELECT DISTINCT td.task_id,td.cntr_nbr,lh.dsp_locn AS pull_locn,td.orig_reqmt,td.qty_pulld,
(
CASE 
WHEN
((SUM(td.qty_pulld) over (partition by td.pull_locn_id)) < td.orig_reqmt)  and ((SUM(td.qty_pulld) over (partition by td.pull_locn_id))-td.orig_reqmt <> 0) 
THEN (td.orig_reqmt- td.qty_pulld)
END) AS units_shorted
FROM wm14.task_dtl td 
INNER JOIN wm14.locn_hdr lh ON lh.locn_id = td.pull_locn_id
INNER JOIN wm14.order_line_item oli ON oli.item_id = td.item_id
WHERE  EXISTS (SELECT 1 FROM wm14.msg_log ml WHERE ml.user_id = td.user_id AND ml.msg_id IN ('1060','1034') AND module = 'CTRLKEY' 
AND TRUNC(td.mod_date_time) = TRUNC(ml.create_date_time)) AND td.invn_need_type IN ('53','54')
AND td.stat_code >= '90' 
and td.task_genrtn_ref_nbr NOT IN (SELECT  ml.ref_value_1 FROM wm14.msg_log ml WHERE  ml.msg ='Undo Wave completed')
group by td.task_id,td.cntr_nbr,lh.dsp_locn,td.task_genrtn_ref_nbr,td.pull_locn_id,td.item_id,td.qty_pulld,td.orig_reqmt
ORDER BY td.task_id,lh.dsp_locn DESC;

Result set:

TASK_ID, CNTR_NBR,   PULL_LOCN,     WAVE_NBR,   ORIG_REQMT, QTY_PULLD, UNITS_SHORTED
5420174  GPK0324828 1GF-27-02144    202204200024    24       23           1
5420174  GPK0324822 1GF-23-03110    202204200024    5        3            2
5420174  GPK0324823 1GF-26-04102    202204200024    7        7           null

CodePudding user response：

A simple option - if query you have returns desired result set - is to use it as a CTE (or a subquery) and filter rows out:

    with temp as
    -- your current query begins here
    (SELECT DISTINCT td.task_id,td.cntr_nbr,lh.dsp_locn AS pull_locn,td.orig_reqmt,td.qty_pulld,
        (
        CASE 
        WHEN
        ((SUM(td.qty_pulld) over (partition by td.pull_locn_id)) < td.orig_reqmt)  and ((SUM(td.qty_pulld) over (partition by td.pull_locn_id))-td.orig_reqmt <> 0) 
        THEN (td.orig_reqmt- td.qty_pulld)
        END) AS units_shorted
        FROM wm14.task_dtl td 
        INNER JOIN wm14.locn_hdr lh ON lh.locn_id = td.pull_locn_id
        INNER JOIN wm14.order_line_item oli ON oli.item_id = td.item_id
        WHERE  EXISTS (SELECT 1 FROM wm14.msg_log ml WHERE ml.user_id = td.user_id AND ml.msg_id IN ('1060','1034') AND module = 'CTRLKEY' 
        AND TRUNC(td.mod_date_time) = TRUNC(ml.create_date_time)) AND td.invn_need_type IN ('53','54')
        AND td.stat_code >= '90' 
        and td.task_genrtn_ref_nbr NOT IN (SELECT  ml.ref_value_1 FROM wm14.msg_log ml WHERE  ml.msg ='Undo Wave completed')
        group by td.task_id,td.cntr_nbr,lh.dsp_locn,td.task_genrtn_ref_nbr,td.pull_locn_id,td.item_id,td.qty_pulld,td.orig_reqmt
    -- your current query ends here; only the ORDER BY clause is moved out
    )
    select *
    from temp
    where units_shorted is not null      --> filter
    ORDER BY task_id, dsp_locn DESC;