Forum Discussion
DataFlow - Natique Oracle query using Incremental refresh - Error 500
You may be overcomplicating things. Use Date.ToText() directly with RangeStart and RangeEnd, directly in your native query. (Shame that dataflows insist on messing with the query step order, but whatever)
NOTE: You have a logical conflict between your native query ( greater than start, less or equal to end) and the incremental refresh (greater or equal to RangeStart, less than RangeEnd)
Hello. Thank you for your answer.
I find it difficult the date/datetime insertion in native query.
The simple Date.ToText() doesn't work for the rangeStart/rangeEnd parameters:
We cannot convert the value #datetime(1, 1, 1, 0, 0, 0) to type Date.
Indeed I have a logical conflict. Fixed it, but I still have the issue.
Use DateTime.ToText instead.
BUT. Do not use a date like 1,1,1 - use only dates that appear in your data, or something close. Using "moon dates" is bad practice.
Hello,
Thank you for your answer.
After some modifications, I am able to use the DateTime conversion:
let query = "select * from csn where load_date >= to_date('" & DateTime.ToText(RangeStart) & "', 'MM/DD/YYYY HH:MI:SS AM') and load_date < to_date('" & DateTime.ToText(RangeEnd) & "', 'MM/DD/YYYY HH:MI:SS AM')", Source = Oracle.Database("xxxxxxxxxxxxxxxx", [Query = query, HierarchicalNavigation = true]), #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh" = Table.SelectRows(Source, each DateTime.From([LOAD_DATE]) >= RangeStart and DateTime.From([LOAD_DATE]) < RangeEnd) in #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh"But I'm still facing the same issue when I validate the dataflow:
The dataflow takes some time before raising the error. Could it be due to a timeout or something like this?
