Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Compare data between 2 identical tables in DB2

    How does one compare data between 2 identical tables in DB2 ?
    In Oracle, one can use the minus option which is not there in DB2.

    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Using the standard SQL option EXCEPT or DIFFERENCE?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2003
    Posts
    93
    the except option worked like a charm.
    thanks

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by mgupta
    How does one compare data between 2 identical tables in DB2 ?
    In Oracle, one can use the minus option which is not there in DB2.
    Have a look at the "SQL ISO standard compatibility" appendix in the Oracle SQL Reference guide; there it is stated that the "MINUS" operator in Oracle is the non-standard form of the standard "EXCEPT" opetator. DB2 uses the standard name.
    --_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
    Oct 2003
    Posts
    93
    Peter,
    Thanks for the update. This clarifies things further.

  6. #6
    Join Date
    May 2009
    Posts
    17
    Hello,

    I would like to know if this EXCEPTfunction exists in DB2 Z/OS ? because I didn't find it !

    Thanks in advance

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by rd4004
    Hello,

    I would like to know if this EXCEPTfunction exists in DB2 Z/OS ? because I didn't find it !

    Thanks in advance

    Yes, it's introduced in Version 9.

    DB2 V9.1 - DB2 SQL - fullselect
    Last edited by umayer; 05-25-09 at 07:52.

  8. #8
    Join Date
    May 2009
    Posts
    17
    I'm in DB2 V8 !!!! :-(
    Is ther anything wich can replace that !???

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rd4004
    I'm in DB2 V8 !!!! :-(
    Is ther anything wich can replace that !???
    I think you can accomplish that with an OUTER JOIN.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    rd4004, what exactly do you want to do?

    If is just compare and find the values that do NOT match, then something along the lines of:
    Code:
    SELECT 'A' as TAB, A.KEY_DATA
      , CASE WHEN A.COL1 = B.COL1 THEN NULL ELSE A.COL1 END AS COL1
      , CASE WHEN A.COL2 = B.COL2 THEN NULL ELSE A.COL2 END AS COL2
      , ect.
    FROM table1 A
       , table2 B
    WHERE A.KEY_DATA = B.KEY_DATA
    UNION ALL
    SELECT 'B' as TAB, B.KEY_DATA
      , CASE WHEN B.COL1 = A.COL1 THEN NULL ELSE B.COL1 END AS COL1
      , CASE WHEN B.COL2 = A.COL2 THEN NULL ELSE B.COL2 END AS COL2
      , etc.
    FROM table1 A
       , table2 B
    WHERE A.KEY_DATA = B.KEY_DATA
    ORDER BY KEY_DATA, TAB
    could work. This is assuming both tables have the same key data. If not, you will need other queries to find rows in one table but not in the other.

    PS I haven't tested this out but I don't see any reason why those CASE expression won't work. IF NULL gives you problems you could try a default depending on the data type (i.e. space, NA 'matches' for char, 0, -999999 for numeric, 0001-01-01, 9999-12-31 for data)

    If you want to find and Replace one tables data with the other table's data, then a Left or Full outer join would probably work better (as n_i suggested).

    It all depends on what you really want to accomplish with the compare.

  11. #11
    Join Date
    Dec 2005
    Posts
    273
    I'd try something like that:


    SELECT * FROM
    (
    SELECT all-columns FROM tableA
    UNION ALL
    SELECT all-columns FROM tableB
    ) CTE
    GROUP BY all-columns
    HAVING COUNT(*) = 1

  12. #12
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by umayer
    I'd try something like that:


    SELECT * FROM
    (
    SELECT all-columns FROM tableA
    UNION ALL
    SELECT all-columns FROM tableB
    ) CTE
    GROUP BY all-columns
    HAVING COUNT(*) = 1
    wont work... what if 1st table has 1,2,3
    and 2nd table 1,2,4
    A except B should return 3.. right , but your query will return 3 and 4 both
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  13. #13
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by rahul_s80
    wont work... what if 1st table has 1,2,3
    and 2nd table 1,2,4
    A except B should return 3.. right , but your query will return 3 and 4 both

    ok - then use this:

    SELECT * FROM
    (
    SELECT all-columns FROM tableA
    UNION ALL
    SELECT all-columns FROM tableB
    UNION ALL
    SELECT all-columns FROM tableB
    ) CTE
    GROUP BY all-columns
    HAVING COUNT(*) = 1

  14. #14
    Join Date
    May 2009
    Posts
    17
    Hey !
    thank you to all of you !!

    I think the EXCEPT can write in this way as well :
    SELECT * FROM table1
    WHERE a not in (SELECT a from table2)


    I wanted to use EXCEPT and I finally wrote this SQL... but I didn't succeed in solving my problem.

    Here is a table TAB wich has 3 columns A char(1), B char(1), C char(1).
    I have the following data in it :
    A B C
    -------
    1 X X
    1 2 X
    A B C
    1 2 Y
    1 2 3

    the query I have to do is a query where :
    if I have the following condition A=1 and B=2 and C=3, I have to return all of them.
    Otherwise, if I have the condition A=1 and B=2, I have to return all of them.
    Otherwise, if I have the condition A=1, I have to return all of them.

    With the hereunder data we have to return the last record (1,2,3) but if we delete the following record the query should only return the records :
    1 2 X
    1 2 Y

    the solution I've found is :

    SELECT A, B, C, 1 AS Lev
    FROM table1
    WHERE A = '1'
    AND B = '2'
    AND C = '3'
    UNION ALL
    SELECT A, B, C, 2 AS Lev
    FROM table1
    WHERE A = '1'
    AND B = '2'
    UNION ALL
    SELECT A, B, C, 3 AS Lev
    FROM table1
    WHERE A = '1'

    The query returns ALL the records by Level (Lev column) 1 or 2 or 3 and in the prgm I have to stop the fetch when the Level changes

    Any other idea ???

  15. #15
    Join Date
    Dec 2005
    Posts
    273
    If you use the UNION ALL a row with
    A='1' AND B='2' AND C='3'
    will occur three times in your result query.

    But you can avoid the UNION ALL:

    SELECT A,B,C,
    CASE
    WHEN A='1' AND B='2' AND C='3' THEN 1
    WHEN A='1' AND B='2' THEN 2
    WHEN A='1' THEN 3
    END AS LEVEL
    FROM TABLE
    WHERE A='1'



    Quote Originally Posted by rd4004
    SELECT * FROM table1
    WHERE a not in (SELECT a from table2)
    this will fail if any a of table2 is NULL
    Last edited by umayer; 05-26-09 at 07:18.

Posting Permissions

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