I would like to compare SUMs from two tables. Addtionally, I need to find the Sum from the OEOrdLin_Sql table when there is not a matching record(Item_No) in the IMLSTrx_SQL table, thus the outer join. I am not getting the right answers with my approach.
The first table is an Order Line table. It contains Item_No and Qty_To_Ship.
It’s SUM looks like:
SELECT item_no, SUM(qty_to_ship) AS QtyToShip
FROM OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no
The second table is a Lot Transaction table. It contains Item_No and Trx_Qty.
It’s SUM looks like:
SELECT SUM(trx_qty) AS TrxQty
FROM IMLSTRX_SQL
WHERE (item_no = '115-005-071') AND (source = 'O') AND (ctl_no = 0)
The SUM for the OEOrdLin_SQL table is 4.
The SUM for the IMLSTrx_SQL table is 4.
Here is my attempt to compare.
SELECT L.item_no, SUM(L.qty_to_ship) AS QtyToShip, SUM(T.trx_qty) AS TrxQty
FROM OEORDLIN_SQL L LEFT OUTER JOIN
IMLSTRX_SQL T ON L.item_no = T.item_no
WHERE (T.source = 'O') AND (T.ctl_no = 0) AND (L.item_no = '115-005-071')
GROUP BY L.item_no
My attempt yields QtyToShip = 16 and TrxQty = 20.
What is the right approach?
Thanks,
Pat
Ultimately, I need to only return those records where QtyToShip <> TrxQty so I will need to add a conditional in the Where or Having clause.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
It worked great. Here is the final query. I made it a Full Join. I need to catch any orders that do not have LotNos attached and I need to catch any LotNos that are not attached to an order.
SELECT L.item_no, L.ord_no, L.line_no, SUM(ISNULL(L.qty_to_ship, 0)) AS Qty_TO_Ship, ISNULL
((SELECT SUM(T.Trx_Qty)
FROM IMLSTrx_SQL T
WHERE L.ITem_No = T.Item_No AND L.Ord_No = T.Ord_No AND L.Line_No = T.Line_No AND T.Source = 'O' AND T.Ctl_No = 0), 0) AS Trx_Qty,
T.ser_lot_no
FROM OEORDLIN_SQL L FULL OUTER JOIN
IMLSTRX_SQL T ON L.item_no = T.item_no AND L.ord_no = T.ord_no AND L.line_no = T.line_no
WHERE (T.source = 'O') AND (T.ctl_no = 0)
GROUP BY L.item_no, L.ord_no, L.line_no, T.ser_lot_no
HAVING (SUM(ISNULL(L.qty_to_ship,
((SELECT SUM(T.Trx_Qty)
FROM IMLSTrx_SQL T
WHERE L.ITem_No = T.Item_No AND L.Ord_No = T.Ord_No AND L.Line_No = T.Line_No AND T.Source = 'O' AND T.Ctl_No = 0), 0))
Business Accounts
Answer for Membership
by: itsvtkPosted on 2006-10-19 at 12:42:47ID: 17768980
try this
SELECT item_no, SUM(qty_to_ship) AS QtyToShip, sum(SELECT trx_qty AS TrxQty
FROM IMLSTRX_SQL
WHERE (item_no = L.item_no) AND (source = 'O') AND (ctl_no = 0))
FROM OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no
itsvtk