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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 multi part identifier could not be bound

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 1
The multi-part identifier "B.ITEMNO" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.WHOUSE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.WHOUSE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.SUMQTY" could not be bound.

When I try to run this query:

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, 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.WHOUSE

I think it is because the named queries are improperly referenced, but I have made several changes without success.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-15 : 23:38:14
Here's an example of a problem in your script:
dbo.inventory_activity_d.item_id

You can't reference a column name like this. It's just AliasName.ColumnName or ObjectName.ColumnName (where ObjectName is typically a table). You can't put dbo.TableName.ColumnName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.WHOUSE

However, when I un-comment the statements at the top and bottom I get the original error.
Go to Top of Page

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, QTY
Only the next derived table called B can select from A and gives 3 Columns: ITEMNO, WHOUSE, SUMQTY
Only the next derived table called C can select from B and gives 2 Columns: ITEMNO, TOTSUM

Your 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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-16 : 15:13:26
I got lost in all of the parenthesis and derived tables. Posting with code tags and formatting (indenting) will help us read your queries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -