Incorrect syntax near the keyword 'WITH'

Looker Q&A Looker Studio
,
1

Really odd. Custom query works fine in Azure Studio. Looker allows it to load in the resources no problem. The moment I try to make a table using it I get the above error. Can’t figure out what the issue is. Looking forward to assist, thank you.

2

Hello Jholman, what would be the query? Attach the question to facilitate support.

3

WITH

CTEu AS (SELECT
u.id,
u.cst_id AS ‘Prospect’,
CASE
WHEN u.brn_id IN (‘IN’,‘CHI’,‘CHIW’,‘CHIN’,‘MIL’,‘STL’) THEN ‘West’
WHEN u.brn_id IN (‘CLE’,‘COL’,‘LSV’,‘PIT’,‘LEX’) THEN ‘Central’
WHEN u.brn_id IN (‘BAL’,‘NC’,‘NOVA’,‘PHL’) THEN ‘East’
ELSE ‘OOA’
END AS ‘Region’,
CASE
WHEN u.brn_id IN (‘CHIW’,‘CHIN’) THEN ‘CHI’
WHEN u.brn_id NOT IN (‘BAL’,‘CHI’,‘CHIW’,‘CHIN’,‘CLE’,‘COL’,‘IN’,‘LSV’,‘MIL’,‘NC’,‘NOVA’,‘PHL’,‘PIT’,‘STL’,‘LEX’) THEN ‘OOA’
ELSE u.brn_id
END AS ‘Branch’,
COALESCE(src_SourceCodes.SourceGroup, ‘Unknown’) AS ‘Source’,
CONCAT_WS(', ‘,promoter.lastname,promoter.firstname) AS ‘Promoter’,
CONCAT_WS(’, ‘,setter.lastname,setter.firstname) AS ‘Setter’,
CONCAT_WS(’, ',confirmer.lastname,confirmer.firstname) AS ‘Confirmer’,
u.ApptDate AS ‘ApptDate’,
u.dsp_id,
CASE
WHEN (u.dsp_id IN (‘Resch’,‘NP1Leg’,‘NPPrice’,‘NPProduct’,‘NPSvc’,‘NPTime’) AND u.apptdate IS NOT NULL) THEN 1
WHEN u.eversat > 0 THEN 1 ELSE 0
END AS ‘Demo’,
ROW_NUMBER() OVER (PARTITION BY u.id, u.cst_id ORDER BY CASE WHEN u.dsp_id IN (‘Resch’,‘NP1Leg’,‘NPPrice’,‘NPProduct’,‘NPSvc’,‘NPTime’)
AND u.apptdate IS NOT NULL THEN 1 ELSE 2 END, u.ApptDate) AS RowNumber
FROM (SELECT
pa67db.dbo.lds_leads.id,
pa67db.dbo.lds_leads.brn_id,
pa67db.dbo.lds_leads.src_id,
pa67db.dbo.lds_leads.cst_id,
pa67db.dbo.lds_leads.pro_id,
pa67db.dbo.lds_leads.eversat,
pa67db.dbo.app_Appointments.setby,
pa67db.dbo.app_Appointments.confirmedby,
CAST(pa67db.dbo.app_Appointments.apptdate AS DATE) AS ‘apptdate’,
pa67db.dbo.app_Appointments.sat,
pa67db.dbo.app_Appointments.dsp_id
FROM pa67db.dbo.Lds_Leads
LEFT JOIN pa67db.dbo.app_Appointments ON pa67db.dbo.app_Appointments.Lds_ID = pa67db.dbo.lds_leads.id) u
LEFT JOIN pa67db.dbo.src_SourceCodes ON pa67db.dbo.src_SourceCodes.SourceCode = u.src_id
LEFT JOIN pa67db.dbo.emp_employees AS promoter ON promoter.id = u.pro_id
LEFT JOIN pa67db.dbo.emp_employees AS setter ON setter.id = u.setby
LEFT JOIN pa67db.dbo.emp_employees AS confirmer ON confirmer.id = u.confirmedby
WHERE CAST(u.apptdate AS DATE) >= ‘2022-01-01’),

CTEt AS (SELECT
t.id,
t.cst_id AS ‘Prospect’,
CASE
WHEN t.brn_id IN (‘IN’,‘CHI’,‘CHIW’,‘CHIN’,‘MIL’,‘STL’) THEN ‘West’
WHEN t.brn_id IN (‘CLE’,‘COL’,‘LSV’,‘PIT’,‘LEX’) THEN ‘Central’
WHEN t.brn_id IN (‘BAL’,‘NC’,‘NOVA’,‘PHL’) THEN ‘East’
ELSE ‘OOA’
END AS ‘Region’,
CASE
WHEN t.brn_id IN (‘CHIW’,‘CHIN’) THEN ‘CHI’
WHEN t.brn_id NOT IN (‘BAL’,‘CHI’,‘CHIW’,‘CHIN’,‘CLE’,‘COL’,‘IN’,‘LSV’,‘MIL’,‘NC’,‘NOVA’,‘PHL’,‘PIT’,‘STL’,‘LEX’) THEN ‘OOA’
ELSE t.brn_id
END AS ‘Branch’,
COALESCE(src_SourceCodes.SourceGroup, ‘Unknown’) AS ‘Source’,
CONCAT_WS(', ‘,promoter.lastname,promoter.firstname) AS ‘Promoter’,
CONCAT_WS(’, ‘,setter.lastname,setter.firstname) AS ‘Setter’,
CONCAT_WS(’, ',confirmer.lastname,confirmer.firstname) AS ‘Confirmer’,
t.ApptDate AS ‘ApptDate’,
t.Dsp_ID,
CASE
WHEN (t.dsp_id IN (‘Resch’,‘NP1Leg’,‘NPPrice’,‘NPProduct’,‘NPSvc’,‘NPTime’) AND t.apptdate IS NOT NULL) THEN 1
WHEN t.eversat > 0 THEN 1 ELSE 0
END AS ‘Demo’,
ROW_NUMBER() OVER (PARTITION BY t.id, t.cst_id ORDER BY CASE WHEN t.dsp_id IN (‘Resch’,‘NP1Leg’,‘NPPrice’,‘NPProduct’,‘NPSvc’,‘NPTime’)
AND t.apptdate IS NOT NULL THEN 1 ELSE 2 END, t.ApptDate) AS RowNumber
FROM (SELECT
fi74db.dbo.lds_leads.id,
fi74db.dbo.lds_leads.brn_id,
fi74db.dbo.lds_leads.src_id,
fi74db.dbo.lds_leads.cst_id,
fi74db.dbo.lds_leads.pro_id,
fi74db.dbo.lds_leads.eversat,
fi74db.dbo.app_Appointments.setby,
fi74db.dbo.app_Appointments.confirmedby,
CAST(fi74db.dbo.app_Appointments.apptdate AS DATE) AS ‘apptdate’,
fi74db.dbo.app_Appointments.sat,
fi74db.dbo.app_Appointments.dsp_id
FROM fi74db.dbo.Lds_Leads
LEFT JOIN fi74db.dbo.app_Appointments ON fi74db.dbo.app_Appointments.Lds_ID = fi74db.dbo.lds_leads.id) t
LEFT JOIN fi74db.dbo.src_SourceCodes ON fi74db.dbo.src_SourceCodes.SourceCode = t.src_id
LEFT JOIN fi74db.dbo.emp_employees AS promoter ON promoter.id = t.pro_id
LEFT JOIN fi74db.dbo.emp_employees AS setter ON setter.id = t.setby
LEFT JOIN fi74db.dbo.emp_employees AS confirmer ON confirmer.id = t.confirmedby
WHERE CAST(t.apptdate AS DATE) >= ‘2022-01-01’)

SELECT
‘UWD’ AS ‘Brand’,
Prospect,
Region,
Branch,
Source,
Promoter,
Setter,
Confirmer,
ApptDate AS ‘ProcessDate’,
0 AS ‘Raw’,
0 AS ‘EverSet’,
CASE WHEN RowNumber = 1 THEN Demo ELSE 0 END AS ‘Demo’
FROM CTEu

UNION ALL

SELECT
‘TBA’ AS ‘Brand’,
Prospect,
Region,
Branch,
Source,
Promoter,
Setter,
Confirmer,
ApptDate AS ‘ProcessDate’,
0 AS ‘Raw’,
0 AS ‘EverSet’,
CASE WHEN RowNumber = 1 THEN Demo ELSE 0 END AS ‘Demo’
FROM CTEt

4

One of the main errors is the naming of aliases, in looker studio it is not necessary to perform the naming with u.cst_id AS “Prospect” using “”, indicating the Alias ​​with u.cst_id AS Prospect it can already be translated.

5

Ok I removed the single quotes from aliases. Did not change the error.