Value.Native Query and CTE

[smpa01]

@bbrowne @ImkeF @AlexisOlson 

I am querying a table from sql server (lakehouse) and I am noticing that Value.NativeQuery generates and error

E.g.

//fn1
let
    sql = (server as text, db as text, query as text) as any => Sql.Database(server, db, [Query=query])
in
    sql

//fn2
let
    sql = (server as text, db as text, query as text) as any => 
        let
            source = Sql.Database(server, db),
            Q = Value.NativeQuery(source, query, null, [PreserveTypes = true, EnableFolding = true])
        in
            Q
in
    sql

If you invoke both with following 3rd parameter, the 2nd one errors out.

";with cte as (select 1 as colA) select * from cte"

But in reality , I want the 2nd one to work as power bi  does not let me apply incremental refresh on the query result from 1st function callback but IR is available to the result set of the 2nd callback.

How can I make the 2nd function to work with CTE? The workaround is 1) to use a table variable with  DECLARE but table variables are not avaialble in lakehouse sql or 2) convert ctes somehow to subquery and compromise performance and query readability.

Thank you in advance. CTEs are unavoidable in  this case.

[Element115]

I wholeheartedly agree.  I heavily rely on CTEs as well and am running into issues too when trying to get incrememental refresh to work at the PBI report level because the M queries do not fold.  What ensues is the creation of multiple reports, duplicate of each other, but each refreshing against a different calendar year.  Very suboptimal.

[AlexisOlson · Accepted solution]

I just read about this issue on Reddit the other day:

CTEs don't really work with query folding because they are in-memory tables and don't typically work when treated as a sub-query which folding attempts to do.

Need guidance on Value.NativeQuery() limitation when using SQL CTEs, and why these CTE expressions d...

It sounds like you might not be able to get both query folding and CTEs working at the same time.

[smpa01]

MS must do something about it by adding support for CTEs/temp table/table variable.

[Anonymous]

HI @smpa01,

In fact, this type of operations already been limited to use in fabric.

You can't directly create in-memory temporary tables at the query data operations. In my opinion, I'd like to suggest you split these to multiple steps and create fact data to instead temporary table.(you can remove them after processing)

In addition, you can also submit idea for these types of operations.

Microsoft Fabric Ideas

Regards,

Xiaoxin Sheng