Error message when loading an SQL query with CTE

[Anonymous]

Hi all,

I have prepared an SQL query with two Common Table Expressions in it and I need to load it into PowerBI Desktop. When I try to do it the system gives me the following error message:

"Microsoft SQL: Incorrect syntax neat the word 'WITH'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semincolon. Incorrect syntax near ',', incorrect syntax near ')'."

I don't have any queries before the CTE, my query starts by creating the CTE. I have added semicolons to all the imported queries in other tables and also tried to add a separate SELECT statement when I import the CTE, however if I do that, PowerBI only imports the data from the first SELECT statement and not the rest of the query.

How can I overcome this error message?

This is my SQL query, in case it might be useful:

WITH List_NOTNULL_systemid AS (

SELECT h.email as Email1, h.[Employee First Name], h.[Employee Last Name], a.fullusername, r.SystemId, h.[Op Company Desc] FROM Table1 as h

               LEFT JOIN Table2 as a

                              ON h.Email = a.mail

               LEFT JOIN Table2 as r

                              ON a.fullusername = r.fullusername

                                             WHERE h.email IS NOT NULL

                                             AND r.systemid IS NOT NULL),

List_NULL_systemid AS (

SELECT h.email as Email2, h.[Employee First Name], h.[Employee Last Name], a.fullusername, r.SystemId, h.[Op Company Desc] FROM Table1 as h

               LEFT JOIN Table2 as a

                              ON h.Email = a.mail

               LEFT JOIN Table3 as r

                              ON a.fullusername = r.fullusername

                                             WHERE h.email IS NOT NULL

                                             AND r.systemid IS NULL)

SELECT * FROM List_NULL_systemid

               WHERE Email2 NOT IN (SELECT email1 FROM List_NOTNULL_systemid)

Please help!

Thank you!!

[parry2k]

@Anonymous I assuming this CTE SQL statement you can run successfully in SSMS, correct?

[Anonymous]

Yes that's right @parry2k , the SQL statement can be run correctly and it doesn't return any errors in SSMS.

[parry2k]

@Anonymous maybe add ; before WITH and then try. 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k ,

Thank you for your suggestion! Now it shows the following error message:

" Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'. "