Forum Discussion
Using query hint in Value.NativeQuery SQL statement
Hi,
Is it somehow possible to add a SQL query hint like "OPTION (FORCE ORDER)" into an sql statement were i'm activating QueryFolding=true?
Example:
"SQL STATEMENT...OPTION (FORCE ORDER)", null,[EnableFolding=true])
When I try it will throw this error:
DataSource.Error: We cannot fold on top of this native query. Please modify the native query or remove the 'EnableFolding' option.
1 Reply
Probably not, because the SQL you enter in Value.NativeQuery gets used in a subquery, similar to what happens in DirectQuery mode as I described here: https://blog.crossjoin.co.uk/2022/03/06/why-not-all-sql-queries-can-be-used-as-a-source-in-power-bi-directquery-mode/), and the OPTION clause can be used inside a subquery (assuming you're writing TSQL: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#remarks).
