Forum Discussion
Native query on OLEDB source with parameteres doesn't work
I have a native query with a parameterised where caluse like this...
WHERE CALDATE > @datefrom AND CLDATE < @datetoI try to query it
let
Source = OleDb.DataSource("provider=IBMDASQL.DataSource.1;data source=serverconnection),
Database = Source{[Name="SERVER",Kind="Database"]}[Data],
calendar = Value.NativeQuery(Database,Query1, [datefrom="20230701",dateto="20240701"])
in
calendarThis results in a datasource error, Column or global variable @DATEFROM not found.
I guess the parameters are not being picked up by Value.NativeQuery.
If I remove the parameters from the where clause, or replace with literal values, it runs fine.
Is this not supported for OLEDB?
1 Reply
The issue you’re encountering might be due to the way OLE DB handles parameters in native queries. In some cases, OLE DB might not support parameterized queries in the same way as other data sources1.
Here are a few potential solutions:
Use Literal Values: If possible, consider replacing the parameters with literal values directly in the query. This might not be feasible if the parameters change frequently or are user inputs1.
Use Power Query Parameters: You can create parameters in Power Query and use them in your native query2. However, this might not work if you need to pass the parameters dynamically at runtime1.
Check Query Syntax: Ensure that the syntax of your query is correct and that the parameters are properly defined and used1.
Consider Other Data Sources: If OLE DB continues to cause issues, you might want to consider using a different data source that better supports parameterized queries1.
Remember, these are just a few potential solutions. The exact solution might vary depending on the specifics of your setup1.
