Assign SYSDATE to Job Parameter
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 15
- Joined: Mon Jan 31, 2005 6:19 pm
Assign SYSDATE to Job Parameter
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
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
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:
Then, use the job parameters in a similar manner to the first suggestion:
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.
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)
Code: Select all
SELECT X FROM Y WHERE Z BETWEEN #STARTDATE# AND #ENDDATE#
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 15
- Joined: Mon Jan 31, 2005 6:19 pm
To Craig ...
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 ?...
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 ?...
Re: To Craig ...
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.kripakarprasad wrote:It gives out an error at the parameter declaration stage itself.
One way to automate this would be by playing the old export-edit-import game.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 15
- Joined: Mon Jan 31, 2005 6:19 pm
Got another solution...
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.ray.wurlod wrote: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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers