I’m getting an error :
Syntax error: Expected end of input but got "." at [1:84]
But the same query in BQ with parameters runs fine.
Any ideas what I could look for?
SQL:
/* with pub id from python code - aniview */
WITH
dates as (
SELECT
day
FROM
UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d',@DS_START_DATE),PARSE_DATE('%Y%m%d',@DS_END_DATE))) AS day
)
,
alldates as (
SELECT
dates.day date,
l.publisher_id publisher_id,
l.publisher_site_id publisher_site_id
FROM
dates
INNER JOIN
\`dev-era-184513.mappings.siteid_to_pubid_latest\` l
ON TRUE
INNER JOIN \(PII Removed by Staff)\` mae ON l.publisher_id = mae.publisher_id
WHERE
lower(mae.emails) = LOWER(@DS_USER_EMAIL)
OR @DS_USER_EMAIL LIKE '%@massarius.com'
OR @DS_USER_EMAIL = '(PII Removed by Staff)@gmail.com'
GROUP BY
1,2,3
)
SELECT
alldates.date,
alldates.publisher_id,
alldates.publisher_site_id,
dnm.publisher_name,
dnm.publisher_site,
dnm.etl_pi,
dnm.etl_revenue,
dnm.etl_traded_revenue,
dnm.etl_net_revenue,
da_wpi.ga4_pv,
da_wpi.ga4_pi,
da_wpi.ga4_event_page_view,
da_wpi.ga4_event_screen_view,
da_cus.ms_pageview,
da_cus.ms_script_initialized
FROM
alldates
LEFT JOIN
(SELECT
cast(date as date) date,
publisher_id publisher_id,
publisher_site_id publisher_site_id,
publisher_name publisher_name,
publisher_site publisher_site,
sum(paid_impressions) etl_pi,
sum(revenue) etl_revenue,
sum(traded_revenue) etl_traded_revenue,
sum(net_revenue) etl_net_revenue,
FROM
\`dev-era-184513.Analysis.data_network_massarius_table\`
WHERE
date>=PARSE_DATE('%Y%m%d',@DS_START_DATE)
AND date< PARSE_DATE('%Y%m%d',@DS_END_DATE)
GROUP BY
1,2,3,4,5
) dnm
ON
alldates.date = dnm.date
AND alldates.publisher_id = dnm.publisher_id
AND alldates.publisher_site_id = dnm.publisher_site_id
LEFT JOIN
(SELECT
cast(date as date) date,
publisher_id,
publisher_site_id,
sum(page_views) ga4_pv,
sum(paid_impressions) ga4_pi,
sum(event_page_view) ga4_event_page_view,
sum(event_screen_view) ga4_event_screen_view,
FROM
\`dev-era-184513.massarius_data.data_analytics_with_pub_ids\`
WHERE
date>=PARSE_DATE('%Y%m%d',@DS_START_DATE)
AND date< PARSE_DATE('%Y%m%d',@DS_END_DATE)
GROUP BY
1,2,3
) da_wpi
ON
alldates.date = da_wpi.date
AND alldates.publisher_id = da_wpi.publisher_id
AND alldates.publisher_site_id = da_wpi.publisher_site_id
LEFT JOIN
(SELECT
cast(date as date) date,
publisher_site_id publisher_site_id,
sum(ms_pageview) ms_pageview,
sum(ms_script_initialized) ms_script_initialized
FROM
\`dev-era-184513.massarius_data.data_analytics_custom_events_mstag\`
WHERE
cast(date as date)>=PARSE_DATE('%Y%m%d',@DS_START_DATE)
AND cast(date as date)< PARSE_DATE('%Y%m%d',@DS_END_DATE)
GROUP BY
1,2
) da_cus
ON
alldates.date = da_cus.date
and alldates.publisher_site_id = da_cus.publisher_site_id
WHERE
dnm.publisher_name IS NOT NULL