Page 1 of 2
passing date parameter
Posted: Fri Apr 13, 2007 10:13 am
by Krazykoolrohit
I tried the search but couldnt find anything to help me.
I am passing the following SQL in the DB2 API. i need to extract data for the last week
Code: Select all
Z_PLCMT between 'DATE('#Week_Start_Date#')' || '-00.00.00.000000' and 'DATE('#Week_End_Date#')' || '-00.00.00.000000'
the values for Week_Start_Date being passed = 14347 and for Week_End_Date = 14341
the error message: SQL0104N An unexpected token "14347" was found following "". Expected tokens may include: "+ - AND". SQLSTATE=42601
Can you please point out where i am doing anything wrong in the syntax?
Posted: Fri Apr 13, 2007 10:18 am
by Krazykoolrohit
the following worked
Code: Select all
Z_PLCMT between char(DATE(#Week_Start_Date#)) || '-00.00.00.000000' and char(DATE(#Week_End_Date#)) || '-00.00.00.000000'
but have another query now.
DATE(14347) is not giving the output as '04-06-2007' instead its just passing 14347.
Posted: Fri Apr 13, 2007 1:51 pm
by us1aslam1us
Is it the Julian Date? I don't see any date function which will convert an Integer representation to date format using DATE function.I am not sure but might be you can do something like this:
Posted: Fri Apr 13, 2007 3:43 pm
by ray.wurlod
14347 could well be a DataStage internal format (12 Apr 2007). Try applying an appropriate Oconv() function to get it into the format required in the SQL.
Posted: Mon Apr 16, 2007 7:53 am
by Krazykoolrohit
ray,
it doesnt accept oconv in SQLs. i have worked it out and made them as a parameter to the jobs. now i need to capture current date and date of a week before in the job parameters.
Posted: Mon Apr 16, 2007 8:24 am
by us1aslam1us
Unless we know what that number exactly signify it will be like hitting in the arrow in the dark.
Posted: Mon Apr 16, 2007 1:32 pm
by Krazykoolrohit
ok. lets start from begining. how do i capture yesterdays date and pass into a job using job parameters?
Posted: Mon Apr 16, 2007 1:34 pm
by us1aslam1us
currdate()-1 or sysdate()-1 just without even using parameters if you are extracting the data on daily basis.
Posted: Mon Apr 16, 2007 2:24 pm
by Krazykoolrohit
now i need to get this sysdate to the following format before passing into the SQL.
Z_PLCMT between '2007-04-06-00.00.00.000000' and '2007-04-12-00.00.00.000000'
so i need systime.or equivalent.
Posted: Mon Apr 16, 2007 2:29 pm
by Krazykoolrohit
us1aslam1us wrote:currdate()-1 or sysdate()-1 just without even using parameters if you are extracting the data on daily basis.
the SQL is aborting when i give the following SQL. i am extracting data from DB2. wud i need to format the sysdate output to DB2 format.
am using this as a filter in the SQL.
Code: Select all
between sysdate()-1 and sysdate()-5
Posted: Mon Apr 16, 2007 3:09 pm
by ray.wurlod
The word you were looking for is "would".
Since sysdate - 5 is before sysdate - 1 try reversing the order in the BETWEEN constraint.
Also I'm not sure that sysdate is a function. But I'm not in a position to check right now. If it's not, you need to lose the parentheses from it - that is, use SYSDATE rather than SYSDATE().
Posted: Mon Apr 16, 2007 3:20 pm
by ds_developer
I avoid using sysdate in a source query since I am likely to have to reload data for a specific date in the past. I use a job control job to calculate the date then pass it to the real job using DSSetParam. The job control job has a parameter which is normally left blank. This causes the job control code to calculate yesterday's date. When this parameter contains a date (like 20070411) that date is used.
Just an idea.
John
Posted: Mon Apr 16, 2007 4:41 pm
by us1aslam1us
For DB2, It should be
orelse you can use
Also make about valid date range, typically it
is from 0001-01-01 to 12-31-9999
Posted: Mon Apr 16, 2007 5:16 pm
by DSguru2B
Pay attention to ds_develper's suggestion. A very valid point.
Posted: Tue Apr 17, 2007 2:53 pm
by Krazykoolrohit
ds_developer wrote:I avoid using sysdate in a source query since I am likely to have to reload data for a specific date in the past. I use a job control job to calculate the date then pass it to the real job using DSSetParam. The job control job has a parameter which is normally left blank. This causes the job control code to calculate yesterday's date. When this parameter contains a date (like 20070411) that date is used.
Just an idea.
John
Joihn, can you please elaborate? i dont know what the job control parameter should be.