union and common table expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

union and common table expression

Author

Topic

tjonas
Starting Member

17 Posts

Posted - 2014-09-15 : 14:42:20

I'm trying to combine two different tables into one. Below is what I've tried but is returning invalid identifier after the DIAGNOSIS table. Any help is appreciated. Thanks.

with PL_DX as
(
select distinct clarity_edg.current_icd9_list , clarity_edg.dx_name
from patient
left join problem_list on patient.pat_id = problem_list.pat_id
left join clarity_edg on problem_list.dx_id = clarity_edg.dx_id
left join patient_3 on patient.pat_id = patient_3.pat_id
where ((patient_3.is_test_pat_yn is null) or not(patient_3.is_test_pat_yn = 'Y'))
),
ENC_DX as
(
select distinct clarity_edg.current_icd9_list , clarity_edg.dx_name
from pat_enc
left join pat_enc_dx on pat_enc.pat_enc_csn_id = pat_enc_dx.pat_enc_csn_id
left join patient on pat_enc.pat_id = patient.pat_id
left join clarity_edg on pat_enc_dx.dx_id = clarity_edg.dx_id
left join patient_3 on patient.pat_id = patient_3.pat_id
where ((patient_3.is_test_pat_yn is null) or not(patient_3.is_test_pat_yn = 'Y'))
),
DIAGNOSIS as
(
select current_icd_list , dx_name
from PL_DX
union
select current_icd9_list , dx_name
from ENC_DX
)
select current_icd9_list , dx_name
from DIAGNOSIS

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 14:56:03

It it just my eyes or does the CTE PL_DX return current_icd9_list and dx_name, yet in the union you are asking for current_icd_list, dx_name?

Could that be it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources