Teresa Strzepka
asked on
Lookup Error - SQL Server Database Error: The multi-part identifier "AG.AGREE_NUM" could not be bound.
Data from Agreement and Agreement_Items is exported to two tables daily. On occasions number of Agreement_Items per an agreement in the imported table is not matching the count in the source table. I need to display/compare the counts . unfortunately my query gives me error: Lookup Error - SQL Server Database Error: The multi-part identifier "AG.AGREE_NUM" could not be bound.
SELECT SIEB.AGREE_NUM, SIEB.SIEBEL_COUNT, SAP.SAP_COUNT
FROM
(
SELECT AG.AGREE_NUM , COUNT (AGI.DOC_AGREE_ID) AS SIEBEL_COUNT
FROM Siebel.dbo.S_DOC_AGREE AG , Siebel.dbo.S_AGREE_ITEM AGI
WHERE AG.ROW_ID = AGI.DOC_AGREE_ID AND AG.AGREE_CD = 'Service Contract'
GROUP BY AG.AGREE_NUM
) SIEB
INNER JOIN
(
SELECT sp.VBELN, COUNT(cl.VBELN) AS SAP_COUNT
FROM SAP.dbo.VBAK sp , SAP.dbo.VBAP cl
WHERE sp.VBELN = cl.VBELN AND sp.AUART = 'ZWV1'
GROUP BY sp.VBELN
) SAP
ON AG.AGREE_NUM = sp.VBELN
ORDER BY SIEB.AGREE_NUM
SELECT SIEB.AGREE_NUM, SIEB.SIEBEL_COUNT, SAP.SAP_COUNT
FROM
(
SELECT AG.AGREE_NUM , COUNT (AGI.DOC_AGREE_ID) AS SIEBEL_COUNT
FROM Siebel.dbo.S_DOC_AGREE AG , Siebel.dbo.S_AGREE_ITEM AGI
WHERE AG.ROW_ID = AGI.DOC_AGREE_ID AND AG.AGREE_CD = 'Service Contract'
GROUP BY AG.AGREE_NUM
) SIEB
INNER JOIN
(
SELECT sp.VBELN, COUNT(cl.VBELN) AS SAP_COUNT
FROM SAP.dbo.VBAK sp , SAP.dbo.VBAP cl
WHERE sp.VBELN = cl.VBELN AND sp.AUART = 'ZWV1'
GROUP BY sp.VBELN
) SAP
ON AG.AGREE_NUM = sp.VBELN
ORDER BY SIEB.AGREE_NUM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On another note, it is bad style to mix old and new join syntax. You use the non-ANSI form in your subselects, but join the subselects with ANSI joins ...
ASKER
Thank you very much. It worked perfectly.
I will change the query so the new and old syntax won't be mixed.
Many Thanks and Kind Regards
I will change the query so the new and old syntax won't be mixed.
Many Thanks and Kind Regards