April 10, 2011 at 2:54 am
Hello Experts,
create table #tab1_audit
(
cls_run_id int,
prcs_dt datetime,
part_id int,
prdct_id char(15),
pay_clct_am numeric (19,9),
pstn_type_cd char(1),
ccy_cd char(3),
tick_val_am numeric (19,9),
tick_dnmtn_am numeric (19,9),
adt_ts datetime,
adt_user_id varchar(20),
adt_actn_cd char(2)
)
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 1:18 AM','APADMIN','CO')
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CO')
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112', -11880,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CN')
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-11880,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO')
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10,'P','INR',0.25,10000,'4/8/11 11:04 PM','APADMIN','CN')
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201112',-110,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO')
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201111',-110,'P','USD',0.25,20000,'4/8/11 11:04 PM','APADMIN','CN')
Here last column 'audit action code' : CO means OLD Value, CN means New Value.
How to Compare Two Rows and find which are all the columns are got changed for the each part id
actually i have around 20 columns in the table and need to list all the columns that are got changed.
Result set should be like
---------------------------------------------------------------------
ReferenceNo Columns_Changed OLD_VALUE NEW_VALUE
---------------------------------------------------------------------
1002 pay_clct_am -10800 -11880
1002 pay_clct_am -11880 -10
1002 ccy_cd USD INR
1003 prdct_id VEDF201112 VEDF201111
1003 tick_dnmtn_am 10000 20000
---------------------------------------------------------------------------
Kindly help me to get this report.
I could see some solutions in our site, which has used PIVOT table and 'XML codes', but kindly suggest me as a SQL code so that i have to implement this code in both SQL SERVER and SYBASE 15.0
Thanks,
Ganpat
April 10, 2011 at 4:53 am
Here's a SQL Server solution. AFAIK, CASE and CAST should work in SyBase 15.
SELECT
t1.part_id,
t1.adt_ts,
CASE
WHEN t1.pay_clct_am = t2.pay_clct_am
THEN 'unchanged'
ELSE 'old:' + CAST(t1.pay_clct_am AS VARCHAR(20)) + ', new:' + CAST(t2.pay_clct_am AS VARCHAR(20))
END AS pay_clct_am,
CASE
WHEN t1.ccy_cd = t2.ccy_cd
THEN 'unchanged'
ELSE 'old:' + CAST(t1.ccy_cd AS VARCHAR(20)) + ', new:' + CAST(t2.ccy_cd AS VARCHAR(20))
END AS pay_clct_am
FROM #tab1_audit t1
INNER JOIN #tab1_audit t2
ON t1.part_id=t2.part_id
AND t1.adt_ts=t2.adt_ts
AND t1.adt_actn_cd='CO'
AND t2.adt_actn_cd='CN'
WHERE
(
t1.pay_clct_am <> t2.pay_clct_am
OR t1.ccy_cd <> t2.ccy_cd
OR t1.tick_val_am <> t2.tick_val_am
OR t1.pay_clct_am <> t2.pay_clct_am
OR t1.tick_dnmtn_am <> t2.tick_dnmtn_am
)
April 10, 2011 at 6:04 am
Hi LutzM,
Thanks for your solution.
Now we are getting the result as follows:
----------------------------------------------------------------------------------
part_id adt_ts pay_clct_amccy_cd
----------------------------------------------------------------------------------
1002 4/8/11 10:29 AMold:-10800.000000000, new:-11880.000000000unchanged
1002 4/8/11 11:04 PM old:-11880.000000000, new:-10.000000000old:USD, new:INR
1003 4/8/11 11:04 PMunchangedunchanged
---------------------------------------------------------------------------------
Actually, (i) we no need to display the columns value which are not modified.
(2) the old value should display under column name OLD_VALUE,
(3) new value should display under column name NEW_VALUE,
(4) Modified column names should display under COLUMNS_CHANGED
Our Result set should be like below:
-------------------------------------------------------------------------
Part_ID Columns_Changed OLD_VALUE NEW_VALUE
-------------------------------------------------------------------------
1002 pay_clct_am -10800 -11880
1002 pay_clct_am -11880 -10
1002 ccy_cd USD INR
1003 prdct_id VEDF201112 VEDF201111
1003 tick_dnmtn_am 10000 20000
-------------------------------------------------------------------------
Thanks for spending your time.
April 10, 2011 at 7:11 am
I would use the UNPIVOT approach followed by a self join.
This would cover items 1 to 3.
I don't understand your last requirement (modified column names): How could this happen when you query just a single table?
Since I don't know enough about SyBase 15 I'd like to leave it for the folks knowing both systems.
As a side note: it would help others if you could name the columns identifying a single row. I just guessed part_id, adt_ts and adt_actn_cd...
April 10, 2011 at 8:35 pm
I have modified the #tab1_audit and included one more column name as 'Row_number'.
(i) When a update trigger inserts row into this #tab1_audit, both old and new (Modified) rows will insert with a unique number (say 100 for CO(old) and CN(New)) next to 'adt_actn_cd' column.
(ii) next modified row will insert with (101 for both CO(old) and CN(New))
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CO',100)
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112', -11880,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CN',100)
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-11880,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',101)
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10,'P','INR',0.25,10000,'4/8/11 11:04 PM','APADMIN','CN',101)
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201112',-110,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',102)
Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201111',-110,'P','USD',0.25,20000,'4/8/11 11:04 PM','APADMIN','CN',102)
So, we need to check the changes in column values between two rows using Row_number.
In my live table I have 25 columns. So I need to display changes for all these columns as below
------------------------------------------------------------------------
Part_ID Columns_Chngd OLD_VALUE NEW_VALUE
-------------------------------------------------------------------------
1002 pay_clct_am -10800 -11880
1002 pay_clct_am -11880 -10
1002 ccy_cd USD INR
1003 prdct_id VEDF201112 VEDF201111
1003 tick_dnmtn_am 10000 20000
-------------------------------------------------------------------------
March 1, 2019 at 7:46 pm
Ganapathi M - Sunday, April 10, 2011 8:35 PMI have modified the #tab1_audit and included one more column name as 'Row_number'. (i) When a update trigger inserts row into this #tab1_audit, both old and new (Modified) rows will insert with a unique number (say 100 for CO(old) and CN(New)) next to 'adt_actn_cd' column.(ii) next modified row will insert with (101 for both CO(old) and CN(New))Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CO',100)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112', -11880,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CN',100)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-11880,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',101)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10,'P','INR',0.25,10000,'4/8/11 11:04 PM','APADMIN','CN',101)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201112',-110,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',102)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201111',-110,'P','USD',0.25,20000,'4/8/11 11:04 PM','APADMIN','CN',102)So, we need to check the changes in column values between two rows using Row_number. In my live table I have 25 columns. So I need to display changes for all these columns as below------------------------------------------------------------------------Part_ID Columns_Chngd OLD_VALUE NEW_VALUE-------------------------------------------------------------------------1002 pay_clct_am -10800 -118801002 pay_clct_am -11880 -101002 ccy_cd USD INR1003 prdct_id VEDF201112 VEDF2011111003 tick_dnmtn_am 10000 20000-------------------------------------------------------------------------
Hi, do you mind to share how to do you do for this part? >> So, we need to check the changes in column values between two rows using Row_number. <<
March 1, 2019 at 9:47 pm
This post is 8 years old. Why not start your own post?
And how do you propose to find out what changed by using ROW_NUMBER()? Even if you did use a windowing function, I don't see how you would identify what changed. You could do it with a trigger if you're changing a value in the table. Are you just adding a new record with one or more columns having new values?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply