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
aneilgAsked:
Who is Participating?
 
waltersnowslinarnoldCommented:
The problem is not with JOINs, you can perform JOIN in any part of a UNION. but the SELECT clause in the last has column without proper alias name, which prompts the error. I mean to say is, The below columns you mentioned in the last SELECT clause may be available in multiple tables in the JOIN tables, so please restrict it by saying alias names before each column. That would solve the issue you facing.

  [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]
0
 
aneilgAuthor Commented:
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.
0
 
waltersnowslinarnoldCommented:
You should actually give the alias name "PRIMARY_DIAGNOSIS_DESC" provided in the outer SELECT Clause. This should solve the problem.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aneilgAuthor Commented:
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
0
 
waltersnowslinarnoldCommented:
A sample query for little more clarity

SELECT
subQuery.col1, subQuery.col2
FROM
(
 SELECT col1,col2, col3
 FROM tablename
) subQuery
0
 
Patrick TallaricoFSEP Systems AnalystCommented:
is the field ICD104NM part of the table's constraints or clustered key(s)?
0
 
aneilgAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.