on 12-08-2011 2:53 PM
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
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.