Recently I ran into a problem with a query. I was trying to add the sums of two columns together, one of the columns happened to be in a table that was LEFT OUTER JOINed and the result was NULL when it should not have been.

This is for a purchase ordering system and is the calculation to get the total of inventory that is in transit, taking into account partial receipts of inventory.

Here is my table structure

select SUM( po_item_qty ) - SUM( po_receipt_qty ) as on_order

from po_item

left outer join po_receipt on ( po_receipt_po_item_id = po_item_id )
left outer join po on ( po_item_po_id = po_id )
left outer join product on ( po_item_product_id = product_id )

where po_status = 'in_transit'

This produces a result of ‘NULL’ when I know perfectly good and well that I have in transit purchase orders with po_item records.

It turns out that subtracting the po_receipt_qty when there are no matching po_receipt records (LEFT OUTER JOIN records return null when missing ) turns into, for example, 100 – NULL which always returns NULL.

What I need is for this to turn into 100 – 0, returning 100.

COALESCE to the rescue!

The query that made it work:

select SUM( po_item_qty ) - COALESCE( SUM( po_receipt_qty ) ,0 ) as on_order

from po_item

left outer join po_receipt on ( po_receipt_po_item_id = po_item_id )
left outer join po on ( po_item_po_id = po_id )
left outer join product on ( po_item_product_id = product_id )

where po_status = 'in_transit'