GET ONLY NEW DATA

[Anonymous]

HI, I WANT TO GET ONLY NEW DATA SINCE LAST ETL JOB PROCESS...I READ ABOUT STORING A GLOBAL VARIABLE WITH MAX UPDATE DATE I STORE IN TARGET DATABASE.

I HAVE 4 toracleinputs  THAT I MUST  FILTER WITH UPDATE_DATE>GLOBAL VARIABLE STORED...THE QUESTION IS...HOW AND WHERE I MUST STORE THAT VARIABLE..I SHOULD DO ONE FOR EACH BASE TABLE...BECAUSE I USE A TMAP JOINING THIS TABLES AND GET ONLY ONE OUTPUT TO ONE TABLE IS A KIND OF VIEW SND I HSVE TO STORE THE MAX DATE OF EACH INPUTDATA.....THANKS FOR YOUR HELP !!!

[abhi90]

@rhall can surely help u out

[Anonymous]

Thanks in new with this how can i ask him?

[Anonymous]

You need to rethink this. I assume "update_date" is a date recorded in your database tables. If that is the case, you need to keep a record of which date you used for each time your jobs runs. The best way to do this is to keep this information in your database or in another source (maybe a flat file). Every time you run your job, you look at the last update_date you ran for and use whatever logic applies to pick a new date to run for. You then run for that date and add that date to your logging table. 

[Anonymous]

I really appreciatte your response. I did what you have told to me ..but now i have problems with date format....i mean... i have stored in bd 01/01/1998 10:00:00 am in a DATE field format....i get this data by using next query (into tOracleInput_)


select to_date(LAST_UPDATE_DATE, 'dd/mm/yyyy hh:mi:ss') from ODS_JOB_RUNS where view_name='ODS_OA_COMMERCIAL_OFFICE'"


then i store it in global variable : LAST_RUN_DATE ((Date)globalMap.get("tOracleInput_4_QUERY"))



AND USE THIS VARIABLE IN ANOTHER tOracleInput


SELECT
GCCB_OFFICE.CREATE_DATE,
GCCB_OFFICE.UPDATE_DATE,
GCCB_OFFICE.UPDATE_USER,
GCCB_OFFICE.UPDATE_PROGRAM,
GCCB_OFFICE.OPTIMIST_LOCK,
GCCB_OFFICE.ID_OFFICE,

FROM GCCB_OFFICE
WHERE IND_COMMERCIAL=1 AND IND_ACTIVE=1 AND


UPDATE_DATE >TO_DATE ('" + (String)globalMap. get("LAST_RUN_DATE") + "', 'DD/MM/YYYY HH:MI S')"



the fact is that this doesn0t bring me any data...but there are lots of rows that should appears.. using debug i see the variable LAST_RUN_DATE as Mon Jan 01 00:00:00 COT 98 I SUPPOUSE THAT THIS IS THE PROBLEM...BUT.. how i should convert that date ????


thanks !!!

[Anonymous]

It's very difficult to identify what is happening here, but I suspect it is down to the casting of the date. It looks like your DB logging table is holding the date as a String. Is this the case? If so, fair enough.....but I would store it as a Date. If you are storing it as a String, you don't need to convert it to a Date for your job. Why not just use the String value and forget about the casting?

But, as I said, I wouldn't store it as a String and I would use a Date type. Then (if your log table is in the same database as your source data) you could just the log table in your query.