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
ORA-01843: not a valid month
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
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.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.