Re: How to fix cache buffer chain issue

Thanks, Mladen and Andy, for prompting me to look at the original SQL.

Without spending more than a couple of minutes on the code I get the impression that it's trying to
1) get all the data for a given date DT_CT, then 2) summarise it into non-overlapping ranges of adt.

The code seems very repetitive with the main variation being:

            AND (TRUNC ( :B2) - ADT) <= 1

            AND (TRUNC ( :B2) - ADT) > 1
            AND (TRUNC ( :B2) - ADT) <= 2

            AND (TRUNC ( :B2) - ADT) > 2
            AND (TRUNC ( :B2) - ADT) <= 3

            AND (TRUNC ( :B2) - ADT) > 3
            AND (TRUNC ( :B2) - ADT) <= 4

            AND (TRUNC ( :B2) - ADT) > 4
            AND (TRUNC ( :B2) - ADT) <= 9

            AND (TRUNC ( :B2) - ADT) > 9
            AND (TRUNC ( :B2) - ADT) <= 14

            AND (TRUNC ( :B2) - ADT) > 60
            AND (TRUNC ( :B2) - ADT) <= 90

            AND (TRUNC ( :B2) - ADT) > 90

A "global" improvement might be to extract all the data for the date and then use something like a case expression to sum the data by the required data ranges.

Regards
Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l