Forum Discussion
Power BI direct query
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!
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
5 Replies
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,
FrankHi 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