I am doing something very similar to what you want , but its in SQLServer.
Do one thing, post the entire sql that you are using for the query of the database, also post exactly what you are passing in the PARMDATE parameter. I might be able to build a query for you.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Ok i think its the param file thats the culprit. The -param operand doesnt like spaces in between. We had the same problem. You have to pass it has a string without spaces.
Like this = 20060401001236
In the sql query, build the where clause as
select ID,Create_Date,Desc_cfp
from table
where Create_Date > cast(substring(cast(#PARMDATE# as varchar(17)),1,8)+' '+substring(cast(#PARMDATE# as varchar(17)),9,2)+':'+
substring(cast(#PARMDATE# as varchar(17)),11,2)+':'+substring(cast(#PARMDATE# as varchar(17)),13,2) as datetime)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
First of all i want you to perform a couple of tests for me.
Put the sql query that i sent above in your user defined sql
Then pass the value 20060401001236 for PARMDATE parameter. Hit view data. Does that work?
IF that works then we have the sql query, we just have to figure out a way to deal with the unix script.
How are you populating the parm file with the date. Is it some DS job or..?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
This may be way off...
Can you verify the EXACT string that is being passed to the SQL. I'm guessing that when your UNIX parmfile parse functionality does the command substitution from the parmfile to the dsjob command line, it stops at the first space. Consequently, you are passing
dsjob -run ... -param PARMDATE 2006-04-01
which you then quote in the SQL. I am unfamiliar with Sybase so all of the above pre-supposes that '2006-04-01' is not a correct format? Is it?
If that is the case then I would try double quoting the date in the parmfile.
Trobinson, that is exactly what i suspect. But the bad news is that the way dsjob command takes in parameters is that there has to be no space, no quotes (single or double).
Even we were stuck in this situation once, so we had to strip off everything from the date except the numbers.
I am guess thats exactly whats happeneing in this case.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
I stand Corrected. Its not the dsjob. Its the platform. I am on Sun, and it doesnt like quotes. And hence my assumption.
If the OP is on SUN then he will have to strip out the space and other characters, if he is on AIX then he should be fine.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
I did some more tests. if you just pass the command of dsjob and specify the params in that same line as
dsjob -run -param paramname=paramvalue
then the quotes work just fine. Even in my case it works.
But when i tried to provide the params to the dsjob command from a parameter file, it spits an error back at me and hence my work around.
I believe the OP is providing the dynamic timestamp from a parameter file to the dsjob command.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
For me it is not accepting the #PARMDATE# in the SQL when i am passing this parameter from the file whereas when i am passing this parameter in the job and running it seperately it is working fine.I believe it is not being able to get the correct format from the file.
It will not. If you are passing the date as 2006-04-01 00:12:36 it will not because of the space in between.
Try this test.
Pass 20060401001236 from the param file.
In your stage, use the generated sql query
select ID,Create_Date,Desc_cfp
from table
where Create_Date > cast(substring(cast(#PARMDATE# as varchar(17)),1,8)+' '+substring(cast(#PARMDATE# as varchar(17)),9,2)+':'+
substring(cast(#PARMDATE# as varchar(17)),11,2)+':'+substring(cast(#PARMDATE# as varchar(17)),13,2) as datetime)
and tell me if it works or not?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.