HANA Advanced SQL statement being changed to NativeQuery. Unable to edit SQL directly

[Syndicate_Admin]

Hi All,

Half of our team are now on the September 2022 patch and the others are on August 2022.  Those of us on Sep 2022 can no longer directly edit Advanced SQL.  We are pushed to the advanced editor, which is not ideal.

Before patch: in applied steps the cog icon is avaiable

= SapHana.Database("pcci-serverurl.us:30015", [Query=

Any new queries since the patch.  applied steps the cog icon is no longer there.  The source is change to look like that below.

= Value.NativeQuery(SapHana.Database("pcci-serverurl.us:30015", [Implementation="2.0"])

Anyone know how we can stop this?  Or at least have the ability to edit the SQL query without all the formating that the advanced editor does.

For some reason all my seraches on this trend towards folding.  At this point we do all the optimisation directly in the SQL.  Moving these to power query logic is not someting I'd want to do right now.

Thanks 

Russell

[Syndicate_Admin]

Hi @russellya ,

You could edit the code in Advanced Editor.

Query overview in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

You could, but it's hideous. It goes from being able to simply adjust the SQL, to having to try and amend your SQL in Syntax like: 

with FISCAL as#(lf)#(lf)(#(lf)#(lf)select 'FISCAL' as VERSION,#(lf) dt.CALENDAR_YEAR as YEAR,#(lf) dt.CALENDAR_MONTH as MONTH,#(lf) RPRCTR as PROFIT_CENTRE,#(lf)

Which isn't particularly user friendly, especially given my 200 line code is now in 1 line in Power Query...

Keeping an eye on this thread in case someone else has another option.

[Syndicate_Admin]

Hi Russel,

I just managed to work around the problem by going to the advanced editor and reverting it back to the old format at the front and end of the query, so removed the Value.NativeQuery( from the start and , null, [EnableFolding=true]) from the end, and replaced with something like the below:

From

To

This then made the cog re-appear and enabled more simplistic editing of the SQL query.

[Syndicate_Admin]

Thank you TheCreepster1 this work around does the trick.  Of course as soon as I save it reverts back but at least I can update code without having to navigate the advanced editor.   Still looking for a way to prevent power BI from converting my queries to nativequery.  

I'm escalating this issue to Microsft as it also seems to be impacting published reports but that's a different topic.  I can't believe this is how microsoft want this to function.  

Thanks again

Russell

[Syndicate_Admin]

I''ve done this before but my team are still learning SQL.  This would breask them.  Hoping microsoft will admit they messed up and fix their patch.  

[Syndicate_Admin]

This is now a ticket with Microsoft.  Verifired that switching back to August Patch fixes the issue.  Support persons stated that it might be fixed in the October 2022 patch.  I'll post if this is the case or not.

[Syndicate_Admin]

Same here. This has basically haunted my work in two different reports. It's terrible.

[Syndicate_Admin]

I'm running into the same issue. What used to be an easy sql change to grab different columns, or filter data, has become a nightmare. 

why would they change this? 

[Syndicate_Admin]

For some reason this option still does not work for me. It says it's returning values greater than expected 😞