Hi All
I am trying to run a query that has 3 sections. I had to alter an inherited code and I am getting an error. Here is the code:
DECLARE @FY AS INT;
SET @FY = 2016; /** Output shows data from the set @FY to the current date **/
DROP TABLE IF EXISTS
#Race , #Location
;
/*-------------------------------------*/
/* 1. Get Race/Ethnicity by FY and MRN */
/*-------------------------------------*/
SELECT FY
, I.PatientIdentityID AS MRN
, Race
INTO #Race
FROM
(SELECT DISTINCT
Race_Num_flg.*
,ROW_NUMBER() OVER (PARTITION BY FY, I.PatientIdentityID ORDER BY RACE_num_flg ASC) AS Race_row
,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
--WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
--WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
WHEN PatientRaceDSC IS NULL THEN 'Unknown'
ELSE 'Other' END AS 'RACE'
FROM
(SELECT DISTINCT
CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
,I.PatientIdentityID
,PatientRaceDSC
,CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
WHEN PatientRaceDSC IS NULL THEN 3
ELSE 2 END AS 'RACE_num_flg' /** RACE_num_flg = 2 captures "Other" **/
FROM Epic.Finance.HospitalTransaction HT
LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID
LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
WHERE 1=1
/* DFCI patients only */
AND I.IdentityTypeID = '109'
/* EPIC FY16 and later */
AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
/* Other condistions */
AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
) Race_num_flg
) Race_row
WHERE Race_row = 1
;
/*-------------------------------*/
/* 2. Get Location by FY and MRN */
/*-------------------------------*/
SELECT DISTINCT
HA.PatientID
, I.PatientIdentityID
, DischargeEpicLocationID
, Loc.RevenueLocationNM
, Pay.PayorID
, Pay.PayorNM
, Pat.DeathDTS
,CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END AS FY
INTO #Location
FROM Epic.Finance.HospitalAccount HA
LEFT JOIN epic.Finance.HospitalTransaction HT ON HT.HospitalAccountID=HA.HospitalAccountID
LEFT JOIN Epic.Reference.[Location] LOC ON HA.[DischargeEpicLocationID] = LOC.LocationID
LEFT JOIN [Epic].[Reference].[Payor] PAY ON HT.PayorID = PAY.PayorID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
WHERE 1=1
--AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END in (2016,2017,2018,2019,2020)
AND Pay.PayorID = '110001'
;
/*---------------------------------------------*/
/* 3. Join Race info to Location by FY and MRN */
/*---------------------------------------------*/
SELECT DISTINCT
R.*
, L.RevenueLocationNM
, L.PayorID
, L.PayorNM
, L.DeathDTS
FROM #Location L
LEFT JOIN #Race R ON L.PatientIdentityID = R.MRN AND L.FY = R.FY
I am getting the following error:
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "I.PatientIdentityID" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "I.PatientIdentityID" could not be bound.
Any assistance would be greatly appreciated.
Thanks
It looks like you're using the wrong alias in the first query. Nested queries only recognize the outermost alias, so at the top I think you'll need to select Race_row.PatientIdentityID, not I.PatientIdentityID. The outer query doesn't recognize the alias I; it's only selecting from the virtual table Race_row.
June 2, 2021 at 5:25 pm
Thanks Doug
That worked perfectly
June 2, 2021 at 5:27 pm
Sorry Doug I spoke too soon. After running it the FY, MRN and Race columns all have NULL's in each row
June 2, 2021 at 5:37 pm
However, If I just run the first and second parts individually they work. hen I join them the 3 columns I mentioned earlier are populated with NULL
June 2, 2021 at 5:40 pm
Please disregard all of those messages. It works. My apologies.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply