Assign SYSDATE to Job Parameter

[kripakarprasad]

Hi ,

I have to run an incremental load every day for the transactions for the previous day. STARTDATE and ENDDATE are two parameters, and basically, STARTDATE should be SYSDATE -1 and ENDDATE should be SYSDATE.

Right now, everyday the two values are being fed in manually. How can i automate this process..

Thanx

[vans2003]

You can Use the following in transform derivation...


Sysdate:
Oconv(@DATE, "D-YMD[4,2,2]")

Sysdate-1
Oconv(@DATE-1, "D-YMD[4,2,2]")

--vans

[chulett]

Welcome!

One way would be to not pass them in as parameters, but simply use SYSDATE - 1 and SYSDATE in your source query in their place. Problem is you have no way to 'override' those values when the job is run out of sequence.

You could also setup two String parameters with the values you've listed:

Code: Select all

STARTDATE    TRUNC(SYSDATE-1)
ENDDATE      TRUNC(SYSDATE)

Then, use the job parameters in a similar manner to the first suggestion:

Code: Select all

SELECT X FROM Y WHERE Z BETWEEN #STARTDATE# AND #ENDDATE#

If you needed to override them, you could pass in a specfic date wrapped in an appropriate TO_DATE function to take their place. The TRUNCate makes sure you don't include the time portion of the sysdate in your query.

Or write some custom job control, determine the current date and pass them in as parameters via the DSSetParam function. I do that at times and make sure the sysdate is only used if the desired job control dates are left blank - if filled in, the assumption is they are override dates.

[kripakarprasad]

STARTDATE TRUNC(SYSDATE-1)
ENDDATE TRUNC(SYSDATE)

does not work..

It gives out an error at the parameter declaration stage itself. I am using the same job parameters in many places in a number of jobs, so its highly important to automate it.

To reiterate : the requirement is , having a job parameter which automatically gets assigned the system date's value. how to do this ?...

[chulett]

It gives out an error at the parameter declaration stage itself.

Then you probably still have them set to a Date type. This solution would require you to change them to a String as I noted.

One way to automate this would be by playing the old export-edit-import game.

[kripakarprasad]

This is what i did...

I had a job called "datetest", in which i used a parameter called "todayparam". I opened another job and in the job control, i added "datetest", and the line which says

ErrCode = DSSetParam(hJob1, "todayparam", 2005-03-21)

i replaced the 2005-03-20 ( the default value which i specified ) with Oconv(@DATE, "D-YMD[4,2,2]")

It works.

[chulett]

Yup, simplest thing is to have your own job control code where you can do pretty much anything you need to. That was the third option I listed.

Glad you found a solution that works for you.

[ray.wurlod]

ErrCode = DSSetParam(hJob1, "todayparam", 2005-03-21)

This is an interesting (?) function call. The value with which the parameter would be set is 1981, which is the result of evaluating the arithmetic expression 2005 - 3 - 21.

Whether the job would accept this would depend to some extent on the parameter type, but it serves as an example of what can go wrong, perhaps without even being noticed, if adequate care is not taken.

Might even make an interesting certification question! Or even a series of them.

[chulett]

Whether the job would accept this would depend to some extent on the parameter type, but it serves as an example of what can go wrong, perhaps without even being noticed, if adequate care is not taken.

Or it might be yet another example of a poster that typed in some of their code by hand rather than doing a cut-and-paste... and didn't quite get it right.