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