Skip to Content
author's profile photo Former Member
Former Member

IDM: "not a valid month" error

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2011 at 02:26 PM

    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'

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2011 at 08:33 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2011 at 08:39 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.