0

Incremental Load with Union Query

Hi,

I am trying to tackle delete scenario in source system with below logic in incremental code, its throwing error, any help is appreciated.

SELECT 'N' AS IS_DELETED,

                    ST.KEY_COLUMN

FROM SOURCE_TABLE ST

WHERE ST.UPDTE_DATE > ?-2

UNION ALL

SELECT 'Y' AS IS_DELETED,

                   INC.KEY_COLUMN

FROM INCORTA_TABLE INC

WHERE INC.KEY_COLUMN IN (

     SELECT KEY_COLUMN FROM INCORTA_TABLE WHERE UPDATE_DATE > ?-180

     MINUS

     SELECT KEY_COLUMN FROM SOURCE_TABLE WHERE UPDATE_DATE > ?-180 )

 

Can this type of SQL statement work in incremental logic?

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • The error message I am getting is as below

    INC_03070402: Failed retrieving sample data from [OTM_CERT] with properties [[incremental, true], [incrementalMethod, 0], [incrementalColumn, ], [timestampFieldType, DATE], [fetchsize, 5000], [batchMethod, 0], [batchSize, ], [batchColumn, ], [batchUpperBound, ], [batchLowerBound, ], [timeBatchColumn, ], [chunkPeriod, ], [customNumberOfDays, ], [timeBatchUpperBound, ], [timeBatchLowerBound, ]]

    Like
  • I guess that the question is whether we can use ? question mark multiple times within a query.  I think that it is supported.  For the syntax of "-180", it depends on the data source you are using.  The syntax needs to be a valid SQL.

    Like
    • Dylan Wan 

      Thanks Dylan for reply, here syntax is good, as per my understanding where it is failing is running this logic in Incremental Load and trying to access both source and Incorta table. I am not sure if that is supported?

      Also I am not able to figure out anything from the error message, could you please explain what is [incrementalMethod, 0] ?

      Like
  • I see.  How about using the approach as we layout in the post: https://community.incorta.com/t/x1rarf/how-does-incorta-handle-source-deletions-of-rows

    Basically, run this query as a full load:

    SELECT KEY_COLUMN FROM SOURCE_TABLE WHERE CREATION_DATE > ?-180 

    Join the incorta table with the above key only table.

    Filtering out all data that does not exist in the key table using a run time security filter.

     

    Otherwise, the above query you described can be done within a MV.  The only difference is that the source table is now extracted as another regular incorta table before they are used in this query.

    Like
    • Dylan Wan the solution described at the other post is definitely a possible option, I was trying to build a solution where I do not have to maintain two tables and hit source system twice. But looks like that is the only option at this time to get this requirement fulfilled.

      Like 1
Like Follow
  • 3 mths agoLast active
  • 5Replies
  • 30Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!