On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, June 30, 2008

Displaying Changes in Audit Data


The subquery pulls the value in each column for that row.

With the 'Lead' function, the value from the next row is used to create a field in the current row.

The parent query gets that data and compares the current with the next, and sets 'Chg' to '*' if they're different.
Column Item_Id     Format 99     Heading Item|Id
Column Nxt_Item_Id Format 99 Heading Nxt|Item|Id
Column Lot_Id Format 999999 Heading Lot|Id
Column Nxt_Lot_Id Format 999999 Heading Nxt|Lot|Id
Column User_Nm Format A4 Heading User|Nm
Column Nxt_User_Nm Format A4 Heading Nxt|User|Nm
Column Chg Format A1 Heading C|h|g


Select Item_Id,
Nxt_Item_Id,
------------------------------------------
Lot_Id,
Case When Lot_Id <> Nxt_Lot_Id
Then '*'
Else ' '
End As
Chg,
Nxt_Lot_Id,
------------------------------------------
User_Nm,
Case When User_Nm <> Nxt_User_Nm
Then '*'
Else ' '
End As
Chg,
Nxt_User_Nm
From
(
Select Item_Id,
Lead(Item_Id) Over (Order By Item_Id)
As
Nxt_Item_Id,
----------------------------------------------
Lot_Id,
Lead(Lot_Id) Over (Order By Item_Id)
As
Nxt_Lot_Id,
-----------------------------------------------
User_Nm,
Lead(User_Nm) Over (Order By Item_Id)
As
Nxt_User_Nm
-----------------------------------------------
From AuditTbl
)
Where Nxt_Item_Id is not null;

See Oracle documentation for further detail:
Lead

No comments:

Post a Comment