Error message: ORA-01858: a non-numeric character was found

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Error message: ORA-01858: a non-numeric character was found

Post by satheesh_color »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

How is your target column DATEDIFF defined in the job?
-craig

"You can never have too many knives" -- Logan Nine Fingers
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

Hi,

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






Thanks.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

Hi,

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






Thanks.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
rrcr
Participant
Posts: 4
Joined: Thu Jul 06, 2017 12:00 am

Re: Error message: ORA-01858: a non-numeric character was fo

Post by rrcr »

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).
Post Reply