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
 

 

Need DB2 query to fetch previous row !

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Chandan1993

New User


Joined: 21 May 2017
Posts: 3
Location: India

PostPosted: Sat Jun 03, 2017 10:43 am    Post subject: Need DB2 query to fetch previous row !
Reply with quote

I have table when there is any changes in row it shows in column change_type_cd is 'U'.i need query like when someone updates the query then I can fetch the original record along with the updated record to compare their values.

Thanks in adv.
_________________
Thanks,
Chandan kumar
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Sat Jun 03, 2017 10:51 am    Post subject: Reply to: Need DB2 query to fetch previous row !
Reply with quote

do You realise that once a row has been updated the previous values are not there any longer
( unless the application is designed to keep the before <image> of the data )
Back to top
View user's profile Send private message
Chandan1993

New User


Joined: 21 May 2017
Posts: 3
Location: India

PostPosted: Sat Jun 03, 2017 11:10 am    Post subject:
Reply with quote

The both original record will be present in table after updating the rows and the change_type _cd will be as 'U' after updating the row.

i just want query to fetch both records so that i can compare which column record user has been updated in table
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Sat Jun 03, 2017 11:36 am    Post subject: Reply to: Need DB2 query to fetch previous row !
Reply with quote

it is bad netiquette to PM for help

remember that we reply on OUR own time and for free

if You have time constraint a paid consultant will be happy to provide assistance according to Your time requirements
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 378
Location: USA

PostPosted: Mon Jun 05, 2017 9:35 am    Post subject: Reply to: Need DB2 query to fetch previous row !
Reply with quote

Does your table has any primary key? If yes, then you won't be able to Insert another row without changing the primary key i.e. another row with same values barring some column that is updated and a change_column as 'U'. Since the new inserted row (with 'U') will have a different primary key value, you'll Not be able to uniquely identify which row was changed unless you have some other column combination to identify a row uniquely.

If your table doesn't have a primary key then you can have many rows which are replica of each other. In that case, you won't be able to tell which row was updated (or shall I call Inserted with a 'U' column).

You really need to provide us with some sample data about what you want to achieve and primary key and other relevant information (such as if you have a timestamp column in table).

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1737
Location: NY,USA

PostPosted: Mon Jun 05, 2017 5:56 pm    Post subject:
Reply with quote

You don't know what you want. Give us sample input and expected output of the query then you have chances here to get help.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Mon Jun 05, 2017 7:06 pm    Post subject: Reply to: Need DB2 query to fetch previous row !
Reply with quote

looks like the TS knows very little about the application flow/processing
( probably he did not care to investigate )

If he had looked at the update/insert logic he should have found enough hints on how to proceed for the inquiry
Back to top
View user's profile Send private message
vishwakotin

New User


Joined: 15 Mar 2017
Posts: 9
Location: India

PostPosted: Tue Jun 06, 2017 6:15 pm    Post subject:
Reply with quote

You can use something BMC Logmarker, which stores all the updates and inserts happened on that table. Then you can get both the rows using that.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1825
Location: UK

PostPosted: Tue Jun 06, 2017 6:55 pm    Post subject:
Reply with quote

However, there is no mention of BMC being installed at his site. As it is third party software it is quite possible that it is not installed.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Tue Jun 06, 2017 7:31 pm    Post subject: Reply to: Need DB2 query to fetch previous row !
Reply with quote

one of the previous TS replies hints that somehow the application logic keeps in the same table the before image of a row

a different can of worms will open when trying to update more than once the same row
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1737
Location: NY,USA

PostPosted: Tue Jun 06, 2017 7:59 pm    Post subject:
Reply with quote

Assuming you do insert a row (second time) with change_column as 'U' and your original row still has change_column <> 'U'. So hence forth all your updates to this unique record is performed on second row and first row is untouched. In that case you can try this.
Code:
select * from table1 A
where exists ( select 1 from table1 B
                     where A.key1 = B.key2
                        and B.change_column = 'U' )
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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