FAQ
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:
[cid:image002.png_at_01CBF2C2.124DFFF0]

Thanks,
Eugene

Search Discussions

  • Gints Plivna at Apr 4, 2011 at 8:05 pm
    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
    )

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 4, '11 at 7:17p
activeApr 4, '11 at 8:05p
posts2
users2
websiteoracle.com

2 users in discussion

Eugene Pipko: 1 post Gints Plivna: 1 post

People

Translate

site design / logo © 2022 Grokbase