Improper column reference in the search condition of a joined table

Database

Improper column reference in the search condition of a joined table

Through process of elimination the 3782 error seems to be caused by the T1.KSN_ID in the on clauses of the query. I don't know what to do to fix this. Any help would be appreciated...Thanks

SELECT
DATE as SYS_DATE,
T1.KSN_ID,
T1.ITEM_ID,
T1.DVSN_NBR,
T2.CAN_CARR_MDL_ID,
T2.NETWORK_DISTRB_CD,
T3.KM_DAY70_CHK_STR_QTY,
T5.LOCN_NBR,
T5.SERV_DC_NBR,
b.DC_CD,
c.THIRTEEN_WK_FRCST,
SUM (DISTINCT B.OO_QTY),
SUM (DISTINCT B.OH_QTY),
SUM (DISTINCT D.STR_OH_QTY),
SUM (DISTINCT D.STR_OO_QTY_R),
SUM (DISTINCT D.STR_OO_QTY_NR)

from

Dataview.ksn T1,
Dataview.item T2,
Dataview.Item_store_count_c T3,
Dataview.ITEM_LOCN_SERV_DC T5

LEFT JOIN

 (Select
   J2.KSN_ID as KSN_ID,
   J2.DC_CD as DC_CD,
   J2.LOCN_NBR as LOCN_NBR,
   Sum (J2.ON_ORD_QTY) as OO_QTY,
   Sum (J2.ON_HAND_QTY) as OH_QTY
        
   FROM
   Dataview.DC_STK_STAT_C J2     
                
    GROUP BY
    1,2,3) b
   
    on (T1.KSN_ID= b.KSN_ID and T5.SERV_DC_NBR = b.LOCN_NBR)
   
LEFT JOIN
 (Select
    J4.KSN_ID,
    J4.ITEM_ID,
    J4.LOCN_NBR,
    SUM(FRCST_13_WK_QTY) as THIRTEEN_WK_FRCST
    
    From 
    Dataview.item_locn_replen J4 
    
     GROUP BY
    
     1,2,3) c
    
     on (T1.KSN_ID = c.KSN_ID and T5.SERV_DC_NBR = c.LOCN_NBR)   
  
JOIN

  (Select
    J3.KSN_ID,
    J3.LOCN_NBR,
    Sum ( J3.SALBL_QTY) as STR_OH_QTY,
    Sum( J3.REPLEN_ON_ORD_QTY) as STR_OO_QTY_R,
    Sum(J3.NON_REPLEN_ON_ORD_QTY) as STR_OO_QTY_NR
    
    From
    
    Dataview.STR_STK_STAT_C J3

    Group by
    
    1,2) d
    
    on (T1.KSN_ID = d.KSN_ID and T5.LOCN_NBR = d.LOCN_NBR) 
  
where
T1.KSN_ID IN
(48464611)

AND
T1.ITEM_ID = T2.ITEM_ID
AND
T1.ITEM_ID = T3.ITEM_ID
AND
T1.ITEM_ID = T5.ITEM_ID

GROUP BY
1,2,3,4,5,6,7,8,9

ORDER BY
1);
2 REPLIES
Teradata Employee

Re: Improper column reference in the search condition of a joined table

The ON clause may only reference the two tables on either side of the JOIN clause. To reference other tables you must put those conditions in a WHERE clause.

Enthusiast

Re: Improper column reference in the search condition of a joined table

SELECT DATE as SYS_DATE,
T1.KSN_ID,
T1.ITEM_ID,
T1.DVSN_NBR,
T2.CAN_CARR_MDL_ID,
T2.NETWORK_DISTRB_CD,
T3.KM_DAY70_CHK_STR_QTY,
T5.LOCN_NBR,T5.SERV_DC_NBR,
b.DC_CD,
c.THIRTEEN_WK_FRCST,
SUM (DISTINCT B.OO_QTY),
SUM (DISTINCT B.OH_QTY),
SUM (DISTINCT D.STR_OH_QTY),
SUM (DISTINCT D.STR_OO_QTY_R),
SUM (DISTINCT D.STR_OO_QTY_NR)
from Dataview.ksn T1
INNER JOIN
Dataview.item T2
ON T1.ITEM_ID = T2.ITEM_ID
INNER JOIN
Dataview.Item_store_count_c T3
ON T1.ITEM_ID = T3.ITEM_ID
INNER JOIN
Dataview.ITEM_LOCN_SERV_DC T5
ON  T1.ITEM_ID = T5.ITEM_ID
LEFT JOIN  (
Select   J2.KSN_ID as KSN_ID,   J2.DC_CD as DC_CD,   J2.LOCN_NBR as LOCN_NBR,
    Sum (J2.ON_ORD_QTY) as OO_QTY,   Sum (J2.ON_HAND_QTY) as OH_QTY           
FROM   Dataview.DC_STK_STAT_C J2                          
GROUP BY    1,2,3) b        
 on (T1.KSN_ID= b.KSN_ID  and T5.SERV_DC_NBR = b.LOCN_NBR)    
LEFT JOIN (
Select    J4.KSN_ID,     J4.ITEM_ID,    J4.LOCN_NBR,    SUM(FRCST_13_WK_QTY) as THIRTEEN_WK_FRCST        
From     Dataview.item_locn_replen J4          
GROUP BY           1,2,3) c          
 on (T1.KSN_ID = c.KSN_ID and T5.SERV_DC_NBR = c.LOCN_NBR)    
 JOIN   (
Select    J3.KSN_ID,    J3.LOCN_NBR,    Sum ( J3.SALBL_QTY) as STR_OH_QTY,
     Sum( J3.REPLEN_ON_ORD_QTY) as STR_OO_QTY_R,    Sum(J3.NON_REPLEN_ON_ORD_QTY) as STR_OO_QTY_NR        
From         Dataview.STR_STK_STAT_C J3             
Group by         1,2) d        
 on (T1.KSN_ID = d.KSN_ID and T5.LOCN_NBR = d.LOCN_NBR)  
  where T1.KSN_ID IN(48464611)
GROUP BY 1,2,3,4,5,6,7,8,9  
ORDER BY 1;

I have replaced the Condition from Where clause to the INNER JOIN. Since You have used the Join in WHERE Clause, it will perform an inner join on the T1,T2,T3,T5 Tables...

from Dataview.ksn T1

INNER JOIN

Dataview.item T2

ON T1.ITEM_ID = T2.ITEM_ID

INNER JOIN

Dataview.Item_store_count_c T3

ON T1.ITEM_ID = T3.ITEM_ID

INNER JOIN

Dataview.ITEM_LOCN_SERV_DC T5

ON  T1.ITEM_ID = T5.ITEM_ID

Thanks & Regards,

Adharssh.