title: Why is UNION causing ''multi part identifier could not be bound' error
body: <p>I have read many different questions on SO regarding this issue however I am still unable to find what is the problem with my code.  I have been struggling for days with this and now am unable to think of anything else to try.
  I can run the entire code in SQL query pane and it runs and gives the expected results however when put into SSRS report the 'multi part identifier....' error is displayed.  If I split the UNION report into its 2 separate parts each part can be loaded into the SSRS report without any error displaying.
  Having read the following posts I have checked:
  <a href="https://stackoverflow.com/questions/10811576/the-multi-part-identifier-could-not-be-bound">The multi-part identifier could not be bound</a>
  --change Group By-- (tried with and without Order By)</p>
  
  <p><a href="https://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound">The multi-part identifier could not be bound</a>
  --implicit and explicit joins -- (all joins explicit I think)</p>
  
  <p><a href="https://stackoverflow.com/questions/14082520/the-multi-part-identifier-could-not-be-bound">The multi-part identifier could not be bound</a>
  check table naming -- (all tables renamed with alias and joined correctly I think)</p>
  
  <p>I have also checked that all the naming is the same in both halves of the union so I am confused why the UNION is causing the error.</p>
  
  <p>My code:
  (I apologise for the length but it is difficult to know how to simplify it and give a true impression of the problem)</p>
  
  <pre><code>--The multi-part identifier "gor.gorec" could not be bound.
  --The multi-part identifier "gos.gosent" could not be bound.
  --C1 
  
  --all ms 46 completed where same company not counted as output previously--
  SELECT        m.ccx_name AS MilestoneName, m.ccx_status, m.ccx_statusname AS MilestoneStatus, proj.ccx_programmename AS Programme, 'C1' AS OutputNumber, proj.ccx_projectnumber AS ProjectNumber, 
                           comp.name AS CompanyName, CASE WHEN comp.tsg_localregionpicklistname IN ('Devon', 'Somerset') 
                           THEN 'Heart of South West' WHEN comp.tsg_localregionpicklistname LIKE 'Isles of Scilly' THEN 'Cornwall' WHEN comp.tsg_localregionpicklistname IN ('Gloucestershire', 'Cornwall', 'Swindon &amp; Wiltshire', 
                           'West of England', 'Dorset') THEN comp.tsg_localregionpicklistname ELSE 'Out of Region' END AS LEPregion, proj.ccx_projectstart AS ProjectStartDate, m.ccx_datecompleted AS AchievedDate, 
                           jsc.fs_anticipatedjobs AS AnticipatedJobs, g.ccx_valueofgrant AS GrantOffer, gor.gorec as GrantOfferReceived, gos.gosent as GrantOfferSent,
                          CASE WHEN gor.gorec = 1 THEN 0
                          WHEN gos.gosent = 1 THEN 1
                          ELSE '' END AS AnticipatedGrant,
                           consult.fullname AS Adviser
  FROM               Filteredccx_milestone AS m INNER JOIN
                           Filteredccx_project AS proj ON m.ccx_project = proj.ccx_projectid INNER JOIN
                           FilteredAccount AS comp ON proj.ccx_customerid = comp.accountid LEFT OUTER JOIN
                           Filteredccx_jobscreatedorsafeguarded AS jsc ON proj.ccx_projectid = jsc.ccx_project LEFT OUTER JOIN
                           Filteredccx_grant AS g ON proj.ccx_grantid = g.ccx_grantid LEFT OUTER JOIN
                           Filteredccx_ccx_project_contact_consultant AS link ON proj.ccx_projectid = link.ccx_projectid LEFT OUTER JOIN
                           FilteredContact AS consult ON link.contactid = consult.contactid LEFT OUTER JOIN
                           (select M1.ccx_project,1 as gorec
                                  FROM Filteredccx_milestone as M1
                                  WHERE (M1.ccx_name LIKE '45b%') AND (M1.ccx_status = '803080001'))AS gor ON gor.ccx_project=proj.ccx_projectid LEFT OUTER JOIN
                           (select M2.ccx_project,1 as gosent
                                  FROM Filteredccx_milestone as M2
                                  WHERE (M2.ccx_name LIKE '45 Grant%') AND (M2.ccx_status = '803080001')) AS gos ON gos.ccx_project=proj.ccx_projectid
  
  WHERE        (m.ccx_name LIKE '46%') AND (m.ccx_datecompleted BETWEEN '2017/04/01' AND '2017/07/01') AND (m.ccx_status = '803080001') AND 
                           (proj.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209') AND (comp.tsg_companyuid NOT IN ('COMP00153968', 'COMP00091748', 'COMP00091284', 'COMP00177586', 'COMP00231427', 
                           'COMP00077428', 'COMP00077490')) OR
                           (m.ccx_name LIKE '46%') AND (m.ccx_datecompleted BETWEEN '2017/04/01' AND '2017/07/01') AND (m.ccx_status = '803080001') AND 
                           (proj.ccx_programme = '14286237-FE36-E611-8870-005056A52209') AND (comp.tsg_companyuid NOT IN ('COMP00153968', 'COMP00091748', 'COMP00091284', 'COMP00177586', 'COMP00231427', 
                           'COMP00077428', 'COMP00077490')) AND (comp.accountid NOT IN
                               (SELECT        FilteredAccount.accountid
                                 FROM            Filteredccx_milestone INNER JOIN
                                                           Filteredccx_project ON Filteredccx_milestone.ccx_project = Filteredccx_project.ccx_projectid INNER JOIN
                                                           FilteredAccount ON Filteredccx_project.ccx_customerid = FilteredAccount.accountid
                                 WHERE        (Filteredccx_milestone.ccx_name LIKE '46%') AND (Filteredccx_milestone.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone.ccx_status = '803080001') AND 
                                                           (Filteredccx_project.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone.ccx_name LIKE '46%') AND (Filteredccx_milestone.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone.ccx_status = '803080001') AND 
                                                           (Filteredccx_project.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone.ccx_name LIKE '45b%') AND (Filteredccx_milestone.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone.ccx_status = '803080001') AND 
                                                           (Filteredccx_project.ccx_programme = 'D8943876-545A-E711-80FE-0050569FE3BD') OR
                                                           (Filteredccx_milestone.ccx_name LIKE '45b%') AND (Filteredccx_milestone.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone.ccx_status = '803080001') AND 
                                                           (Filteredccx_project.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone.ccx_name LIKE '45b%') AND (Filteredccx_milestone.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone.ccx_status = '803080001') AND 
                                                           (Filteredccx_project.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209')))
  UNION
  SELECT        m.ccx_name AS MilestoneName, m.ccx_status, m.ccx_statusname AS MilestoneStatus, proj.ccx_programmename AS Programme, 'C1' AS OutputNumber, proj.ccx_projectnumber AS ProjectNumber, 
                           comp.name AS CompanyName, CASE WHEN comp.tsg_localregionpicklistname IN ('Devon', 'Somerset') 
                           THEN 'Heart of South West' WHEN comp.tsg_localregionpicklistname LIKE 'Isles of Scilly' THEN 'Cornwall' WHEN comp.tsg_localregionpicklistname IN ('Gloucestershire', 'Cornwall', 'Swindon &amp; Wiltshire', 
                           'West of England', 'Dorset') THEN comp.tsg_localregionpicklistname ELSE 'Out of Region' END AS LEPregion, proj.ccx_projectstart AS ProjectStartDate, m.ccx_datecompleted AS AchievedDate, 
                           jsc.fs_anticipatedjobs AS AnticipatedJobs, g.ccx_valueofgrant AS GrantOffer, gor.gorec as GrantOfferReceived, gos.gosent as GrantOfferSent,
                          CASE WHEN gor.gorec = 1 THEN 0
                          WHEN gos.gosent = 1 THEN 1
                          ELSE '' END AS AnticipatedGrant, 
                            consult.fullname AS Adviser
  FROM               Filteredccx_milestone AS m INNER JOIN
                           Filteredccx_project AS proj ON m.ccx_project = proj.ccx_projectid INNER JOIN
                           FilteredAccount AS comp ON proj.ccx_customerid = comp.accountid LEFT OUTER JOIN
                           Filteredccx_jobscreatedorsafeguarded AS jsc ON proj.ccx_projectid = jsc.ccx_project LEFT OUTER JOIN
                           Filteredccx_grant AS g ON proj.ccx_grantid = g.ccx_grantid LEFT OUTER JOIN
                           Filteredccx_ccx_project_contact_consultant AS link ON proj.ccx_projectid = link.ccx_projectid LEFT OUTER JOIN
                           FilteredContact AS consult ON link.contactid = consult.contactid LEFT OUTER JOIN
  
                          (select M1.ccx_project,1 as gorec
                                  FROM Filteredccx_milestone as M1
                                  WHERE (M1.ccx_name LIKE '45b%') AND (M1.ccx_status = '803080001'))AS gor ON gor.ccx_project=proj.ccx_projectid LEFT OUTER JOIN
  
                           (select M2.ccx_project,1 as gosent
                                  FROM Filteredccx_milestone as M2
                                  WHERE (M2.ccx_name LIKE '45 Grant%') AND (M2.ccx_status = '803080001')) AS gos ON gos.ccx_project=proj.ccx_projectid
  
  WHERE        (m.ccx_name LIKE '45b%') AND (m.ccx_datecompleted BETWEEN '2017/04/01' AND '2017/07/01') AND (m.ccx_status = '803080001') AND 
                           (proj.ccx_programme = 'D8943876-545A-E711-80FE-0050569FE3BD') AND (comp.tsg_companyuid NOT IN ('COMP00153968', 'COMP00091748', 'COMP00091284', 'COMP00177586', 'COMP00231427', 
                           'COMP00077428', 'COMP00077490')) AND (comp.accountid NOT IN
                               (SELECT        FilteredAccount_3.accountid
                                 FROM            Filteredccx_milestone AS Filteredccx_milestone_3 INNER JOIN
                                                           Filteredccx_project AS Filteredccx_project_3 ON Filteredccx_milestone_3.ccx_project = Filteredccx_project_3.ccx_projectid INNER JOIN
                                                           FilteredAccount AS FilteredAccount_3 ON Filteredccx_project_3.ccx_customerid = FilteredAccount_3.accountid
                                 WHERE        (Filteredccx_milestone_3.ccx_name LIKE '46%') AND (Filteredccx_milestone_3.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_3.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_3.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_3.ccx_name LIKE '46%') AND (Filteredccx_milestone_3.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_3.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_3.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_3.ccx_name LIKE '45b%') AND (Filteredccx_milestone_3.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_3.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_3.ccx_programme = 'D8943876-545A-E711-80FE-0050569FE3BD') OR
                                                           (Filteredccx_milestone_3.ccx_name LIKE '45b%') AND (Filteredccx_milestone_3.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_3.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_3.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_3.ccx_name LIKE '45b%') AND (Filteredccx_milestone_3.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_3.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_3.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209'))) OR
  
                           (m.ccx_name LIKE '45b%') AND (m.ccx_datecompleted BETWEEN '2017/04/01' AND '2017/07/01') AND (m.ccx_status = '803080001') AND 
                           (proj.ccx_programme = '14286237-FE36-E611-8870-005056A52209') AND (comp.tsg_companyuid NOT IN ('COMP00153968', 'COMP00091748', 'COMP00091284', 'COMP00177586', 'COMP00231427', 
                           'COMP00077428', 'COMP00077490')) AND (comp.accountid NOT IN
                               (SELECT        FilteredAccount_2.accountid
                                 FROM            Filteredccx_milestone AS Filteredccx_milestone_2 INNER JOIN
                                                           Filteredccx_project AS Filteredccx_project_2 ON Filteredccx_milestone_2.ccx_project = Filteredccx_project_2.ccx_projectid INNER JOIN
                                                           FilteredAccount AS FilteredAccount_2 ON Filteredccx_project_2.ccx_customerid = FilteredAccount_2.accountid
                                 WHERE        (Filteredccx_milestone_2.ccx_name LIKE '46%') AND (Filteredccx_milestone_2.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_2.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_2.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_2.ccx_name LIKE '46%') AND (Filteredccx_milestone_2.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_2.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_2.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_2.ccx_name LIKE '45b%') AND (Filteredccx_milestone_2.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_2.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_2.ccx_programme = 'D8943876-545A-E711-80FE-0050569FE3BD') OR
                                                           (Filteredccx_milestone_2.ccx_name LIKE '45b%') AND (Filteredccx_milestone_2.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_2.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_2.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_2.ccx_name LIKE '45b%') AND (Filteredccx_milestone_2.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_2.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_2.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209'))) OR
  
                           (m.ccx_name LIKE '45b%') AND (m.ccx_datecompleted BETWEEN '2017/04/01' AND '2017/07/01') AND (m.ccx_status = '803080001') AND 
                           (proj.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209') AND (comp.tsg_companyuid NOT IN ('COMP00153968', 'COMP00091748', 'COMP00091284', 'COMP00177586', 'COMP00231427', 
                           'COMP00077428', 'COMP00077490')) AND (comp.accountid NOT IN
                               (SELECT        FilteredAccount_1.accountid
                                 FROM            Filteredccx_milestone AS Filteredccx_milestone_1 INNER JOIN
                                                           Filteredccx_project AS Filteredccx_project_1 ON Filteredccx_milestone_1.ccx_project = Filteredccx_project_1.ccx_projectid INNER JOIN
                                                           FilteredAccount AS FilteredAccount_1 ON Filteredccx_project_1.ccx_customerid = FilteredAccount_1.accountid
                                 WHERE        (Filteredccx_milestone_1.ccx_name LIKE '46%') AND (Filteredccx_milestone_1.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_1.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_1.ccx_name LIKE '46%') AND (Filteredccx_milestone_1.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_1.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_1.ccx_name LIKE '45b%') AND (Filteredccx_milestone_1.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_1.ccx_programme = 'D8943876-545A-E711-80FE-0050569FE3BD') OR
                                                           (Filteredccx_milestone_1.ccx_name LIKE '45b%') AND (Filteredccx_milestone_1.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_1.ccx_programme = '14286237-FE36-E611-8870-005056A52209') OR
                                                           (Filteredccx_milestone_1.ccx_name LIKE '45b%') AND (Filteredccx_milestone_1.ccx_datecompleted &lt; '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                           (Filteredccx_project_1.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209')))
  ORDER BY CompanyName
  </code></pre>