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 have several dataflows that are refreshed).

But when I'm using native query, I cannot save my dataflow. I have this error:

 

 

Activity ID: 8f0df613-49aa-4e5b-950f-14dc863bac92
Request ID: 19039a32-0188-df35-a78a-a2c880ec3bd4
Correlation ID: a09cceb9-0024-12c3-c376-5bc09d590cec
Status code: 500
Time: Fri Mar 25 2022 10:22:53 GMT+0100 (Central European Standard Time)
Service version: 13.0.17904.42
Client version: 2203.2.10453-train
Cluster URI: https://wabi-france-central-a-primary-redirect.analysis.windows.net/

 

 

 

Here is my query:

getDateKey

 

 

let
    Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)
in
    Source

 

 

 

Basic query

 

 

let
    startDate = getDateKey(RangeStart),
    endDate = getDateKey(RangeEnd),
    query = "select test_date, load_date from csn where to_number(to_char(load_date,'yyyymmdd')) > " & Text.From(startDate) & " and to_number(to_char(load_date,'yyyymmdd')) <= " & Text.From(endDate),
    Source = Oracle.Database("my gateway information", [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"

 

 

 

In the editor, I manage to have a preview of my query:

 

 

I'm using a gateway to connect to my DB.

I have a pro license, but I'm not administrator of the BI server. So I don't have access to any log.

 

Thanks in advance for your help.

 

 

 

Dataflow

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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)

    • Erebe's avatar
      Erebe
      New Member
      4 years ago

      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.

       

      • 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.