DSXchange

Hi All,

When i try to execute the below query in sql developer/Toad it's fecthing the required results.

DATE1 & DATE2 is date datatype in oracle

SELECT
nvl(abs(to_date(DATE2,'dd/mm/yyyy') - to_date(DATE1,'dd/mm/yyyy')),0) as DATEDIFF,
FROM TABLE;

But, when i try the same in Oracle Connector in Datastage it throws me the below error message.



Error code: 1858, Error message: ORA-01858: a non-numeric character was found where a numeric was expected.

Kindly let me know your thoughts on the same.

Thanks.

How is your target column DATEDIFF defined in the job?

Hi,

It has been defined as decimal(38,8) in Dataset.






Thanks.

Just in case it is different, I actually meant in the Connector... same?

Hi,

Thanks Craig. Now i have changed the datatype as varchar(96) and the issue gets resolved.






Thanks.

Sure, that's one way to "fix" the issue. All that shows is that you did indeed have "non-numeric" data in your output somewhere. Keep in mind all it takes is one record to cause that and it's not necessarily something you'll see in Toad. You'll see it there if the issue happens within a join but the output data is not typed in Toad whereas it is in DataStage and that's why you can see it in one tool when the problem is only in what you've selected but not the other.

satheesh_color wrote:Hi All,

When i try to execute the below query in sql developer/Toad it's fecthing the required results.

DATE1 & DATE2 is date datatype in oracle

SELECT
nvl(abs(to_date(DATE2,'dd/mm/yyyy') - to_date(DATE1,'dd/mm/yyyy')),0) as DATEDIFF,
FROM TABLE;

But, when i try the same in Oracle Connector in Datastage it throws me the below error message.



Error code: 1858, Error message: ORA-01858: a non-numeric character was found where a numeric was expected.

Kindly let me know your thoughts on the same.

Thanks.

When date1 and date2 are already date data types, i dont understand why are you trying to convert to date again?
You can simply write abs(date1-date2).