Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Strange legacy sql returning unpredictable results

790398Aug 9 2010 — edited Aug 9 2010
Hello,
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 6 2010
Added on Aug 9 2010
1 comment
588 views