I have a problem with a (relativ) simple query which I do not understand, and I hope someone can help me on this.
Here we have the query:
SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
WHERE
trunc(SYSDATE)
BETWEEN
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND
TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')
The curious thing in this query is that it throw a ORA-01843 but only with the WHERE clause if I remove the WHERE clause no error is thrown.
So this works
SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
Since the WHERE part is using the exact same things as the SELECT part I am asking myself how this is possible?
Some background information:
I also checked stackoverflow and found some questions which go in the same direction but I did not find a question with a answer which works for me or explain the behaviour.
I think the reason for this behaviour can be the execution plan (or precidence of execution). So the two rows which can trigger the error are filterd after the WHERE which throws the ORA-01843 but before the SELECT part. can this be true and if so, do someone have a idea how I can change the query so this is working?
Thank you in advance!
The SQL Engine is opting to rewrite your query without the nested sub-queries so your first query is effectively:
SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM babw
WHERE ABWABTNR <> 'PASRZ'
AND trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')
and the BETWEEN
clause is being evaluated before the ABWABTNR
comparison.
You can try using hints to solve the issue. Either:
/*+ no_push_pred(a) */
in the outer query; or/*+ no_merge */
in the inner query.Or you can materialize the inner query using ROWNUM
:
SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (
SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ' AND ROWNUM > 0
) a
WHERE trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD');
Or you can use a CASE
expression:
SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM babw
WHERE CASE
WHEN ABWABTNR <> 'PASRZ'
AND trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')
THEN 1
END = 1;