This content has been marked as final. Show 8 replies
I would go for a query like this on your work repository (in the refresh tab of an ODI variable) :
I do not have an ODI install in front of me, so I'm not about the OK. Just check the current values of some past executions ;)
SELECT max(SESS_END) FROM SNP_SESSION WHERE SESS_NAME = 'package_name' AND SESS_STATUS = 'OK';
You can do it from 2 ways
you set variables in your package in order to store in a custom parameter table the date of last beginning and last ending
from the repository
you retrieve the value in the work repository : in the SNP_SCEN_REPORT table. You have the columns SESS_BEG and SESS_END in date format.
With SCEN_NO, do an inner join with SNP_SCEN to get the name of the scenario.
Juste retrieve the last row which SCEN_NAME = your scenario.
I created a variable that called #GET_SESSION
SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL
and then I created second variable valled GET_SESSION2 :
SELECT TO_DATE(SUBSTR(MAX(SESS_BEG),1,10),'DD-MM-YYYY') FROM ODIW_rep.SNP_SESSION WHERE SESS_NO=#GET_SESSION
My session information is here :
195001 DENEME_1234 5 2778252 1355211056548 11/12/2012 09:30:56 11/12/2012 09:30:57
beg time is :11/12/2012 09:30:56
But when I execute package now (11/12/2012 09:38:56) max(sess_beg) will be last executed time but it will be sysdate.I want to get 11/12/2012 09:30:56
How can ı get ?
This is normal.
You have created a variable that retrieves the ACTUAL session begin date.
So, when you're launching your package at 11/12/2012 09:38:56, it will retrieve the begin date of this session, so 11/12/2012 09:38:56.
If you want the previous session, you cannot use a filter like SESS_NO = <%=odiRef.getSession("SESS_NO")%>
==> You must filter on the previous session of the same scenario. Or the previous session of the same session name.
Use WHERE clause to filter on finished / not running session
But don't use <%=odiRef.getSession("SESS_NO")%>
Other solution : use the SESS_END in order to retrieve the END date. Since your actuel scenario is not finish, you will get the last session.
If you really want the BEGIN date of the last session, you must adapt your SQL Statement in order to get the session previously executed.
Hi Drieux ,
I want to get last execution lend time.
Now my filter should be like this..SELECT * FROM SOURCE_TABLE S WHERE S.TRANS_DATE BETWEEN last_execution_time and SYSDATE.For example my previous last_time is 11/12/2012 09:30:56
sysdate is 11/12/2012 09:30:56
I will insert just these datas.Is there any solution to insert datas from previous last execution to current?
Last execution time is :previous end_time .Now What should I do?
I refer you to our previous posts
1) Either you store the execution date in a custom parameter table (if you want to use this solution, I will give you more details).
2) OR you retrieve the date in the SNP_SESSION table, or the SNP_SCEN_REPORT table with the SQL statement we give you, Jerome and me.
Moreover, your previous execution should NOT be the last END execution time, but the last BEGINNING execution time. Otherwise, you won't extract the data that have been created in source table between the first extract and the end of your last execution.
3) You can also use CDC to journalize new/modified rows in source and select only them.