Forum Discussion

Erebe's avatar
Erebe
New Member
4 years ago

DataFlow - Natique Oracle query using Incremental refresh - Error 500

Hello, I'm trying to create a new Dataflow referencing an Oracle database. I would like ot use a native query to perform some tasks on the source side.   I have no problem to connect to my DB (I ...
Dataflow
lbendlin's avatar
lbendlin
Super User
4 years ago

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.

Erebe's avatar
Erebe
New Member
4 years ago

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?

 

 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You are supposed to specify the format as part of DateTime.ToText(), not outside of it.  NOTE:  AM/PM is formatted as tt.