Re: Can this be done using analytics?

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Mon, 4 Apr 2011 23:05:57 +0300
Message-ID: <BANLkTimuzedSyEawNvHG1UySS8jSUC6Vsg_at_mail.gmail.com>

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_at_unionbay.com>

> 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
>
>
>

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

Received on Mon Apr 04 2011 - 15:05:57 CDT