Hi Nick,don't mix old style and ANSI style joins.Add a JOIN to the Blank_customer.Dieter
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.
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
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
How long does the SELECT run?
What's the Explain/DDL/statistics of the tables?
Dieter
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
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 JOINTABLE_B TMIPON 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_SalesON Cust_Sales.customer_party_id=Blank_Customer.Party_idAN D Cust_Sales.Division='00'LEFT OUTER JOIN TABLE_E TDIMONPOS.SALE_DT=TDIM.CALENDAR_DTImproper Column Reference in the search condition of a Joined Table.Error: 3782However when I remove the Join on the Table_E (the last join), it runs fine.Nick