Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
@Anonymous I assuming this CTE SQL statement you can run successfully in SSMS, correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes that's right @parry2k , the SQL statement can be run correctly and it doesn't return any errors in SSMS.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k ,
Thank you for your suggestion! Now it shows the following error message:
" Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'. "
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |