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

Need DB2 query to fetch previous row !


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

New User


Joined: 21 May 2017
Posts: 6
Location: India

PostPosted: Sat Jun 03, 2017 10:43 am
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sat Jun 03, 2017 10:51 am
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: 6
Location: India

PostPosted: Sat Jun 03, 2017 11:10 am
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sat Jun 03, 2017 11:36 am
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: 446
Location: USA

PostPosted: Mon Jun 05, 2017 9:35 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Jun 05, 2017 5:56 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Jun 05, 2017 7:06 pm
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
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: 2455
Location: Hampshire, UK

PostPosted: Tue Jun 06, 2017 6:55 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Jun 06, 2017 7:31 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jun 06, 2017 7:59 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Fetch data from programs execute (dat... DB2 3
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top