I keep getting the following error:
Error: Union results in ambiguous schema. [Bookings] is ambiguous and is aliasing multiple fields. Aliased fields: z.Bookings, Bookings
I have looked at the other threads here and made (what I thought) were the correct changes however no luck.
SELECT * FROM (SELECT
CASE WHEN depdate between DATE('1970-01-02') AND DATE('2013-10-31') THEN 'OLD'
WHEN depdate between DATE('2013-11-01') AND DATE('2014-04-30') THEN 'W13'
WHEN depdate between DATE('2014-05-01') AND DATE('2014-10-31') THEN 'S14'
WHEN depdate between DATE('2014-11-01') AND DATE('2015-04-30') THEN 'W14'
WHEN depdate between DATE('2015-05-01') AND DATE('2015-10-31') THEN 'S15'
WHEN depdate between DATE('2015-11-01') AND DATE('2016-04-30') THEN 'W15'
WHEN depdate between DATE('2016-05-01') AND DATE('2016-10-31') THEN 'S16'
WHEN depdate between DATE('2016-11-01') AND DATE('2017-04-31') THEN 'W16'
WHEN depdate between DATE('2017-05-01') AND DATE('2017-10-31') THEN 'S17'
WHEN depdate > DATE('2017-11-01') THEN 'OTHER'
WHEN depdate = DATE('1970-01-01') THEN 'ALL' ELSE 'N/A' END as Season,
c.GUID as MyCode,
c.MasterCode as MasterCode,
c.Name as PropertyName,
c.Destination as Gateway,
SUM(CASE WHEN a.Type = 'Quote' THEN 1 ELSE 0 END) as Visits,
SUM(CASE WHEN a.Type = 'Booking' THEN 1 ELSE 0 END) as Bookings,"CHARTER"as Journey
FROM
[dataset.GUID_Master_Mapping] c
LEFT OUTER JOIN
(SELECT
DATE(concat(left([date],4),'-',substring([date],5,2),'-',substring([date],7,2))) as VisitDay,
UPPER(substring(hits.page.pagePath, instr(lower(hits.page.pagePath),'accomguid')+10,8)) as MyCode,
CASE WHEN hits.page.pagePath like '%booking?%' THEN 'Booking' ELSE 'Quote' END as Type,
ifnull(DATE(concat(substring(hits.page.pagePath,
instr(lower(hits.page.pagePath),'deptdate')+15, 4),'-',
substring(hits.page.pagePath,
instr(lower(hits.page.pagePath),'deptdate')+12, 2),'-',
substring(hits.page.pagePath,
instr(lower(hits.page.pagePath),'deptdate')+9, 2))),DATE('1970-01-01')) as depdate,
concat(fullVisitorId, string(visitId)) as UniqueVisit
FROM TABLE_DATE_RANGE([data set], DATE_ADD(CURRENT_TIMESTAMP(),-14,'DAY'),CURRENT_TIMESTAMP())
WHERE hits.type = 'PAGE'
AND (hits.page.pagePath like 'web url/cust?%'
or hits.page.pagePath like 'web url/booking?%'
)
AND UPPER(substring(hits.page.pagePath, instr(lower(hits.page.pagePath),'accomguid')+10,1)) = 'H'
and trafficSource.medium <> 'newrelic'
GROUP EACH BY
VisitDay, MyCode, depdate, Type, UniqueVisit) a
ON c.GUID = a.MyCode
WHERE c.Brand <> 'Z'
AND c.Season=CASE WHEN depdate between DATE('1970-01-02') AND DATE('2013-10-31') THEN 'OLD'
WHEN depdate between DATE('2013-11-01') AND DATE('2014-04-30') THEN 'W13'
WHEN depdate between DATE('2014-05-01') AND DATE('2014-10-31') THEN 'S14'
WHEN depdate between DATE('2014-11-01') AND DATE('2015-04-30') THEN 'W14'
WHEN depdate between DATE('2015-05-01') AND DATE('2015-10-31') THEN 'S15'
WHEN depdate between DATE('2015-11-01') AND DATE('2016-04-30') THEN 'W15'
WHEN depdate between DATE('2016-05-01') AND DATE('2016-10-31') THEN 'S16'
WHEN depdate between DATE('2016-11-01') AND DATE('2017-04-31') THEN 'W16'
WHEN depdate between DATE('2017-05-01') AND DATE('2017-10-31') THEN 'S17'
WHEN depdate > DATE('2017-11-01') THEN 'OTHER'
WHEN depdate = DATE('1970-01-01') THEN 'ALL' ELSE 'N/A' END
GROUP EACH BY Season, MyCode, Gateway, PropertyName,MasterCode),
(SELECT Season,MyCode,MasterCode,PropertyName,d.Gateway as Gateway,Visits,Bookings,Journey
FROM
[data set.Resorts_Gateway_Mapping]d
RIGHT JOIN EACH
(SELECT
CASE WHEN depdate between DATE('1970-01-02') AND DATE('2013-10-31') THEN 'OLD'
WHEN depdate between DATE('2013-11-01') AND DATE('2014-04-30') THEN 'W13'
WHEN depdate between DATE('2014-05-01') AND DATE('2014-10-31') THEN 'S14'
WHEN depdate between DATE('2014-11-01') AND DATE('2015-04-30') THEN 'W14'
WHEN depdate between DATE('2015-05-01') AND DATE('2015-10-31') THEN 'S15'
WHEN depdate between DATE('2015-11-01') AND DATE('2016-04-30') THEN 'W15'
WHEN depdate between DATE('2016-05-01') AND DATE('2016-10-31') THEN 'S16'
WHEN depdate between DATE('2016-11-01') AND DATE('2017-04-31') THEN 'W16'
WHEN depdate between DATE('2017-05-01') AND DATE('2017-10-31') THEN 'S17'
WHEN depdate > DATE('2017-11-01') THEN 'OTHER'
WHEN depdate = DATE('1970-01-01') THEN 'ALL' ELSE 'N/A' END as Season,
c.GUID as MyCode,
c.MasterCode as MasterCode,
c.Name as PropertyName,
c.Destination as Gateway,
SUM(CASE WHEN e.Type = 'Quote' THEN 1 ELSE 0 END) as Visits,
SUM(CASE WHEN e.Type = 'Booking' THEN 1 ELSE 0 END) as Bookings, "LIPS"as Journey
FROM
[dataset.GUID_Master_Mapping] c
RIGHT JOIN EACH
(SELECT
DATE(concat(left([date],4),'-',substring([date],5,2),'-',substring([date],7,2))) as VisitDay,
UPPER(substring(hits.page.pagePath, instr(lower(hits.page.pagePath),'accomguid')+10,8)) as MyCode,
CASE WHEN hits.page.pagePath like '%booking?%' THEN 'Booking' ELSE 'Quote' END as Type,
ifnull(DATE(concat(substring(hits.page.pagePath,
instr(lower(hits.page.pagePath),'deptdate')+15, 4),'-',
substring(hits.page.pagePath,
instr(lower(hits.page.pagePath),'deptdate')+12, 2),'-',
substring(hits.page.pagePath,
instr(lower(hits.page.pagePath),'deptdate')+9, 2))),DATE('1970-01-01')) as depdate,
concat(fullVisitorId, string(visitId)) as UniqueVisit
FROM TABLE_DATE_RANGE([.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(),-14,'DAY'),CURRENT_TIMESTAMP())
WHERE hits.type = 'PAGE'
AND (hits.page.pagePath like 'web url/cust?%'
or hits.page.pagePath like 'web url/booking?%'
)
AND UPPER(substring(hits.page.pagePath, instr(lower(hits.page.pagePath),'accomguid')+10,1)) = 'H'
and trafficSource.medium <> 'newrelic'
GROUP EACH BY
VisitDay, MyCode, depdate, Type, UniqueVisit) e
ON c.GUID = e.MyCode
WHERE c.Brand = 'Z'
GROUP EACH BY Season, MyCode, Gateway, PropertyName,MasterCode)z
On z.Gateway=d.Resort)
I realize this is a lot to look at but any help is always appreciated.
Not that this directly solves the original problem, but consider giving standard SQL a shot in the future. It has fewer surprising edge cases and handles unions as you with expect with, well, standard SQL :)