Oracle Error : not a valid month

[Christina Lim]

Hallo all,

oraS_STDALT_IDN,0: Oracle call failed: sqlcode = -1,843
Message: ORA-01843: not a valid month

oraS_STDALT_IDN,0: Array Fetch failed for: SELECT ZCOMPLDT,ZRCVDT,ZALSTAT,"DESCRP", ((to_date(ZCOMPLDT, 'YYYYMMDD') - to_date(ZRCVDT, 'YYYYMMDD')) + 1) as AMOUNT FROM S_STDALT_IDN WHERE
substr(ZCOMPLDT, 1, 4)||'0'||substr(ZCOMPLDT, 5, 2) = substr(200500831,1,7) AND ZALSTAT='F'
and period=200500831

In Oracle the data type for ZCOMPLDT is NUMBER and data type in DataStage is Integer. This job has been running in production every month. Until this month it aborted with the above error.

I tried copy the query in run in SQL Toad and the query works fine.

Any idea why this is happening.

[WoMaWil]

you get this error when the number is not a yyyymmdd date.

for example in the periode you have a 0 too much.

example:
select to_DAte(200500101,'YYYYMMDD') Datum from dual
ERROR at line 1:
ORA-01843: not a valid month
select to_DAte(20050101,'YYYYMMDD') Datum from dual
DATUM
--------
01.01.05

[msbahirat]

Hi,

You are going to execute the script for month end ? or based on date it will execute.

i.e. suppos today is 08 sept. as you mentioned that datatype is number so 0 before number will not considered.
--
select to_date('2092005','DDMMYYYY') from dual;
ERROR at line 1:
ORA-01843: not a valid month
--
select to_date('02092005','DDMMYYYY') from dual;

TO_DATE('
---------
02-SEP-05

Hallo all,

oraS_STDALT_IDN,0: Oracle call failed: sqlcode = -1,843
Message: ORA-01843: not a valid month

oraS_STDALT_IDN,0: Array Fetch failed for: SELECT ZCOMPLDT,ZRCVDT,ZALSTAT,"DESCRP", ((to_date(ZCOMPLDT, 'YYYYMMDD') - to_date(ZRCVDT, 'YYYYMMDD')) + 1) as AMOUNT FROM S_STDALT_IDN WHERE
substr(ZCOMPLDT, 1, 4)||'0'||substr(ZCOMPLDT, 5, 2) = substr(200500831,1,7) AND ZALSTAT='F'
and period=200500831

In Oracle the data type for ZCOMPLDT is NUMBER and data type in DataStage is Integer. This job has been running in production every month. Until this month it aborted with the above error.

I tried copy the query in run in SQL Toad and the query works fine.

Any idea why this is happening.