DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 178

Points: 2104

Post Posted: Thu Sep 06, 2018 3:55 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: Error message: ORA-01858:
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42791
Location: Denver, CO
Points: 220555

Post Posted: Thu Sep 06, 2018 7:01 am Reply with quote    Back to top    

How is your target column DATEDIFF defined in the job?

_________________
-craig

"I don't mind you comin' here and wastin' all my time time"
Rate this response:  
Not yet rated
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 178

Points: 2104

Post Posted: Thu Sep 06, 2018 7:03 am Reply with quote    Back to top    

Hi,

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






Thanks.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42791
Location: Denver, CO
Points: 220555

Post Posted: Thu Sep 06, 2018 7:13 am Reply with quote    Back to top    

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

_________________
-craig

"I don't mind you comin' here and wastin' all my time time"
Rate this response:  
Not yet rated
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 178

Points: 2104

Post Posted: Thu Sep 06, 2018 8:36 am Reply with quote    Back to top    

Hi,

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






Thanks.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42791
Location: Denver, CO
Points: 220555

Post Posted: Thu Sep 06, 2018 9:00 am Reply with quote    Back to top    

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

"I don't mind you comin' here and wastin' all my time time"
Rate this response:  
Not yet rated
rrcr
Participant



Joined: 06 Jul 2017
Posts: 4

Points: 34

Post Posted: Mon Sep 24, 2018 11:14 pm Reply with quote    Back to top    

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).
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours