Incorrect syntax near the keyword 'WITH'

[Jholman]

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.

[GuilhermeMelo]

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

[Jholman]

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

[GuilhermeMelo]

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.

[Jholman]

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