April 22, 2008 at 10:29 am
Hi all,
I have a query which is using a subquery that is giving bad results when there is no data found in the subquery. Basically it is taking the quantity of an item ordered and trying to subtract however many have been received in already. But if there are no rows found in the received table, then it does not return 0 (zero) as a SUM. It seems to return nothing, and this is causing the main query to not return anything for this item.
My query is:
SELECT d.order_id, d.product_id, p.ProductCode,
d.quantity_requested - (SELECT SUM(r.received_count) FROM product_vendor_receiving
AS r WHERE r.order_detail_id = d.id) AS qty_due, o.order_date, o.due_date
FROM product_vendor_order_details AS d
JOIN product_vendor_receiving AS r
ON d.id = r.order_detail_id
JOIN products AS p
ON d.product_id = p.id
JOIN product_vendor_orders AS o
ON d.order_id = o.id
WHERE d.quantity_requested > (SELECT SUM(r.received_count)
FROM product_vendor_receiving AS r WHERE r.order_detail_id = d.id)
AND(o.status = 'Posted' OR o.status = 'Open')
The part which I think is causing the issue is:
(SELECT SUM(r.received_count) FROM product_vendor_receiving AS r WHERE r.order_detail_id = d.id)
If there is at least one row found in the product_vendor_receiving table, then the main query returns what it is supposed to. If there are no rows found (such as if none of this product has been received yet) then it does not return any info in the main query.
I have tried using ISNULL and COALESCE in various ways but have not had any luck.
Any suggestions would be greatly appreciated!
Thanks,
Steve Stout
April 22, 2008 at 10:46 am
Nevermind, found my answer!
I needed to use:
COALESCE(SELECT SUM(r.received_count) FROM product_vendor_receiving AS r WHERE r.order_detail_id = d.id), 0)
Which I apparently hadn't tried yet...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply