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
 

 

Compare two tables and populate Action type(I,U,D,N)

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

New User


Joined: 09 Dec 2013
Posts: 5
Location: trivandrum

PostPosted: Wed Dec 11, 2013 7:55 pm    Post subject: Compare two tables and populate Action type(I,U,D,N)
Reply with quote

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
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Dec 11, 2013 8:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Dec 12, 2013 12:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
noti

New User


Joined: 09 Dec 2013
Posts: 5
Location: trivandrum

PostPosted: Thu Dec 12, 2013 6:34 pm    Post subject: Thank you All
Reply with quote

Ok with the help of union we can do as per the same, is there any better way than this? icon_smile.gif

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Dec 12, 2013 6:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Dec 12, 2013 6:47 pm    Post subject:
Reply with quote

The better way icon_smile.gif 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 icon_confused.gif )

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? icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Dec 12, 2013 7:01 pm    Post subject:
Reply with quote

or you could do an unload and run SuperC
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Dec 12, 2013 7:08 pm    Post subject:
Reply with quote

Or Unload & JOINKEYS
Back to top
View user's profile Send private message
noti

New User


Joined: 09 Dec 2013
Posts: 5
Location: trivandrum

PostPosted: Fri Dec 13, 2013 12:28 pm    Post subject: THANK YOU ALL
Reply with quote

Hi All,

Thanks for u r replies....
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Sat Dec 14, 2013 6:06 am    Post subject:
Reply with quote

You may also wants to look at EXCEPT and INTERSECT functions.
Back to top
View user's profile Send private message
noti

New User


Joined: 09 Dec 2013
Posts: 5
Location: trivandrum

PostPosted: Tue Dec 17, 2013 8:46 pm    Post subject: Query review
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Dec 17, 2013 9:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
noti

New User


Joined: 09 Dec 2013
Posts: 5
Location: trivandrum

PostPosted: Sun Dec 22, 2013 8:31 pm    Post subject: How to handle NULLIF( xxx)='?' data
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Dec 24, 2013 5:45 am    Post subject:
Reply with quote

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
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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts COMPARE command sivatechdrive TSO/ISPF 7 Fri Sep 16, 2016 4:31 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm


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