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

UDA
Enthusiast

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

6 REPLIES
Senior Apprentice

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

Hi Nick,
don't mix old style and ANSI style joins.
Add a JOIN to the Blank_customer.

Dieter

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

Move the last left outer join to the first part of from statement as it makes no sense in the second part.
If it has to be joined with the second part only, find a common column between TABLE_E and Blank_Customer or TABLE_D.
When ur aim is to avoid cross join,you have to get any common column inbetween Table A or B and Blank customer or D.
Senior Apprentice

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

INNER JOIN Sales.Invoice_Price_item_Cur PRI

ON PRI.Invoice_Id =INV.Invoice_I

AND Part.Geo_cd = PRI.Geo_cd

In the join to PRI you use a column from Part, which is not joined, yet.

So simply join Part first.

Btw, is this join really correct?


Dieter

Senior Apprentice

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

Does the SELECT return any rows?

Syntactically the join is correct now, but logically?

Maybe you have to use some Outer Joins instead, but you must know if the result set is correct or not...

Dieter

Senior Apprentice

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

How long does the SELECT run?

What's the Explain/DDL/statistics of the tables?

Dieter

Senior Apprentice

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

Running out of spool is a bit strange, there must have been some additional sessions by the same user using spool.

There are lots of "no confidence" retrieve steps, indicating missing statistics. The number of rows is overestimated by a factor of 10000.

Which stats are defined and what's the PI/partitioning?

Dieter