|
View previous topic :: View next topic
|
| Author |
Message |
noti
New User
Joined: 09 Dec 2013
Posts: 5
Location: trivandrum
|
|
|
|
COMPARE THE TODAY'S TABLE DATA WITH THE PREVIOUS DAY
BACK UP TABLE DATA AND POPULATE THE AUDIT TABLE WITH APPROPRIATE ACTION TYPE(I, U, D, N).
I-INSERT,U-UPDATE,D=DELETE,N=NO CHANGE
NOTE:don't have date field in my tables and both tables contain the num of columns,data type and sizes are same.
Thanks in adavace. [/Search][/url] |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
If you have extract of the data Compare yesterdays data with todays and prepare the data and that could help in updating / populating the audit table
Or
DPROP or Triggers May be if you are looking for DB2 based solution |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
Better approach could be, while processing today's data have additional columns doing auditing work.
If you really want to do the way you are asking, Joining both columns with some would do the job. There should be 4 sets you need to consider
1: Left outer join - Deleted rows
2: Right outer join - Added rows
3: Inner join with all other columns also match with each other - No change
4: Inner join with at-least one column value do not match with back up table column - Updated |
|
| Back to top |
|
|
noti
New User
Joined: 09 Dec 2013
Posts: 5
Location: trivandrum
|
|
|
|
Ok with the help of union we can do as per the same, is there any better way than this? 
Thanks in adavace
| Escapa wrote: |
Better approach could be, while processing today's data have additional columns doing auditing work.
If you really want to do the way you are asking, Joining both columns with some would do the job. There should be 4 sets you need to consider
1: Left outer join - Deleted rows
2: Right outer join - Added rows
3: Inner join with all other columns also match with each other - No change
4: Inner join with at-least one column value do not match with back up table column - Updated |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Do a full outer join, something resembling this :
| Code: |
select , Case
when oldkey is null then 'I' end
when newkey is null then 'D' end
when olddatacolA <> newdatacolA
or olddatacolB <> newatacolB
or olddatacolC <> newdatacolC
or ...
then 'U' end
else'N' end
end as actioncode
, * from (... New full outer join OLD on new.key = old.key) |
|
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
The better way is to throw the whole approach in the trash.
You have Mondays Backup (full data), Tuesdays data (full current data) and Tuesday Audit (full data with change flag, including no change  )
Then have another Audit on Wednesday?
And have a process for creating this mess?
Stop the madness.
And what is it with the smiley face at the end of a bad question? Does it make it ok? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| or you could do an unload and run SuperC |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Or Unload & JOINKEYS |
|
| Back to top |
|
|
noti
New User
Joined: 09 Dec 2013
Posts: 5
Location: trivandrum
|
|
|
|
Hi All,
Thanks for u r replies.... |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| You may also wants to look at EXCEPT and INTERSECT functions. |
|
| Back to top |
|
|
noti
New User
Joined: 09 Dec 2013
Posts: 5
Location: trivandrum
|
|
|
|
By using your's valuable posts i prepared this query could plz kindly review and let me know your comments.(whether this will work fine or not?)
| Code: |
DECLARE XX-CURSOR CURSOR WITH HOLD FOR
SELECT ONE.COLUMN1
,ONE.COLUMN2
,ONE.COLUMN3
,'U'
FROM TABLE1 ONE
INNER JOIN
TABLE2 TWO
ON
(ONE.COLUMN1 = TWO.COLUMN1
AND
ONE.COLUMN2 = TWO.COLUMN2)
AND
ONE.COLUMN3 != TWO.COLUMN3
UNION
SELECT ONE.COLUMN1
,ONE.COLUMN2
,ONE.COLUMN3
,'N'
FROM TABLE1 ONE
INNER JOIN
TABLE2 TWO
ON
(ONE.COLUMN1 = TWO.COLUMN1
AND
ONE.COLUMN2 = TWO.COLUMN2)
AND
ONE.COLUMN3 = TWO.COLUMN3
UNION
SELECT TWO.COLUMN1
,TWO.COLUMN2
,TWO.COLUMN3
,'D'
FROM TABLE2 TWO
LEFT OUTER JOIN
TABLE1 ONE
ON
(ONE.COLUMN1 = TWO.COLUMN1
AND
ONE.COLUMN2 = TWO.COLUMN2)
AND
ONE.COLUMN3 = TWO.COLUMN3
WHERE
(((ONE.COLUMN1) IS NULL)
AND
((ONE.COLUMN2) IS NULL)
AND
((ONE.COLUMN3) IS NULL))
UNION
SELECT ONE.COLUMN1
,ONE.COLUMN2
,ONE.COLUMN3
,'I'
FROM TABLE2 TWO
RIGHT OUTER JOIN
TABLE1 ONE
ON
(ONE.COLUMN1 = TWO.COLUMN1
AND
ONE.COLUMN2 = TWO.COLUMN2)
AND
ONE.COLUMN3 = TWO.COLUMN3
WHERE
(((TWO.COLUMN1) IS NULL)
AND
((TWO.COLUMN2) IS NULL)
AND
((TWO.COLUMN3) ISNULL))
ORDER BY ONE.COLUMN1
FOR FETCH ONLY
END-EXEC. |
NOTE:
1.In table1 and table2 contains same num of columns and same number of primary keys(column1, column2 are the primary keys)
2.Here TABLE1 has today's data, TABLE2 has yesterday's data(Back Up table)
Thanks in advance...... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
What happens when you execute this?
You also need to use the Code tag when posting things that need to retain alignment. Your previous reply has been "Code'd". |
|
| Back to top |
|
|
noti
New User
Joined: 09 Dec 2013
Posts: 5
Location: trivandrum
|
|
|
|
Hi,
For the above query i can't able to get the effect of no chage 'N'.
(ONE.COLUMN1 = TWO.COLUMN1
AND
ONE.COLUMN2 = TWO.COLUMN2)
AND
ONE.COLUMN3 = TWO.COLUMN3
my tables ONE, TWO has the same data, but default with '?' data. so when ever i have '?' can't able to handle.
plz help me ...
note:fields declared with NULLIF( xxx)='?'
Thanks in advance. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Use Column IS NOT NULL condition for every column and get the data from each table and then have the inner join. |
|
| Back to top |
|
|
|
|
