Power BI direct query

[Anonymous]

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!

[Anonymous]

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!

[v-frfei-msft]

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

[Anonymous]

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

[Anonymous]

Hello Again,

I am using this version of Power BI.

Version: 2.59.5135.601 64-bit (June, 2018)

[v-frfei-msft · Accepted 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