Page 1 of 1
ORA-01843: not a valid month
Posted: Thu Feb 13, 2020 7:24 am
by abhilashnair
I have a strange issue wherein a query runs fine in TOAD, but not on Datastage Oracle connector, the job fails with error
The OCI function OCIStmtFetch2 returned status -1. Error code: 1843, Error message: ORA-01843: not a valid month. (CC_OraStatement::fetch, file CC_OraStatement.cpp, line 1,729)
The query is
select
col1,col2..coln
from table 1
where
col1<=(select max(col1) from table 2)
here col1 of table1 and col1 of table2 have same data type- DATE in Oracle
Posted: Thu Feb 13, 2020 8:55 am
by chulett
Not strange. Toad just runs the sql and shows you the result, DataStage has to do more than that. It needs to put the results into variables (much like a "select into" in PL/SQL) and that is what is causing your issue. The question is, what are the data types of the columns in the job? An implicit conversion going on under the covers is failing.
Posted: Thu Feb 13, 2020 9:16 am
by abhilashnair
Weirdly though, the same job works without GREATER THAN EQUAL TO operator within the where condition.
For example when I run the query with below where condition job goes fine
where
col1=(select max(col1) from table 2)
Posted: Thu Feb 13, 2020 4:47 pm
by chulett
Probably just a difference in the data selected between the two filters. Still would like to see my question answered.
Posted: Thu Feb 13, 2020 5:10 pm
by abhilashnair
As I said, I did run the same job without the where condition and with same metadata. It ran successfully
Posted: Fri Feb 14, 2020 2:31 pm
by chulett
Right. Oddly enough, I actually read your post before I replied. And unfortunately, simply regurgitating information doesn't help us help you.
We would need more information from you in order to carry on this discussion...
Posted: Thu Feb 20, 2020 9:43 am
by abhilashnair
chulett wrote:Not strange. Toad just runs the sql and shows you the result, DataStage has to do more than that. It needs to put the results into variables (much like a "select into" in PL/SQL) and that is what is causing your issue. The question is, what are the data types of the columns in the job? An implicit conversion going on under the covers is failing.
The metadata was imported and loaded in the Oracle connector stage using IMAM, if that is what you were asking. On top of it, I have cros verified them with database.