IDM: "not a valid month" error

[Former Member]

Hi,

I have a problem with an IDM job...

The SQL Query produces the error:
ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

The part of the statement producing the error is

select TO_DATE('08-12-2011', 'DD-MM-YYYY'), to_date(avalue||'','YYYYMMDD') 
from mxiv_sentries 
where attrname = 'RS_ABSENT_TO' 
AND NOT (AVALUE = '00000000' OR AVALUE = 'undefinedundefined')
and TO_DATE(avalue||'','YYYYMMDD') >= TO_DATE('08-12-2011', 'DD-MM-YYYY')

If I execute only the first part

select TO_DATE('08-12-2011', 'DD-MM-YYYY'), to_date(avalue||'','YYYYMMDD') 
from mxiv_sentries 
where attrname = 'RS_ABSENT_TO' 
AND NOT (AVALUE = '00000000' OR AVALUE = 'undefinedundefined')

the system produces no error

Next strange thing is that on our DEV system the query runs without error, only on production the error occurs.

Edited by: Habib Pleines on Dec 8, 2011 3:53 PM

[Former Member · Accepted solution]

Hi,

perhaps you have an idea how to solve the problem with my existing data...

the table mxiv_sentries contains rows with the attrname 'RS_ABSENT_TO'

This attribute can have the values

- date in format 'yyyymmdd'

- 'UNDEFINEDUNDEFINED'

- '00000000'

another attrname is 'RS_ABSENT' which either has the value 1 or doesn't exist.

next attrname is MX_VALIDFROM which consists of a date in format 'YYYY-MM-DD'

what I need is the mskey of the identities having

- 'RS_ABSENT' set

- 'MX_VALIDFROM' <= current date

- 'RS_ABSENT_TO' < current date and not '00000000' or 'undefinedundefined'

[Former Member · Accepted solution]

Hi Habib,

as I can see you have the avalue in the format YYYYMMDD so you can the date of today into that format and check with this values:

avalue >substr('%$ddm.-date%',7,4)||substr('%$ddm.-date%',4,2)||substr('%$ddm.-date%',0,2)

Then there is no conversion.

BR,

Christoph

[Former Member]

Hi,

I also first thought that it might be corrupt data, but why does the to_date function work properly in the Select "to_date" from part of the statement?

KR

Habib

[Former Member]

My guess would be that this is due to the order of validating the conditions done by the database.

If for example the last line is evaluated before the check for the attribute name, it will try to do a TO_DATE function on an aValue which is NOT a date, which will fail.

I would propose to store any dates in the identity store using the ISO8601 syntax. In that way you can use string compare to compare dates. You will also avoid having to use functions in WHERE clauses (which is STRONGLY discouraged).

Also, make sure you always use SearchValue in the WHERE clause.

Best regards

John Erik Setsaas

Development Architect IdM

[Former Member]

I would assume that the reason for this error is that there are values in RS_ABSENT_TO which cannot be properly converted using the TO_DATE function.

Also be aware that the performance of the statement will be very poor, and is not adviced. This is for two reasons:

• Using aValue in the WHERE clause. You should ALWAYS use SearchValue

• Using a function (TO_DATE) in the WHERE clause. This causes the database to do a table scan, as the index cannot be used.

Best regards

John Erik Setsaas

Development Architect SAP NW IdM