3782: Improper column reference in the search condition of a joined table. ERROR

Database
Highlighted

3782: Improper column reference in the search condition of a joined table. ERROR

I'm feeling a little silly, but I can't seem to spot the error in the code. Any help would be appreciated, I've been looking at this way too long and am certain I'm missing something simple... I believe the error I'm looking for falls in the code highlighted in red. This query was working before I added the additional joins/where clauses. 

 

Thanks!

 

SyntaxEditor Code Snippet

SELECT
    
    TRIM(HEADER.LOC_ID) || TRIM(HEADER.POS_RGSTR_ID) || TRIM(HEADER.SLTRN_ID) || TRIM(HEADER.SLTRN_DT) || TRIM(HEADER.SLS_TM) AS TRANSACTION_ID    ,  CASE 
        WHEN DETAIL.DISC_LINE_DISC_AMT <0
            THEN (DETAIL.EXT_RETL_AMT+DETAIL.DISC_LINE_DISC_AMT)
                ELSE DETAIL.EXT_RETL_AMT
                    END                                            AS EXTENDED_SALES    
    , CASE 
            WHEN DETAIL.EXT_RETL_AMT <0 
                THEN (DETAIL.ITEM_QTY *-1) 
                    ELSE DETAIL.ITEM_QTY 
                        END                                        AS QUANTITY        
    , DETAIL.SLTRN_DT                                              AS TRANSACTION_DATE    , TRIM(ITEM.ITEM_SKU_NBR)||' '||'-'||' '||TRIM(ITEM.ITEM_DESC) AS SKU    , ITEM.ITEM_SKU_NBR                                            AS SKU_NUMBER    , ITEM.ITEM_DESC           AS ITEM_DESCRIPTION    , ITEM.DEPT_NBR            AS DEPARTMENT_NUMBER    , DETAIL.UPC_CD            AS UPC    , LOCATION.CITY_NM         AS CITY    , LOCATION.D_ST_CD         AS STATE    , LOCATION.PSTL_CD         AS ZIP    , LOCATION.CNTY_NM         AS COUNTY    , LOCATION.D_CNTRY_CD      AS COUNTRY    , LOCATION.DIV_NM        
    , LOCATION.GEO_HQ_NM    
    , LOCATION.LAT_NBR         AS LATITUDE    , LOCATION.LNG_NBR         AS LONGITUDE    , LOCATION.MGRP_NM    , LOCATION.MKT_NM           AS MARKET_NAME    , LOCATION.RELO_TO_LOC_NBR    AS STORE_NUMBER    , LOCATION.RELO_TO_STR_NM     AS STORE_NAME    , TIME_PERIOD.FSCL_WK_DESC         AS FISCAL_WEEK    , TIME_PERIOD.FSCL_PRD_DESC        AS FISCAL_PERIOD    , TIME_PERIOD.FSCL_QTR_DESC        AS FISCAL_QUARTER    , TIME_PERIOD.FSCL_HY_DESC         AS FISCAL_HALF_YEAR    , TIME_PERIOD.FSCL_YR_DESC         AS FISCAL_YEAR    , POG_CATEGORY.POG_CTGRY_NM        AS POG_CATEGORY_NAME    , POG_SPC.POG_SPC_NM               AS POG_NAME    , POG_SPC.BAY_CNT                  AS BAY_COUNT    , POG_SPC.POG_BAY_WID_VAL          AS POG_BAY_WIDTH

FROM SALES_VIEWS.SLTRN AS HEADER

JOIN SALES_VIEWS.SLTRN_LINE AS DETAIL
    ON  HEADER.SLTRN_DT = DETAIL.SLTRN_DT
    AND HEADER.SLTRN_ID = DETAIL.SLTRN_ID
    AND HEADER.LOC_ID = DETAIL.LOC_ID
    AND HEADER.POS_RGSTR_ID = DETAIL.POS_RGSTR_ID
JOIN SHRD_VIEWS.LOC_HIER AS LOCATION
    ON  LOCATION.LOC_ID = HEADER.LOC_ID
JOIN SHRD_VIEWS.ITEM_HIER AS ITEM
    ON  DETAIL.ITEM_ID = ITEM.ITEM_ID
JOIN SHRD_VIEWS.CAL_PRD_HIER AS TIME_PERIOD
    ON TIME_PERIOD.SHFT_CAL_DT = HEADER.SLTRN_DT
JOIN POG_VIEWS.POG_CTGRY AS POG_CATEGORY
          ON ITEM_CLASS_HIER.ITEM_CLASS_ID = POG_CATEGORY.ITEM_CLASS_ID
JOIN POG_VIEWS.POG_SUB_CTGRY
          ON POG_CATEGORY.POG_CTGRY_CD = POG_SUB_CTGRY.POG_CTGRY_CD
JOIN POG_VIEWS.POG_SPC
          ON POG_SUB_CTGRY.POG_SUB_CTGRY_CD = POG_SPC.POG_SUB_CTGRY_CD
JOIN POG_VIEWS.STR_POG_SPC 
          ON POG_SPC.POG_SPC_ID = STR_POG_SPC.POG_SPC_ID
JOIN POG_VIEWS.POG_SPC_POSN
          ON POG_SPC.POG_SPC_ID = POG_SPC_POSN.POG_SPC_ID
JOIN POG_VIEWS.POG_SPC_PROD
          ON POG_SPC_POSN.POG_SPC_PROD_ID = POG_SPC_PROD.POG_SPC_PROD_ID
  
WHERE HEADER.SLTRN_DT BETWEEN DATE '2017-09-25' AND '2017-10-29'
AND HEADER.TRANS_STAT_CD = 1 
AND HEADER.TRANS_TYP_CD IN (1,3)AND HEADER.CSHR_TI_FLG <> '2'
AND DETAIL.TRANS_STAT_CD = 1
AND POG_CATEGORY.ACTV_FLG = 'Y'
AND POG_SPC.ACTV_FLG = 'Y'
AND POG_SPC.POG_STAT_TYP_CD = '1'
AND POG_SPC_POSN.ACTV_FLG = 'Y'
AND STR_POG_SPC.ACTV_FLG = 'Y'
AND POG_CATEGORY.EFF_END_DT = '12/31/9999'
AND POG_SPC.EFF_END_DT = '12/31/9999'
AND POG_SPC_POSN.EFF_END_DT = '12/31/9999'
AND STR_POG_SPC.EFF_END_DT = '12/31/9999'
AND ITEM.SUB_CLASS_NBR = '025-004-002'

 


Accepted Solutions
Tourist

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

JOIN POG_VIEWS.POG_CTGRY AS POG_CATEGORY
          ON ITEM_CLASS_HIER.ITEM_CLASS_ID = POG_CATEGORY.ITEM_CLASS_ID

 I dont see the table "ITEM_CLASS_HIER" any where in the mentioned tables. May be you have to alias the table correctly somewhere or need to add that table too.

1 ACCEPTED SOLUTION
2 REPLIES
Tourist

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

JOIN POG_VIEWS.POG_CTGRY AS POG_CATEGORY
          ON ITEM_CLASS_HIER.ITEM_CLASS_ID = POG_CATEGORY.ITEM_CLASS_ID

 I dont see the table "ITEM_CLASS_HIER" any where in the mentioned tables. May be you have to alias the table correctly somewhere or need to add that table too.

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

Thank you Sansarma... I knew it was something simple... I think I had just been looking at it too long.