DSXchange

Hi,
I have a requirement in which I used a userdefined SQL in a Source DRS where I put
select START_DT,END_DT, LATE_START_DT from tablename.The fields have timestamp as the datatype.
I am using 2 databases(SQL Server and Oracle).The data in SQL server is in the format '1900-01-01 00:00:00'.The data in Oracle is of the format '04-Nov-03'.
The Job runs fine in SQL Server.But when I put it in Oracle, it is giving an error
"Attempt to convert String value '04-Nov-03' to Timestamp unsuccessful.
The query from the director is fetching the data perfectly.So this seems to be the problem with the fetching of data by the Source DRS in Ascential

If I modify the user defined Query as

select TO_CHAR(START_DT, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(END_DT, 'YYYY-MM-DD HH24:MI:SS')
from tablename ,
it will work fine in Oracle.But then it will fail in SQL Server since to_char is not a function in SQL Server.
Can anyone please suggest some solution to fix this issue in Oracle rather than using a To_Char function?

Thanks,
Andal

Why does this need to be 'user defined' sql? Let the stage generate it.

No actually some outer join is there in the end.I did not add the same to mke the query look less complex.

Maybe you can set your NLS_DATE_FORMAT in your 'before sql' tab in your input.

Makes it kind of hard to provide good advice when you leave out details like that, Andal. Don't worry, people here can read 'complex' sql. My only point was using user defined sql in a DRS stage removes any benefit of the 'D'ynamic part of it, unless you can stick with extremely generic sql.

At worst case, you may need two jobs.

Good luck!

ps - what is SQL Server going to do when it sees the NLS_DATE_FORMAT statement? Nothing good, I would guess.

The NLS_DATE_FORMAT was intended to solve the Oracle date problem.

I understand. But they intend to switch it back and forth between Oracle and SQL Server, so the solution needs to be applicable to both.

Then they can make it a parameter !!??

Hi Sai

where do you set this NLS_DATE_FORMAT in datastage NLS or in oracle?

Thank you very much,

Hi Andal,

Can you please send ur Datastage Job Design I am just curious how u used DRS stage in what scenario?

Thank you Very much,

Hi,
I got a resolution on how to use it.We can write the query like select %DateTimeOut(START_DT) from tablename.This will work both in SQL Server as well as in Oracle.
Craig, I did not mean that you are back in anything.Its only to reduce unnecessary complexity that I made the query like that. :D

Thanks,
Andal