Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Unanswered: To compare two tables in db2

    what is the query to compare values in two different tables which contains same column name and datatype?

    Eg:
    TAB1
    ID COL1 COl2 Col3
    1 A A A
    2 B B B
    3 C C C
    4 A A A

    TAB2
    ID COL1 COl2 Col3
    1 A A A
    2 B B B
    3 C D C
    4 A A A


    Output should be :

    ID COL1 COl2 Col3
    3 C C C
    3 C D C
    Last edited by Sajeev; 08-03-11 at 05:11.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    with replication there is a utility shipped asntdiff for this purpose
    have a look at this in info center
    otherwise regular sql and joins.....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What information do you want?
    Depending on the information you want,
    you can use EXCEPT, NOT EXISTS, NOT IN, so on.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    SELECT 'Only in A:', count(*)
    FROM  (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
    UNION ALL
    SELECT 'Only in B:', count(*)
    FROM (SELECT * FROM B EXCEPT ALL SELECT * FROM A)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Aug 2011
    Posts
    10

    @All

    Thanks for ur reply..
    I got this doubt solved with this query.. Peter's query was also working fine..

    SELECT ID,COL1,COl2,COl3,count(*)
    FROM
    (


    SELECT A.ID, A.COL1,A.COL2,A.COL3
    FROM TAB1 A

    UNION ALL

    SELECT B.ID, B.COL1,B.COL2,B.COL3

    FROM TAB2 B
    ) TMPTBL
    GROUP BY
    ID,COL1,COL2,COl3

    HAVING COUNT(*) <> 2
    ORDER BY ID

  6. #6
    Join Date
    Aug 2011
    Location
    London, UK
    Posts
    3
    Quote Originally Posted by tonkuma View Post
    What information do you want?
    Depending on the information you want,
    you can use EXCEPT, NOT EXISTS, NOT IN, so on.
    Sure. All in Exccept

  7. #7
    Join Date
    Aug 2011
    Posts
    10

    @bee

    But with EXCEPT u may get duplicates.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    From Peter's example...
    Code:
    SELECT 'Only in A:', count(*)
    FROM  (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
    UNION ALL
    SELECT 'Only in B:', count(*)
    FROM (SELECT * FROM B EXCEPT ALL SELECT * FROM A)
    remove marked parts...
    Code:
    SELECT 'Only in A:', count(*)
    FROM  (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
    UNION ALL
    SELECT 'Only in B:', count(*)
    FROM (SELECT * FROM B EXCEPT ALL SELECT * FROM A)
    then, you'll get an answer.
    Code:
    (SELECT * FROM A EXCEPT ALL SELECT * FROM B)
    UNION ALL
    (SELECT * FROM B EXCEPT ALL SELECT * FROM A)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •