Re: How to fix cache buffer chain issue

It's up to you to check the validaty of my guess regarding the nature of the query simply breaking a data set for a given date by different ranges then the basis of the mechanism is to take the predicate (list) that breaks the data set into pieces and combine the list in a CASE expression that generates a value that can be used as an extra grouping column, along the lines of:

select

        case
                when adt - to_date(:b1,'yyyy-mm-dd') <= 1
                      then 'g1'
                when adt - to_date(:b1,'yyyy-mm-dd')  > 1 and adt -
to_date(:b1,'yyyy-mm-dd') <= 2 then 'g2'
                when adt - to_date(:b1,'yyyy-mm-dd')  > 2 and adt -
to_date(:b1,'yyyy-mm-dd') <= 7 then 'g3'
                when adt - to_date(:b1,'yyyy-mm-dd')  > 7
                      then 'g4'
        end flag,
        mcd,
        sum(value_col), count(*)
from
        v1
group by
        case
                when adt - to_date(:b1,'yyyy-mm-dd') <= 1
                      then 'g1'
                when adt - to_date(:b1,'yyyy-mm-dd')  > 1 and adt -
to_date(:b1,'yyyy-mm-dd') <= 2 then 'g2'
                when adt - to_date(:b1,'yyyy-mm-dd')  > 2 and adt -
to_date(:b1,'yyyy-mm-dd') <= 7 then 'g3'
                when adt - to_date(:b1,'yyyy-mm-dd')  > 7
                      then 'g4'
        end,
        mcd
order by
        flag, mcd

Regards
Jonathan Lewis

On Tue, 22 Jun 2021 at 19:16, Pap <oracle.developer35_at_gmail.com> wrote:

> Thanks much Jonathan.
>
> Trying to visualize/understand the code modification which you suggested.
> Agreed that the difference in the query is just the date criteria filter on
> column ADT of table TAD. But currently we are fetching sum(amt) and
> count(*) based on the grouping column in each of those queries.
>
>
>

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