Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
actsql
Starting Member
34 Posts |
Posted - 2010-10-15 : 23:21:00
|
I am getting this error:Msg 4104, Level 16, State 1, Line 1The multi-part identifier "B.ITEMNO" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "B.WHOUSE" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "B.WHOUSE" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "B.SUMQTY" could not be bound.When I try to run this query:SELECT C.ITEMNO, B.WHOUSE, B.SUMQTYFROM ( SELECT ITEMNO, SUM(SUMQTY) AS TOTSUM FROM ( SELECT ITEMNO, WHOUSE, Sum(QTY) AS SUMQTY FROM ( (SELECT t.item_id AS ITEMNO, dbo.inventory_receipts_d.warehouse_no AS WHOUSE, ISNULL(dbo.inventory_receipts_d.received_quantity,0)AS QTY FROM dbo.inventory_receipts_d JOIN dbo.items t ON dbo.inventory_receipts_d.item_id= t.item_id WHERE t.primary_item_category_no='INVEN' AND t.record_status='A' AND dbo.inventory_receipts_d.warehouse_no IS NOT NULL) UNION ALL (SELECT u.item_id AS ITEMNO, dbo.inventory_activity_transfer_d.warehouse_no AS WHOUSE, ( CASE WHEN dbo.inventory_activity_transfer_d.transfer_type = 'F' AND dbo.inventory_activity_transfer_d.move_from_to='W' AND dbo.inventory_activity_d.item_id = u.item_id AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL THEN (-1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0) WHEN dbo.inventory_activity_transfer_d.transfer_type = 'T' AND dbo.inventory_activity_transfer_d.move_from_to='W' AND dbo.inventory_activity_d.item_id = u.item_id AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL THEN (1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0) ELSE 0 END) AS QTY FROM dbo.items u JOIN dbo.inventory_activity_d ON u.item_id = dbo.inventory_activity_d.item_id JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no WHERE u.primary_item_category_no='INVEN' AND u.record_status='A' AND dbo.inventory_activity_transfer_d.warehouse_no IS NOT NULL) ) A GROUP BY ITEMNO, WHOUSE ) B WHERE SUMQTY>0 GROUP BY ITEMNO ) C WHERE C.ITEMNO = B.ITEMNO AND C.TOTSUM<0 GROUP BY C.ITEMNO, B.WHOUSEI think it is because the named queries are improperly referenced, but I have made several changes without success. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
actsql
Starting Member
34 Posts |
Posted - 2010-10-16 : 08:15:04
|
Thanks for your post. I made the changes you suggested. This script works:--SELECT C.ITEMNO, B.WHOUSE, B.SUMQTY----FROM ( SELECT ITEMNO, SUM(SUMQTY) AS TOTSUM FROM ( SELECT ITEMNO, WHOUSE, Sum(QTY) AS SUMQTY FROM ( (SELECT t.item_id AS ITEMNO, q.warehouse_no AS WHOUSE, ISNULL(q.received_quantity,0)AS QTY FROM dbo.inventory_receipts_d q JOIN dbo.items t ON q.item_id= t.item_id WHERE t.primary_item_category_no='INVEN' AND t.record_status='A' AND q.warehouse_no IS NOT NULL) UNION ALL (SELECT u.item_id AS ITEMNO, r.warehouse_no AS WHOUSE, ( CASE WHEN r.transfer_type = 'F' AND r.move_from_to='W' AND s.item_id = u.item_id AND r.inv_rcpt_trx_id IS NULL THEN (-1)*isnull(r.stocking_quantity,0) WHEN r.transfer_type = 'T' AND r.move_from_to='W' AND s.item_id = u.item_id AND r.inv_rcpt_trx_id IS NULL THEN (1)*isnull(r.stocking_quantity,0) ELSE 0 END) AS QTY FROM dbo.items u JOIN dbo.inventory_activity_d s ON u.item_id = s.item_id JOIN dbo.inventory_activity_transfer_d r ON s.transaction_no = r.transaction_no AND s.line_no = r.line_no WHERE u.primary_item_category_no='INVEN' AND u.record_status='A' AND r.warehouse_no IS NOT NULL) ) A GROUP BY ITEMNO, WHOUSE ) B WHERE SUMQTY>0 GROUP BY ITEMNO ) /*C*/ ---- WHERE C.ITEMNO = B.ITEMNO-- AND C.TOTSUM<0---- GROUP BY C.ITEMNO, B.WHOUSEHowever, when I un-comment the statements at the top and bottom I get the original error. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-16 : 13:32:17
|
It is because of this:The innermost derived table called A gives 3 Columns: ITEMNO, WHOUSE, QTYOnly the next derived table called B can select from A and gives 3 Columns: ITEMNO, WHOUSE, SUMQTYOnly the next derived table called C can select from B and gives 2 Columns: ITEMNO, TOTSUMYour outermost select can only select from the derived table called C and hence it can reach only the Columns ITEMNO, TOTSUM No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
actsql
Starting Member
34 Posts |
Posted - 2010-10-16 : 18:09:41
|
Thank you. That explains why it is not working. Back to the drawing board. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-16 : 18:16:16
|
welcome No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|