View previous topic :: View next topic
|
Author |
Message |
Chandan1993
New User
Joined: 21 May 2017 Posts: 6 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
Chandan1993
New User
Joined: 21 May 2017 Posts: 6 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
vishwakotin
New User
Joined: 15 Mar 2017 Posts: 9 Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
|