Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Quires regarding SQL Stored Procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Quires regarding SQL Stored Procedure
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    Post subject: @Ram
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Is possible to call subprogram from c... cmsmoon COBOL Programming 3 Fri Aug 05, 2016 6:46 pm
This topic is locked: you cannot edit posts or make replies. Stored procedure on Federated thru DB2 arif677 DB2 6 Tue May 31, 2016 11:48 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us