Need some help with a workflow

[sk1909]

I've been running this workflow a really long time and I've run into a roadblock where it stops pulling data after a certain date.  I've changed the parameters to reduce the file size only.  I need to understand what the sqls are telling me.  My understanding is that what it should be doing is looking at the newest date in the database and creating a single database that contains only this "Max Last Update Date" then based on that date, it pulls all data after that Max Last Update Date and overwrites that database with the new one.  Something has gone wrong and it stopped pulling anything after 9/24.  Other parts of the consecutive workflows pull the correct data, just not this piece.  See attached workflow.

I can't share the yxdb because it contains proprietary data.

Thanks,

Sheila

[BrandonB]

Hi @sk1909 

I would recommend filling in a "LAST_UPDATE_DATE" placeholder and seeing what the resulting SQL is generated as shown below. This might help give you some perspective as to what isn't processing correctly. 

 

If I put in 2020-10-18 as my LAST_UPDATE_DATE, these are the resulting queries:

SQL1
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-10-17' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-10-18 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL2
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-10-18 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-10-19 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL3
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-10-19 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-10-20 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL4
SELECT to_date('0001-01-01 00:00:01' ,'YYYY-MM-DD HH24:MI:SS') CREATION_DATE,1 SOURCE_SYSTEM,1 CPE_PROCESS_FLAG FROM DUAL

SQL5
SELECT to_date('0001-01-01 00:00:01' ,'YYYY-MM-DD HH24:MI:SS') CREATION_DATE,1 SOURCE_SYSTEM,1 CPE_PROCESS_FLAG FROM DUAL

SQL6
SELECT to_date('0001-01-01 00:00:01' ,'YYYY-MM-DD HH24:MI:SS') CREATION_DATE,1 SOURCE_SYSTEM,1 CPE_PROCESS_FLAG FROM DUAL

[sk1909]

Hi @BrandonB ! Thank you for the suggestion.  It took me a while to realize what you were saying here.  I have a detour in place attempting to use this logic to get the data I need.  I'll keep you posted!

[BrandonB]

This is what I see happening:

Cust_OO_Line_Combined.yxdb has some data in it. From this data, you are deriving the Max_LAST_UPDATE_DATE which is the latest date in the file.

If this date is 2020-09-27, it is then fed into the formula tool that calculates the following:

DateTimeAdd([Max_LAST_UPDATE_DATE],-1,"hours")

This is subtracting one hour from the last update date

Connection column is created and "RRProcessNew" is used -> This is your database connection

SQL1 through SQL 6 are 6 separate formulas that appear to be pulling different ranges based on where the last update date falls with regard to the current date. Here are the queries that show up when 2020-09-27 is the last update date:

SQL1
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-09-26' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-09-27 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL2
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-09-27 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-09-28 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL3
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-09-28 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-09-29 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL4
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-09-29 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-09-30 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL5
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-09-30 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-10-01 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

SQL6
SELECT * FROM apps.cust_oo_lines_stage
WHERE LAST_UPDATE_DATE > to_date('2020-10-01 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')
AND LAST_UPDATE_DATE <= to_date('2020-10-02 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')

These queries seem to want to pull data from 9/27 through the next 6 days. You also have a filter that says:

ToDate([CREATION_DATE]) >= "2018-08-01"
AND
[SOURCE_SYSTEM] IN ('DTV-OMS','LS-OMS')
AND
[CPE_PROCESS_FLAG] = 'P'

So it is possible that data you expect to be passing through is getting filtered out here, but I can't be sure without knowing what data you are working with. 

Finally, your data outputs to Cust_OO_Line_Combined_Temp1.yxdb. Does this file not have your latest outputs from the queries?