Toad World® Forums

Improper column reference in the search condition of a joined table


#1

Hi.

I have looked at other posts with this similar problem for guidance but cannot seem to find where it is that this is occuring in my script.

Perhaps I am trying to do too much in this one script?

Maybe someone with more experience might spot the problem.

Thank you for any assistance.

WHP

CREATE TABLE ar_asrh_dm_etl_beacon.Flg_ERAlgo1 AS (
SELECT DISTINCT RANK() OVER( PARTITION BY fca.Mem_id
ORDER BY fca.Dte_Admit_id ASC, fca.Dte_Disch_id ASC, fca.Claim_id ASC, spr.ProvRend_id ASC ) AS line_rank
, fca.Mem_id AS AdmitMem_id
, fca.Claim_id AS AdmitClaim_id
, ldx.Dx_cd AS AdmitDx_cd
, ldx.Dx_dl AS AdmitDx_dl
, lps.PatStat_dl AS AdmitPatStat_dl
, fca.PatStat_id AS AdmitPatStat_id
, spr.ProvRend_id AS AdmitProvRend_id
, spr.ProvRend_LastName AS AdmitProvRend_LastName
, fca.Dte_Admit_id AS AdmitDte_Admit_id
, fca.Dte_Disch_id AS AdmitDte_Disch_id
, ama.Admission_id AS Admission_id1
, adm.Admission_id AS Admission_id2
, ama.DRG_Final_Id AS DRG_Final_Id
, ama.DRG_Final4_Id AS DRG_Final4_Id
, ama.Dte_Disch_Id AS Dte_Disch_Id1
, sty.Dte_Disch_Id AS Dte_Disch_Id2
, ama.MS_Indic_Final_Id AS MS_Indic_Final_Id1
, adm.Dte_Admit_id AS Dte_Admit_id1
, adm.PatStat_DRG_id AS PatStat_DRG_id
, adm.PatStat_id AS PatStat_id
, sty.ProvRend_Id AS ProvRend_Id
, sty.Stay_Id AS Stay_Id
, fca.DX_Prim_Id AS DX_Prim_Id
, faa.DRG4_ds AS DRG4_ds

FROM ar_asrh_ap_etl_beacon.Fv_Claimline_Apport       fca

INNER JOIN ar_asrh_ap_etl_beacon.Lv_ProvRendVer lpr ON fca.ProvRendVerIdent_id = lpr.ProvRendVerIdent_id
INNER JOIN ( SELECT lpv.ProvRendVerIdent_id
, lpv.ProvRend_id
, lpv.ProvRend_LastName
FROM ar_asrh_ap_etl_beacon.Lv_ProvRendVer lpv
INNER JOIN ( SELECT ProvRend_id
, MAX( ProvRendVerIdent_id ) AS ProvRendVerIdent_id
FROM ar_asrh_ap_etl_beacon.Lv_ProvRendVer
GROUP BY 1
) AS mxp ON lpv.ProvRend_id = mxp.ProvRend_id AND
lpv.ProvRendVerIdent_id = mxp.ProvRendVerIdent_id
) AS spr ON lpr.ProvRend_id = spr.ProvRend_id

     INNER JOIN ar_asrh_ap_etl_beacon.Lv_PatStat        lps   ON fca.PatStat_id = lps.PatStat_id
     INNER JOIN ar_asrh_ap_etl_beacon.Lv_Dx_Prim        ldx   ON fca.Dx_Prim_id = ldx.Dx_id
     INNER JOIN ar_asrh_ap_etl_beacon.FV_Admission_Ams  ama   ON adm.Admission_Id = ama.Admission_id
     INNER JOIN ar_asrh_ap_etl_beacon.FV_Admission      adm   ON sty.Mem_Id = adm.Mem_Id
     INNER JOIN ar_asrh_ap_etl_beacon.LV_Stay           sty   ON fca.Stay_Id = sty.Stay_Id AND
                                                                 fca.Mem_Id = sty.Mem_Id
     INNER JOIN ar_asrh_ap_etl_beacon.Lv_DRG4_Final_onAdmission_Ass faa ON ama.DRG_Final4_Id = faa.DRG4_Id
  
WHERE fca.Dte_SvcFrom_id BETWEEN '2008-07-01' AND '2011-06-30'
  AND fca.SvcPlace_id IN (36, 37, 38) /*Inpatient Hospital ER Outpatient Hospital*/
  AND fca.ClaimCategory_id IN (4, 3) /*Inpatient Outpatient*/
  AND fca.AdjustIndic_id = 1
  AND fca.ClaimPayStat_ClaimLine_id = 5
  AND fca.F_PAID_AM > 0
  AND fca.Dte_Disch_id <> '0001-01-01'
  AND fca.Dte_Admit_id <> '0001-01-01'

) WITH DATA UNIQUE PRIMARY INDEX ( AdmitMem_id, AdmitClaim_id, AdmitProvRend_id, line_rank );