CODEWITHSUNDEEP
sqloracle-databasenull
arrchar
arrchar

Reputation: 123

Replacing/Evaluating a nested query with a NULL value | Oracle SQL

Im using the following query and I apologize if its a bit messy this is the only way I could get this to give me the results I wanted :) But in the left most column "PO" when its all said and done there is a single instance of (null) see example output. This is causing the numerical values to show as (null) when I can confirm they should have values.

I have used the NVL,NVL2,COALESCE functions with no success.

select query2.PO, query2.Work_Group, query2.PO_QTY_TO_Move, query8.Active_Pick_Faces AS Active_Pick_Faces, query4.C0R_Qty_To_Move AS Move_C0R, query6.NA_Qty_To_Move AS Move_NA
from((select PO, Work_Group, SUM(qty_to_move) AS PO_Qty_To_Move
        from(select order_header.purchase_order AS PO,order_header.work_group AS Work_Group, move_task.task_id, move_task.qty_to_move AS qty_to_move
                from order_header
                left join move_task
                on order_header.order_id = move_task.task_id
                where move_task.site_id = 'USCOL1' and move_task.client_id = 'SDRY-US' and move_task.status != 'Consol' )query1
        group by PO, Work_Group)query2
    left join
        (select PO, Work_Group, SUM(qty_to_move) AS C0R_Qty_To_Move
        from(select order_header.purchase_order AS PO,order_header.work_group AS Work_Group, move_task.task_id, move_task.from_loc_id, move_task.qty_to_move AS qty_to_move
                from order_header
                left join move_task
                on order_header.order_id = move_task.task_id
                left join location
                on move_task.from_loc_id = location.location_id
                where move_task.site_id = 'USCOL1' and move_task.client_id = 'SDRY-US' and location.work_zone = 'C0-R' and move_task.status != 'Consol' or order_header.purchase_order is null)query3
        group by PO, Work_Group)query4
    on query2.PO = query4.PO
    left join 
        (select PO, Work_Group, SUM(qty_to_move) AS NA_Qty_To_Move
        from(select order_header.purchase_order AS PO,order_header.work_group AS Work_Group, move_task.task_id, move_task.qty_to_move AS qty_to_move
                from order_header
                left join move_task
                on order_header.order_id = move_task.task_id
                where move_task.site_id = 'USCOL1' and move_task.client_id = 'SDRY-US' and move_task.from_loc_id like 'NA%' and move_task.from_loc_id not like '%PAL%' and move_task.status != 'Consol' )query5
        group by PO, Work_Group) query6
    on query2.PO = query6.PO
    left join 
        (select PO, Work_Group, SUM(qty_to_move) AS Active_Pick_Faces 
        from(select order_header.purchase_order AS PO,order_header.work_group AS Work_Group, move_task.task_id, move_task.qty_to_move AS qty_to_move
                from order_header
                left join move_task
                on order_header.order_id = move_task.task_id
                left join location
                on move_task.from_loc_id = location.location_id
                where move_task.site_id = 'USCOL1' and move_task.client_id = 'SDRY-US' and location.work_zone in ('C0S','C0S2','LAUNCH','C0-RPALLET', 'TRANSIT')and move_task.status != 'Consol' or move_task.from_loc_id like '%PAL%')query7
        group by PO, Work_Group) query8
    on query2.PO = query8.PO)

Example Output

Upvotes: 0

Views: 39

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

You are using join conditions like:

on query2.PO = query8.PO

but null is never equal to (or not equal to) anything else, so if PO is null then you can't use an equality check; you need to use is null. As you want to check both you could do:

on query2.PO = query8.PO
or (query2.PO is null and query8.PO is null)

Read more in the documentation.

Upvotes: 1

Related Questions