FueledbyAccess
New member
- Local time
- Today, 15: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];
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];