All Forums Database
birddog59 2 posts Joined 03/13
07 Aug 2013
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);

 

ToddAWalter 316 posts Joined 10/11
07 Aug 2013

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.

Adharssh 36 posts Joined 08/13
21 Aug 2013
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.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

You must sign in to leave a comment.