Re: Can this be done using analytics?

  • From: Gints Plivna <gints.plivna@xxxxxxxxx>
  • To: eugene.pipko@xxxxxxxxxxxx
  • Date: Mon, 4 Apr 2011 23:05:57 +0300

It is not clear what the necessary output should be.
BUT you can filter out only those rows with at least one changed value since
previous view something like this (NOT TESTED!!!):

SELECT po, po_line, audit_timestamp, suppl, prev_suppl, price, prev_price,
completed, prev_completed
FROM (
  SELECT po, po_line, audit_timestamp,
    suppl, lag(suppl) OVER (PARTITION BY po, po_line ORDER BY audit_seq)
prev_suppl,
    price, lag(price) OVER (PARTITION BY po, po_line ORDER BY audit_seq)
prev_price,
    completed, lag(completed) OVER (PARTITION BY po, po_line ORDER BY
audit_seq) prev_completed
  FROM audit_table
  WHERE <some_conditions>
)
WHERE lnnvl(suppl = prev_suppl)
   or lnnvl(price = prev_price)
   or lnnvl(completed = prev_completed)

And then calculate some flags to indicate what value had actually been
changed.

Gints Plivna
http://www.gplivna.eu


2011/4/4 Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>

>  Hi all,
>
> Oracle 9.2.0.8 on Windows 2K3.
>
> I have a vendor’s audit table which gets populated every time a user opens
> a PO form, for instance.
>
> Many times there is no change reflected in the audit, however, when user
> does make a change to the PO, the new values are written into the audit.
>
> My question is: how can I build a view of what was changed on what day
> using Oracle’s analytics. I know I can use lag(), but I will need to compare
> all rows, not just prior to current.
>
> Can this be done in sql?
>
>
>
> Here is the snippet of the table:
>
>
>
>
>
> Thanks,
>
> Eugene
>
>
>

Other related posts: