ORA-01843: not a valid month

abhilashnair · Post by **abhilashnair** » Thu Feb 13, 2020 7:24 am

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

chulett · Post by **chulett** » Thu Feb 13, 2020 8:55 am

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.

abhilashnair · Post by **abhilashnair** » Thu Feb 13, 2020 9:16 am

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)

chulett · Post by **chulett** » Thu Feb 13, 2020 4:47 pm

Probably just a difference in the data selected between the two filters. Still would like to see my question answered.

abhilashnair · Post by **abhilashnair** » Thu Feb 13, 2020 5:10 pm

As I said, I did run the same job without the where condition and with same metadata. It ran successfully

chulett · Post by **chulett** » Fri Feb 14, 2020 2:31 pm

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

abhilashnair · Post by **abhilashnair** » Thu Feb 20, 2020 9:43 am

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.