Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a query that I added a CTE to and I get the following message: Incorrect syntax near ';' and incorrect syntax near ')'.
If I take the CTE out, it works fine. The query runs fine on the SQL console.
Any ideas?
Thank you!
Solved! Go to Solution.
Hi @Anonymous,
Based on my research, this should be a known issue. And I have reproduced the issue in DirectMode.
In addition, there is an idea about adding this function, you can vote it up.
Regards,
Frank
Hi Frank,
Thanks for the info. I voted the issue up. Hopefully this will be addressed. CTEs are essential for the queries we write.
Thank you!
Hi @Anonymous,
I cannot reproduce the issue in DirectMode when I tried to connect to SQL Server database. Here everything works well.
Could you please share your SQL code to me? Thanks in advance!
Regards,
Frank
Hi Frank,
Sure, here is my query. I don't really need the CTE in this instance but I would still like to know what the issue is on my end as I will be developing templates using direct query.
Thank you for your help!
;with orders as (
select
OrderDate.todate [OrderDate]
,oh.OCusNo [CusNo]
,oh.O_Billto [BillToCusNo]
--,oh.OCusNm [Name]
,case
when oh.oflag = 0 then 'Unselected'
when oh.oflag = 1 then 'Selected'
when oh.oflag = 2 then 'Intermediate'
when oh.oflag = 3 then 'Completed'
when oh.oflag = 4 then 'Aborted'
when oh.oflag = 5 then'Intermediate' else cast(oh.oflag as varchar) end [Order Status]
,oh.oordno [Order No.]
,oh.obakno [Order Seq.]
,ol.linenuma [Order Line]
,case when oh.oquote = 0 then 'No' else 'Yes' end [Quote]
,ol.lprcat [Prod. Cat.]
,ol.LITMNO_SUP [SUP]
,ol.LITMNO_ITEM [Part]
,ol.LITMNO_LOC [LOC]
--,ltrim(rtrim(ol.LDESCR1)) + ', ' + ltrim(rtrim(ol.ldescr2)) [Item Description]
,(ol.lprice * ol.lqtysh) * (1 - ol.ldisc) [ExtPrice]
,ol.lcost * ol.lqtysh [Ext. Cost]
,((ol.lprice * ol.lqtysh) * (1 - ol.ldisc)) - (ol.lcost * ol.lqtysh) [Margin]
,(((ol.lprice * ol.lqtysh) * (1 - ol.ldisc)) - (ol.lcost * ol.lqtysh)) / (case when ((ol.lprice * ol.lqtysh) * (1-ol.ldisc)) <> 0 then abs((ol.lprice * ol.lqtysh) * (1-ol.ldisc)) else 1 end ) [Margin %]
,oh.O_Username [Last_User]
,oh.O_Orig_User [Orig_User]
from dbo.ordlin ol (nolock)
join dbo.ordhdr oh (nolock) on ol.lordno = oh.oordno and ol.lseqno = oh.obakno
cross apply timsdata.dbo.tlgetdate(oh.OORDDT) [OrderDate]
where ol.lware = ''
and ol.lkit <> 2
)
select
*,
o.[Margin]/case when o.[ExtPrice] <> 0 then abs(o.[ExtPrice])/100 else 1 end [Margin%]
from orders o
Hello Again,
I am using this version of Power BI.
Version: 2.59.5135.601 64-bit (June, 2018)
Hi @Anonymous,
Based on my research, this should be a known issue. And I have reproduced the issue in DirectMode.
In addition, there is an idea about adding this function, you can vote it up.
Regards,
Frank
User | Count |
---|---|
90 | |
73 | |
68 | |
63 | |
56 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |