1

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: The multi-part identifier could not be bound --change Group By-- (tried with and without Order By)

The multi-part identifier could not be bound --implicit and explicit joins -- (all joins explicit I think)

The multi-part identifier could not be bound check table naming -- (all tables renamed with alias and joined correctly I think)

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.

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)

--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 & 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 < '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 < '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 < '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 < '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 < '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 & 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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                         (Filteredccx_project_1.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209')))
ORDER BY CompanyName
Caroline Allen
  • 183
  • 14

3 Answers3

2

This error is often when you have two (or more) columns with the same name. SSMS can handle that but SSRS cant. Looks like you've got two columns called anticipated grant (could be misreading the SQL though) try calling one anticipated grant2 and do the same for any other duplicated column names and see if that works.

tomdemaine
  • 738
  • 6
  • 22
  • Thank you @tomdemaine. I am not an expert in SQL I'm afraid but I thought that In this case as there is a UNION between the 2 select clauses I was supposed to give them the same name. All of the fields in the second select clause have the same alias as in the first select clause. Will a UNION work if I name all of the second select clause with different alias names? – Caroline Allen Jul 14 '17 at 07:15
  • Thank you though as you made me think that maybe I could do it without the second select clauses which has worked – Caroline Allen Jul 14 '17 at 07:21
  • Indeed in that case SSRS cannot refresh the fields, due to duplicate column names/aliases. Very good tomdemaine!! – niktrs Jul 14 '17 at 11:01
0

create as a view and get SSRS to use that instead;

create view YourData

as

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 & 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 < '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 < '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 < '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 < '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 < '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 & 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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '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 < '2017/04/01') AND (Filteredccx_milestone_1.ccx_status = '803080001') AND 
                                                         (Filteredccx_project_1.ccx_programme = '04D0B1DF-FD36-E611-8870-005056A52209')))

I'm suggesting this because you say the SQL works correctly if run outside of SSRS. SSRS might be the issue with the syntax so using a view should take that complication away from SSRS.

  • Unfortunately that does not work - I get a different error - Incorrect syntax near the keyword 'view'. Incorrect syntax near 'go'. Not sure if this is due to permissions as I am unable to create views in SQL query pane either – Caroline Allen Jul 13 '17 at 11:37
  • @CarolineAllen to clarify; if you run the SQL you posted in the 'question' section. Does that run? –  Jul 13 '17 at 11:46
  • @CarolineAllen try amended example above –  Jul 13 '17 at 11:50
  • @CarolineAllen 1 more clarification; [1] Create the view above in SQL. [2]. SSRS should simple be; select * from YourData –  Jul 13 '17 at 11:54
  • SSRS is not the issue, but the query. Encapsulating the query in a view does not change anything – niktrs Jul 13 '17 at 12:02
  • @niktrs that is not what she said in the question. She said it DOES run in SQL (as a union) but NOT in SSRS –  Jul 13 '17 at 12:09
  • I know what she said, but having faced the error many times in SSRS, it always had to do with the query. Very strange that it runs on management studio. – niktrs Jul 13 '17 at 12:18
  • @niktrs the 'view' option is just to rule out SSRS having issues with the syntax. If it runs in SQL then you'd imagine it should run elsewhere. Just ruling it out at the moment –  Jul 13 '17 at 12:19
  • Thank you all for your responses. Sorry for the delay but was still trying with the code. @JF- I am unable to create a view in SQL(no permission) so wont be able to run the select * from YourData. You are right in that I can run the query in SQL management studio query pane but not in SSRS but each seperate part does run in SSRS just not when merged by UNION. – Caroline Allen Jul 13 '17 at 13:03
  • @CarolineAllen I see. If you can get the view created if sure your issue will be resolved. You can tweak and maintain the code in the view rather than SSRS –  Jul 13 '17 at 13:06
  • I will ask the question but do not think that the permissions will be changed. The problem seems to be with the fields from the LEFT OUTER JOIN sub-queries but only when the UNION is added - am I missing something fundamental using UNION when sub queries have been used in the join – Caroline Allen Jul 13 '17 at 13:11
  • @CarolineAllen It runs in SQL. That's the thing. Creating the view and running in SSRS would at least rule this out but I'm confident it would sort your issue –  Jul 13 '17 at 13:18
  • When I try the Create View in SSRS I also get the following error: 'The CREATE VIEW SQL construct or statement is not supported' – Caroline Allen Jul 13 '17 at 13:19
  • @CarolineAllen I'm not saying create in SSRS. Create the view in SQL first. Then pickup the view in SSRS (or use 'select * from YourData') –  Jul 13 '17 at 13:20
  • Sorry @J_F we seem to be talking at cross purposes. My terminology is not a good as it could be. I can write the code and run in Sql (my code example) but if I add in the CREATE VIEW part it will not work in SQL query pane and therefore cannot be called in SSRS. – Caroline Allen Jul 13 '17 at 13:26
0

Thank you all for your responses. The response from @tomdemaine made me think that maybe I could get rid of the second select clause and use an OR instead of the UNION and for some reason that has worked and the query now runs in SSRS as well as in the SQL query pane and although it is a very long WHERE clause it runs in 13 seconds which is acceptable.

Caroline Allen
  • 183
  • 14