0

LAG() function hold the previous value so I want to flush it only ? Is there any query/command to flush log/cache of LAG() function in oracle ?

I don' t want to flush all logs from oracle. I just want to flush stored previous value in Lag() function.

Actual Problem:

Oracle get row where column value changed

I am facing same problem as above and i used this query to getting changed value

select *
from t2 
where date1 = ( 
    select max(date1)
    from (
        select
            id, 
            date1, 
            cctr, 
            lag(cctr) over(order by date1 desc) as prev 
        from t2
    ) x
    where 
        prev is not null and
        cctr <> prev
    );

but in my case i have status column which i update with 'Y' and 'N' so when i use above query and I change the status 'Y' with 'N', it shows the changed row but when i change again status from 'N' to 'Y' then it does not show the changed row. I am using lag() function.

I am thinking if there is any command to flush/clear lag() function cache? because it check previous value. or there is any solution?

James Z
  • 12,209
  • 10
  • 24
  • 44
Aadi
  • 1
  • 1
  • 1
    The question doesn't appear to make sense. `lag()` lets you get data from the prior row of the current result set. It doesn't store any values, there is no cache, there are no logs to flush. Do you have an actual problem with how `lag` is functioning in a particular query? If so, please edit your question to show us a test case. – Justin Cave Oct 08 '19 at 17:24
  • 1
    Please post a reproducible test case (i.e. show us the table structure, the sample data, expected results, etc.) – Justin Cave Oct 08 '19 at 17:42
  • Is there any solution to detect changed row in oracle ? Without using trigger. – Aadi Oct 08 '19 at 18:09
  • Is this related to your question (where it looks like you're asking about querying changes between two rows rather than a trigger which would fire when a particular row was changed)? What problems does a trigger pose that you are trying to avoid? Depending on the answer, there are likely solutions (GoldenGate, LogMiner, flashback query, etc.) but those are likely to be rather more complicated than a trigger. – Justin Cave Oct 08 '19 at 18:13

0 Answers0