Strange legacy sql returning unpredictable results
790398Aug 9 2010 — edited Aug 9 2010Hello,
I just got a new position and am returning to Oracle after working with other rdbms for a few years. I recently came across a report that had some sql i didnt understand and was 1. taking a long time to run and 2. appeared to capture only a subset of the possible matching records.
This is the basic form of the query and my fix for it. I've tried to make it generic but if i have eliminated too many details lease let me know.
Original form
SELECT createdate, some other stuff, trunc(SYSDATE) pdate
FROM (
SELECT createdate, some other stuff
FROM some table
)
JOIN
(SELECT trunc(to_date(:process_dt, 'mm/dd/yyyy')) pdate FROM dual) ON
TRUNC(createdate) >=
CASE WHEN TRIM(TO_CHAR(pdate, 'day')) != 'monday' THEN pdate - 1
ELSE pdate - 3
END
It appeared to me that the join was created solely to allow the user to use different process_dt's and to return 3 days of data if process_dt were a monday.
My fix
SELECT createdate, some other stuff,
trunc(SYSDATE) pdate
FROM (
SELECT createdate, some other stuff
FROM some table
)
WHERE TRUNC(createdate) >= CASE WHEN TRIM(TO_CHAR(trunc(to_date(:process_dt, 'mm/dd/yyyy')), 'day')) != 'monday' THEN trunc(to_date(:process_dt, 'mm/dd/yyyy')) - 1 ELSE trunc(to_date(:process_dt, 'mm/dd/yyyy')) - 3 END
ORDER BY 1, 2
Any ideas on why these queries would return different records or why the original query would overlook potential matches would be greatly appreciated. Also, if there is a clearer or more efficiant way to express this I would love to see it.
Thanks very much in advance.