Hello
I am fairly new to SQL and tying to work out a code. I created the following query and it wont run. The RevenueCD field is located in the [Epic].[Finance].[UBRevenueCode] table and needs to be joined to the Epic.Finance.HospitalTransaction table. The common field in both tables is [UBRevenueCodeID]. When I tried to join them I get the following error messages.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'HospitalTransaction'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'HCPCS_CPT'.
Can anyone tell me why this isn’t working?
SELECT DISTINCT
PatientMRN
, PatientNM
, dischargeDTS
, CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
, RevenueCD
FROM Epic.Finance.HospitalAccount HA
LEFT JOIN Epic.Reference.BenefitPlan PY ON HA.PrimaryPayorID = PY.PayorID
LEFT JOIN Epic.Finance.HospitalTransaction HT ON HA.HospitalAccountID = HT.HospitalAccountID
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN Epic.Reference.[Location] LOC ON HA.[DischargeEpicLocationID] = LOC.LocationID
LEFT JOIN [Epic].[Finance].[UBRevenueCode] R ON HT.HospitalTransaction = R.UBRevenueCodeID
WHERE
DISCHARGEDTS >='2019-07-01' and DISCHARGEDTS <= '2019-09-30'
or DISCHARGEDTS >='2020-07-01' and DISCHARGEDTS <= '2020-09-30'
AND RevenueCD = '0510'
or HCPCS_CPT = 'Q3014'
AND I.IdentityTypeID = '109'
AND HT.HospitalAccountClassDSC = 'Outpatient'
AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
AND HT.TransactionTypeDSC = 'Charge'
AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
October 15, 2020 at 3:30 pm
Does column HCPCS_CPT exist? In your where clause, replace this with the definition you used to calculate it
October 15, 2020 at 3:35 pm
Thanks Cebisa. I did that and I get this new error message
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'. Here is what I did.
WHERE
DISCHARGEDTS >='2019-07-01' and DISCHARGEDTS <= '2019-09-30'
or DISCHARGEDTS >='2020-07-01' and DISCHARGEDTS <= '2020-09-30'
AND RevenueCD = '0510'
or CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT = 'Q3014'
AND I.IdentityTypeID = '109'
AND HT.HospitalAccountClassDSC = 'Outpatient'
AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
AND HT.TransactionTypeDSC = 'Charge'
AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
October 15, 2020 at 3:38 pm
I just removed "END AS HCPCS_CPT" from the line and now I get error message
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '='.
Instead of removing "END AS HCPCS_CPT", just remove "AS HCPCS_CPT" - you might want to read up on the syntax of a CASE.. WHEN statement (it always ends with an END and can't have an AS after it )
Other things you might want to look at:
Using DISTINCT - remove the DISTINCT and see if you get the same number of records. If not, you are concealing a one-to many join. It's better to understand why so you can improve your coding skills. You might want to read "SQL Code Smells" by Phil Factor - SELECT DISTINCT is in there
You've used left joins, but some of the joined tables are used in the WHERE clause - that effectively makes them inner joins. Again, it would help you to understand when to use each of the join types (SQL Code Smells again)
None of the OR expressions is bracketed - I'm not sure whether you intended that, but again you might want to look at how to bracket ANDs and ORs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply