Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    207

    Answered: Compare two tables with SQL NOT IN or NOT EXISTS

    Hello friends,

    I want to compare two tables with each other (Sysibm Table), the best thing would be to take the
    SQL "NOT IN" or "NOT EXISTS"

    Can anyone give me an example? Thank you for your support in advance.

  2. Best Answer
    Posted by db2dp

    "Hi DB_N00b,

    SELECT
    SUBSTR(NAME,1,8) AS NAME,
    SUBSTR(CREATOR,1,8) AS CREATOR,
    SUBSTR(TBNAME,1,8) AS TBNAME,
    SUBSTR(DBNAME,1,8) AS DBNAME,
    SUBSTR(CREATEDBY,1,8) AS CREATEDBY,
    SUBSTR(TBCREATOR,1,8) AS TBCREATOR
    FROM SYSIBM.SYSINDEX A
    WHERE NOT EXISTS
    (
    SELECT
    SUBSTR(NAME,1,8) AS NAME,
    SUBSTR(CREATOR,1,8) AS CREATOR
    FROM <How do you call the second SYSIBM.SYSINDEX> B
    WHERE
    A.NAME=B.NAME
    AND
    A.CREATOR=B.CREATOR
    )


    Good luck
    db2dp"


  3. #2
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    We need more details

    Hi DB_N00b,

    some more information like DDL of the tables would be helpfull.
    Which columns should be compared, EXIST OR NOT EXISTS ....

    Just guessing:

    SELECT FIELDX FROM TABLEA.XXX A
    WHERE NOT EXISTS
    (
    SELECT FIELDY FROM TABLEB.YYY B
    WHERE
    A.FIELDX=B.FIELDY
    )


    Good luck
    db2dp

  4. #3
    Join Date
    Jan 2010
    Posts
    207
    Hello db2dp,

    thank you for your answer.

    The first query looks like:

    SELECT
    SUBSTR(NAME,1,8) AS NAME,
    SUBSTR(CREATOR,1,8) AS CREATOR,
    SUBSTR(TBNAME,1,8) AS TBNAME,
    SUBSTR(DBNAME,1,8) AS DBNAME,
    SUBSTR(CREATEDBY,1,8) AS CREATEDBY,
    SUBSTR(TBCREATOR,1,8) AS TBCREATOR

  5. #4
    Join Date
    Jan 2010
    Posts
    207
    The table is from sysibm.sysindex

    I just want to compare two of them from different environments.

    Help is appreciated. Thank you.

  6. #5
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    We need more details

    Hi DB_N00b,

    SELECT
    SUBSTR(NAME,1,8) AS NAME,
    SUBSTR(CREATOR,1,8) AS CREATOR,
    SUBSTR(TBNAME,1,8) AS TBNAME,
    SUBSTR(DBNAME,1,8) AS DBNAME,
    SUBSTR(CREATEDBY,1,8) AS CREATEDBY,
    SUBSTR(TBCREATOR,1,8) AS TBCREATOR
    FROM SYSIBM.SYSINDEX A
    WHERE NOT EXISTS
    (
    SELECT
    SUBSTR(NAME,1,8) AS NAME,
    SUBSTR(CREATOR,1,8) AS CREATOR
    FROM <How do you call the second SYSIBM.SYSINDEX> B
    WHERE
    A.NAME=B.NAME
    AND
    A.CREATOR=B.CREATOR
    )


    Good luck
    db2dp

  7. #6
    Join Date
    Jan 2010
    Posts
    207
    I try that. Thank you

  8. #7
    Join Date
    Jan 2010
    Posts
    207
    QUERY MESSAGES:

    There is an invalid reference to a remote object in your query.

    Is coming as an error back now.

  9. #8
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    without federated DB, you can not have two objects from different subsystems in the same query. You can use a three part name to read a table on a remote subsystem, but you cannot join that 3 part named table with a local table. You can copy that data to a table on your local subsystem and then join with that table.

Posting Permissions

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