IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Quires regarding SQL Stored Procedure


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rammanu

New User


Joined: 11 Oct 2007
Posts: 5
Location: Chennai

PostPosted: Wed Mar 10, 2010 1:49 pm
Reply with quote

I have one table called Employee and detail would like below

EMP_TABLE
S.No Emp_Id EmP_Name Emp_Salary
1 123 John 100
5 123 Sunil 100
7 123 Raj 500
10 123 Joseph 101

When ever i do the update on the columns like Emp_Name or Emp_Salary a new
record is inserted into the same table with new S.No
For Ex. Emp_Name "John" Changed to "Sunil" new record is inserted into the table with S.No"5".
Note:Emp_ID will remain same and it can't be changed/updated.

Now My requirement is "I should generate the report(using Cursors in SQL Stored procedure)
for audit log" for the "employee_Id 123" The report should be like below

Changed Field Old Value New value
Employee Name John Sunil
Employee Name Sunil Raj
Employee Nmae Raj Joseph
Employee Salary 100 500
Employee Salary 500 101

Please help how I can achieve this.
Back to top
View user's profile Send private message
manikawnth

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Wed Mar 10, 2010 3:15 pm
Reply with quote

I would suggest this:
DECLARE C CURSOR FOR
SELECT S.NO, EMP_ID, EMP_NAME, EMP_SALARY
FROM EMP_TABLE
GROUP BY EMP_ID
ORDER BY S.NO;

Fetch each row, store it in working storage, fetch next row.
Compare the EMP_IDs of current and previous. Both should be same. Identify the changed value prepare a report.
As simple as that.

I assume that for each update the insert is done according the increasing order of S.No.

Manikanth
P.S: Do I make any sense or Am I completely wrong?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
This topic is locked: you cannot edit posts or make replies. Internal Autonomous Stored Procedure ... DB2 6
No new posts Quires in GETADDRINFO All Other Mainframe Topics 0
No new posts Stored procedure cpu utilization DB2 1
No new posts In JCL Procedure how we can execute R... CLIST & REXX 7
No new posts Execute DSNTEP2 in REXX which is call... CLIST & REXX 4
Search our Forums:

Back to Top