asked on

SQl Query

I have three unions [1011], [1011], [1112].

With the unions you need to have the same numhber of rows in each select, which is fine.

The additional select in each union is
,[Primary Diagnosis Code],LEFT([Primary Diagnosis Code],5) AS PRIMARY_DIAGNOSIS_ICD10,ICD_PRIM_DIAG.ICD104NM AS PRIMARY_DIAGNOSIS_DESC

But my question is I only want to do the joins on the last union. But this causes a problem because Msg 4104, Level 16, State 1, Line 119
The multi-part identifier "ICD_PRIM_DIAG.ICD104NM" could not be bound.
Msg 4104, Level 16, State 1, Line 279
The multi-part identifier "ICD_PRIM_DIAG.ICD104NM" could not be bound.

Which means the join need to be in each union.

SELECT

[Primary Diagnosis Code]
,LEFT([Primary Diagnosis Code],5) AS PRIMARY_DIAGNOSIS_ICD10
,ICD_PRIM_DIAG.ICD104NM AS PRIMARY_DIAGNOSIS_DESC

,[Secondary Diagnosis Code 1]
,[Secondary Diagnosis Code 2]
,[Secondary Diagnosis Code 3]
,[Secondary Diagnosis Code 4]

FROM [0910]

UNION ALL

SELECT

[Primary Diagnosis Code]
,LEFT([Primary Diagnosis Code],5) AS PRIMARY_DIAGNOSIS_ICD10
,ICD_PRIM_DIAG.ICD104NM AS PRIMARY_DIAGNOSIS_DESC

,[Secondary Diagnosis Code 1]
,[Secondary Diagnosis Code 2]
,[Secondary Diagnosis Code 3]
,[Secondary Diagnosis Code 4]

FROM [1011]

UNION ALL

SELECT

[Primary Diagnosis Code]
,LEFT([Primary Diagnosis Code],5) AS PRIMARY_DIAGNOSIS_ICD10
,ICD_PRIM_DIAG.ICD104NM AS PRIMARY_DIAGNOSIS_DESC

,[Secondary Diagnosis Code 1]
,[Secondary Diagnosis Code 2]
,[Secondary Diagnosis Code 3]
,[Secondary Diagnosis Code 4]

FROM [1112]

LEFT OUTER JOIN
[LookUp].[dbo].[tbl_ICD10] AS ICD_PRIM_DIAG
ON ICD_PRIM_DIAG.ICD104CD = LEFT([Primary Diagnosis Code],4)

LEFT OUTER JOIN
[LookUp].[dbo].[tbl_ICD10] AS ICD_SEC1_DIAG
ON ICD_SEC1_DIAG.ICD104CD = [Secondary Diagnosis Code 1]

LEFT OUTER JOIN
[LookUp].[dbo].[tbl_ICD10] AS ICD_SEC2_DIAG
ON ICD_SEC2_DIAG.ICD104CD = [Secondary Diagnosis Code 2]

LEFT OUTER JOIN
[LookUp].[dbo].[tbl_ICD10] AS ICD_SEC3_DIAG
ON ICD_SEC3_DIAG.ICD104CD = [Secondary Diagnosis Code 3]

LEFT OUTER JOIN
[LookUp].[dbo].[tbl_ICD10] AS ICD_SEC4_DIAG
ON ICD_SEC4_DIAG.ICD104CD = [Secondary Diagnosis Code 4]

GO

ASKER CERTIFIED SOLUTION

waltersnowslinarnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aneilg

ASKER

yeah, the problem is i have been told to be it this way.

Select fieldNames from (

10/11 query

Union all

11/12 query

) as subQuery

but i get Msg 4104, Level 16, State 1, Line 121
The multi-part identifier "ICD_PRIM_DIAG.ICD104NM" could not be bound.

waltersnowslinarnold

You should actually give the alias name "PRIMARY_DIAGNOSIS_DESC" provided in the outer SELECT Clause. This should solve the problem.

aneilg

ASKER

sorry to be a pain but can you give me an example.

i've craeted a sub query.

SELECT * FROM (

) as g

then done my joins but get.

Msg 4104, Level 16, State 1, Line 121
The multi-part identifier "ICD_PRIM_DIAG.ICD104NM" could not be bound.
Msg 4104, Level 16, State 1, Line 125

waltersnowslinarnold

A sample query for little more clarity

SELECT
subQuery.col1, subQuery.col2
FROM
(
SELECT col1,col2, col3
FROM tablename
) subQuery

Patrick Tallarico

is the field ICD104NM part of the table's constraints or clustered key(s)?

aneilg

ASKER

thanks