Solved Left Join on Union Query not working (1 Viewer)

FueledbyAccess

New member
Local time
Today, 04:27
Joined
Sep 6, 2019
Messages
6
Hello,

Hoping some one can educate me on LEFT JOINS a bit. I'm trying to add a LEFT JOIN to a UNION JOIN but when I run the query I receive the following 'Syntax error (Missing Operator) in query expression ‘t0.Project = t3.[Project Code] Left JOIN [Ref_Project Cost Centres] AS t4 on t0.[Task Key’.’

The query works if I remove the LEFT JOIN and also works if I try LEFT JOIN [Ref_Projects & Hierarchy] AS t3 ON t0.Project = t3.[Project Code]; or LEFT JOIN [Ref_Project Cost Centres] AS t4 ON t0.[Task Key] = t4.[Key]; on their own but not together.


Below is the Full Query and attached are extracts of the table being used.

SELECT *

FROM (select iif(t2.Source = 'Committed', 'ForecastCommitted' , 'Actuals' ) as SourceType,

t2.Source, t2.[Nominal Code], '' AS [Date], t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], '' AS Required, t2.[Order Number], '' AS [Order date], t2.Creator, t2.[Line Number], (t2.Net) , (t2.Hours), t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, iif(t2.Source = 'Committed', 'Committed' , 'Actuals' ) as Quality

from Actuals_raw t2

where val(Period) <= (select Period from Last_actuals) and t2.Source = 'Actuals' and Cost_Type1 <> 'Revenue' and t2.Hours = 0

union all select

iif(t2.Source = 'Committed', 'ForecastCommitted' , 'Actuals' ) as SourceType, 'Forecast' as Source, t2.[Nominal Code], '' AS [Date], t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], t2.Required AS Required, t2.[Order Number], '' AS [Order date], t2.Creator, t2.[Line Number], (t2.Net) , (t2.Hours) , t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, iif(t2.Source = 'Committed', 'Committed' , 'Actuals' ) as Quality

from Actuals_raw t2

where t2.Source = 'Committed' and Cost_Type1 <> 'Revenue' and t2.Hours = 0

UNION ALL SELECT t2.Source as SourceType,

t2.Source, t2.[Nominal Code], '' AS [Date], t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], '' AS Required, t2.[Order Number], '' AS [Order date], t2.Creator, t2.[Line Number], Sum(t2.Net) AS SumOfNet, Sum(t2.Hours) AS SumOfHours, t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, t2.[Detail Line] AS Quality

FROM Actuals_raw AS t2

where val(Period) <= (select Period from Last_actuals) and Source = 'Actuals' and Cost_Type1 <> 'Revenue' and t2.Hours <>0

GROUP BY t2.Source, t2.[Nominal Code], '', t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], '', t2.[Order Number], '', t2.Creator, t2.[Line Number], t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, t2.[Detail Line], Val([Period]), t2.[Source], t2.[Cost_Type1]

UNION ALL SELECT

'ForecastPM' AS Sourcetype, 'Forecast' AS Source,

t1.[Nominal Code], '' AS [Date], t1.Month, t1.[Header Ref], t1.[Internal Ref], t1.Supplier, t1.Year, t1.Project, t1.Task, t1.[Task Key], t1.[Detail Line], '' AS Required, t1.[Order Number], '' AS [Order date], t1.Creator, t1.[Line Number], (t1.Net), (t1.Hours), t1.[Nominal Account Name], t1.Cost_Type1, t1.Cost_Type2, t1.Cost_Type3, t1.GroupCostCentre, t1.Period, t1.[Detail Line] AS Quality

FROM Forecast AS t1

where period > (select Period from Last_actuals)
and Cost_Type1 <> 'Revenue') AS t0

LEFT JOIN [Ref_Projects & Hierarchy] AS t3 ON t0.Project = t3.[Project Code]
LEFT JOIN [Ref_Project Cost Centres] AS t4 ON t0.[Task Key] = t4.[Key];
 

Attachments

  • Tables.zip
    18 KB · Views: 148

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:27
Joined
May 7, 2009
Messages
16,076
you are better off with Union query.
it's because you don't have Unique key to your project field.
 

plog

Banishment Pending
Local time
Yesterday, 22:27
Joined
May 11, 2011
Messages
10,864
Eat the elephant in bites not one big gulp. You essentially have a bunch of individual queries, get each to work by itself as you want, then UNION them. Don't cram a ton of logic and the UNION together, you'll never find out exactly where its going wrong and its a pain to debug.

So, make the first SELECT its own query (sub1). Then make the second SELECT its own query (sub2), etc, etc.. Debug each as needed. Then make a last query that just UNIONs them together:

SELECT * FROM sub1
UNION ALL
SELECT * FROM sub2
UNION ALL
SELECT * FROM sub3
....
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:27
Joined
Oct 29, 2018
Messages
18,742
Hi. Welcome to AWF!

Is the problem only about getting the syntax error? If so, I would also suggest starting with a simpler SQL statement first, so you can get the LEFT JOINs working. After that, you can start adding more fields and criteria.

Just a thought...
 

FueledbyAccess

New member
Local time
Today, 04:27
Joined
Sep 6, 2019
Messages
6
Hi all, thanks for your advice on this one. I went with plog's suggestion of making each select it's own query.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:27
Joined
Oct 29, 2018
Messages
18,742
Hi all, thanks for your advice on this one. I went with plog's suggestion of making each select it's own query.
Hi. Thanks for the update. Good luck with your project.
 

Users who are viewing this thread

Top Bottom