Error 3782 Improper Column Reference in the search condition of a Joined Table.

Database
Enthusiast

Error 3782 Improper Column Reference in the search condition of a Joined Table.

Hi,

I am getting the error "Error 3782 Improper Column Reference in the search condition of a Joined Table." when executing the below query.

SEL *
FROM TABLE_A POS

LEFT OUTER JOIN
TABLE_B TMIP
ON TMIP.ITEM_ID = POS.ITEM_ID

,(SELECT Party_ID FROM TABLE_C
WHERE Source_Party_Num = '12345678'
AND Data_Cd = 1
AND Party_Cd = 20
GROUP BY 1
) Blank_Customer

LEFT OUTER JOIN TABLE_D Cust_Sales
ON Cust_Sales.customer_party_id=Blank_Customer.Party_id
AND Cust_Sales.Division='00'

LEFT OUTER JOIN TABLE_E TDIM
ON
POS.SALE_DT=TDIM.CALENDAR_DT

Improper Column Reference in the search condition of a Joined Table.
Error: 3782

However when I remove the Join on the Table_E (the last join), it runs fine.

Nick
1 REPLY
Enthusiast

Re: Error 3782 Improper Column Reference in the search condition of a Joined Table.

SEL *
FROM TABLE_A POS

LEFT OUTER JOIN
TABLE_B TMIP
ON TMIP.ITEM_ID = POS.ITEM_ID

,(SELECT Party_ID FROM TABLE_C
WHERE Source_Party_Num = '12345678'
AND Data_Cd = 1
AND Party_Cd = 20
GROUP BY 1
) Blank_Customer
LEFT OUTER JOIN TABLE_D Cust_Sales
ON Cust_Sales.customer_party_id=Blank_Customer.Party_id
AND Cust_Sales.Division='00'


LEFT OUTER JOIN TABLE_E TDIM
ON
POS.SALE_DT=TDIM.CALENDAR_DT

There is no link of the part in BOLD to the rest of the query...a CROSS JOIN is inevitable.

But oh ! wait...you got a comma in there as well.....you have mixed up the ANSI syntax & Teradata Syntax for JOINs. In ANSI syntax the comma is replaced by the JOIN word(LEFT JOIN, RIGHT JOIN, INNER JOIN etc) and the WHERE keyword is replaced by ON....there is no room for a comma in the ANSI syntax..it was introduced to prevent people from forgetting to give the JOIN conditions and cause CROSS JOINs.