DSXchange

Posted: **Wed May 10, 2006 2:01 pm**

well try

cast(#PARMDATE# as datetime(19))

I dont know if Cast is supported by Sybase or not. If the above doesnt work then try the convert function

Code: Select all

CONVERT(datetime (19), #PARMDATE#)

Posted: **Wed May 10, 2006 2:08 pm**

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.

Posted: **Wed May 10, 2006 2:14 pm**

DSguru2B wrote:I am doing something very similar to what you want , but its in SQLServer.

Hi

I am having a .parm file in the following format

PARMDATE=2006-04-01 00:12:36

And I am trying to pull the data from one of the source table which is in sybase which is having three fields:

ID char
Create_Date timestamp
Desc_cfp char

I am using Sybase OCI stage and written the query like

select ID,Create_Date,Desc_cfp
from table
where Create_Date>'#PARMDATE#'

Thanks
Sam

Posted: **Wed May 10, 2006 2:23 pm**

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

Code: Select all

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)

Posted: **Wed May 10, 2006 5:35 pm**

DSguru2B wrote:Ok i think its the param file thats the culprit.
[/code]

I am sure that the format of parm file is the real culprit. Do you have any other idea like how to pass this dynamic parameter to the query.

Thanks
Sam

Posted: **Thu May 11, 2006 6:44 am**

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..?

Posted: **Thu May 11, 2006 7:23 am**

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.

Posted: **Thu May 11, 2006 7:36 am**

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.

Posted: **Thu May 11, 2006 9:46 am**

I don't think it is dsjob, rather the mechanism you are using to extract the parms from your parmfile. The following works just fine on AIX UNIX;

dsjob -run -param parm1="tim robinson" todd test

Posted: **Thu May 11, 2006 9:53 am**

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.

Posted: **Thu May 11, 2006 10:05 am**

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.

Posted: **Thu May 11, 2006 10:29 am**

Escape the quotes in the parmfile and I bet it works.
parm1=\"fdsafasfd sadfsfasfd\"

Posted: **Thu May 11, 2006 10:36 am**

Nop. It did not. I tried it. But its failing to grab it. But when i remove the space. It grabs it.

Posted: **Thu May 11, 2006 11:54 am**

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.

Thanks
Sam

Posted: **Thu May 11, 2006 12:10 pm**

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

Code: Select all

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?