Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    20

    Unanswered: Find Non-Matching Records

    Hi
    I am trying to find the non-matching records from the 3 tables.

    Requirement is select the records from Table A which are not matching the pattern of Table B and Not in Table C and insert into Table D

    Below is an example

    Code:
    Table A
    ID	col1	col2	col3
    1	NAME1	DB0I	ONLINE
    2	NAME2	DB2D	ONLINE
    3	NAME3	DB2K	BATCH
    4	NAME4	DB34	BAT-CH
    Code:
    Table B
    ID	col1	col2	col3
    1	NAME%	DB2_	ONL%
    2	NAME%	%	ABCD
    3	ABCD%	%	%
    Code:
    Table C
    ID	col1	col2	col3
    1	NAME1	DB0I	ONLINE
    Output Should be
    Code:
    Table D
    ID	col1	col2	col3
    3	NAME3	DB2K	BATCH
    4	NAME4	DB34	BAT-CH
    I tried the below code and not working because the pattern is came as % and matching with column data. So I got all the records from Table A as output and inserted into Table D . Any solution is highly appreciated. I need a single query to the requirement.
    Code:
    INSERT INTO TABLE D VALUES(
    SELECT *
    FROM TABLEA AS X
    WHERE NOT EXISTS(SELECT 1 FROM TABLEB Y
    WHERE Y.ID=X.ID
    AND (X.COL1 LIKE 'NAME%'
          OR X.COL1 LIKE 'NAME%'
          OR X.COL1 LIKE 'ABCD%')
    AND (X.COL2 LIKE 'DB2_'
          OR X.COL2 LIKE '%'
          OR X.COL2 LIKE '%')
    AND (X.COL3 LIKE 'ONL%'
          OR X.COL3 LIKE '%')));
    Last edited by shrivatsa; 06-08-13 at 07:21.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why ID = 2 was not selected?

    Because,
    'ONLINE'/*A.col3*/ NOT LIKE 'ABCD'/*B.col3*/
    for ID = 2.
    And ID = 2 was Not in Table C.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are your DB2 version/release/fixpack and plaform OS?

  4. #4
    Join Date
    May 2006
    Posts
    20
    my Db2 version is Z/OS 10

    The record 2 is not selected because of the OR condition used. and Col3 is ONLINE in first record of Table A which is matched and record 2 is not displyed.

    Hope I am clear for what you have asked.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The record 2 is not selected because of the OR condition used.
    If OR condition was used, why ID = 3 was selected?
    ID = 3 matched in col2 and col3, because values in Table B
    are '%' which matches any string except NULL.

    and Col3 is ONLINE in first record of Table A which is matched and record 2 is not displyed.
    Why the value of first record of Table A(other ID) influences to selection of record 2?

    You compared rows in TableA and TableB having same id(Y.ID=X.ID).
    Code:
    ...
    FROM TABLEA AS X
    WHERE NOT EXISTS(SELECT 1 FROM TABLEB Y
    WHERE Y.ID=X.ID
    ...

  6. #6
    Join Date
    May 2006
    Posts
    20
    I am getting output like below after executing query

    Code:
    INSERT INTO TABLE D VALUES(
    SELECT *
    FROM TABLEA AS X
    WHERE NOT EXISTS(SELECT 1 FROM TABLEB Y
    WHERE Y.ID=X.ID
    AND (X.COL1 LIKE 'NAME%'
          OR X.COL1 LIKE 'NAME%'
          OR X.COL1 LIKE 'ABCD%')
    AND (X.COL2 LIKE 'DB2_'
          OR X.COL2 LIKE '%'
          OR X.COL2 LIKE '%')
    AND (X.COL3 LIKE 'ONL%'
          OR X.COL3 LIKE '%')));
    Code:
    ID	col1	col2	col3
    1	NAME1	DB0I	ONLINE
    2	NAME2	DB2D	ONLINE
    3	NAME3	DB2K	BATCH
    4	NAME4	DB34	BAT-CH
    but the requirement is to get the output as below
    Code:
    Table D
    ID	col1	col2	col3
    3	NAME3	DB2K	BATCH
    4	NAME4	DB34	BAT-CH


    Record 1 should skip because its exists in Table C
    Record 2 should skip because an pattern exists in Table B record #1

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Record 2 should skip because an pattern exists in Table B record #1
    So, I think that you should not specify Y.ID=X.ID in your query.
    Am I right?
    Code:
    SELECT *
    FROM TABLEA AS X
    WHERE NOT EXISTS(SELECT 1 FROM TABLEB Y
    WHERE Y.ID=X.ID
    AND ...
    ...

  8. #8
    Join Date
    May 2006
    Posts
    20
    ok In the query I have not written table C
    and I have removed the matching condition x.id=y.id but now I am getting the result as

    Code:
    INSERT INTO TABLE D VALUE(SELECT *
    FROM TABLEA  X
    WHERE NOT EXISTS(SELECT 1 FROM TABLEB Y
    WHERE 
     (X.COL1 LIKE 'NAME%'
          OR X.COL1 LIKE 'NAME%'
          OR X.COL1 LIKE 'ABCD%')
    AND (X.COL2 LIKE 'DB2_'
          OR X.COL2 LIKE '%'
          OR X.COL2 LIKE '%')
    AND (X.COL3 LIKE 'ONL%'
          OR X.COL3 LIKE '%'))
    and not exists(select 1 from tablec z
    where x.id=z.id);
    Result
    Here Record 2 should have not come as Pattern exists in Table B Rec#1
    Code:
    ID	col1	col2	col3
    2	NAME2	DB2D	ONLINE
    3	NAME3	DB2K	BATCH
    4	NAME4	DB34	BAT-CH
    Any suggestions will highly appreciated.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try

    Example 1:
    Code:
    SELECT *
     FROM  Table_A AS a
     WHERE
      NOT
      (    a.col1 LIKE 'NAME%'
       AND a.col2 LIKE 'DB2_'
       AND a.col3 LIKE 'ONL%'
       OR
           a.col1 LIKE 'NAME%'
       AND a.col2 LIKE '%'
       AND a.col3 LIKE 'ABCD'
       OR
           a.col1 LIKE 'ABCD%'
       AND a.col2 LIKE '%'
       AND a.col3 LIKE '%'
      )
    EXCEPT
    SELECT * FROM Table_C
    ;

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    OR

    Example 2: Applied logical equalities to the WHERE condition in Example 1.
    Code:
    SELECT *
     FROM  Table_A AS a
     WHERE
      (    a.col1 NOT LIKE 'NAME%'
       OR  a.col2 NOT LIKE 'DB2_'
       OR  a.col3 NOT LIKE 'ONL%'
      )
      AND
      (    a.col1 NOT LIKE 'NAME%'
       OR  a.col2 NOT LIKE '%'
       OR  a.col3 NOT LIKE 'ABCD'
      )
      AND
      (    a.col1 NOT LIKE 'ABCD%'
       OR  a.col2 NOT LIKE '%'
       OR  a.col3 NOT LIKE '%'
      )
    EXCEPT
    SELECT * FROM Table_C
    ;

  11. #11
    Join Date
    May 2006
    Posts
    20
    Quote Originally Posted by tonkuma View Post
    Please try

    Example 1:
    Code:
    SELECT *
     FROM  Table_A AS a
     WHERE
      NOT
      (    a.col1 LIKE 'NAME%'
       AND a.col2 LIKE 'DB2_'
       AND a.col3 LIKE 'ONL%'
       OR
           a.col1 LIKE 'NAME%'
       AND a.col2 LIKE '%'
       AND a.col3 LIKE 'ABCD'
       OR
           a.col1 LIKE 'ABCD%'
       AND a.col2 LIKE '%'
       AND a.col3 LIKE '%'
      )
    EXCEPT
    SELECT * FROM Table_C
    ;
    Result I am getting is
    Code:
      
      3 NAME3    DB2K     BATCH     
      4 NAME4    DB3J     BATCH
    and this is the required output

    I have just added Trim function to match as there is a space exists.

    Code:
    SELECT *
     FROM  Table_A AS a
     WHERE
      NOT
      (    a.col1 LIKE 'NAME%'
       AND trim(a.col2) LIKE 'DB2_'
       AND trim(a.col3) LIKE 'ONL%'
       OR
           a.col1 LIKE 'NAME%'
       AND trim(a.col2) LIKE '%'
       AND trim(a.col3) LIKE 'ABCD'
       OR
           a.col1 LIKE 'ABCD%'
       AND trim(a.col2) LIKE '%'
       AND trim(a.col3) LIKE '%'
      )
    EXCEPT
    SELECT * FROM Table_C
    ;
    Thank you so much for all the effort in building the query.
    Last edited by shrivatsa; 06-08-13 at 14:17.

  12. #12
    Join Date
    May 2006
    Posts
    20
    Quote Originally Posted by tonkuma View Post
    OR

    Example 2: Applied logical equalities to the WHERE condition in Example 1.
    Code:
    SELECT *
     FROM  Table_A AS a
     WHERE
      (    a.col1 NOT LIKE 'NAME%'
       OR  a.col2 NOT LIKE 'DB2_'
       OR  a.col3 NOT LIKE 'ONL%'
      )
      AND
      (    a.col1 NOT LIKE 'NAME%'
       OR  a.col2 NOT LIKE '%'
       OR  a.col3 NOT LIKE 'ABCD'
      )
      AND
      (    a.col1 NOT LIKE 'ABCD%'
       OR  a.col2 NOT LIKE '%'
       OR  a.col3 NOT LIKE '%'
      )
    EXCEPT
    SELECT * FROM Table_C
    ;
    I am not getting any record in the output with the execution of this query.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by shrivatsa View Post
    I am not getting any record in the output with the execution of this query.
    I tested Example 2 on DB2 9.7.5 for Windows.
    And it returned the required output, like this...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Table_A
    ( ID , col1 , col2 , col3 ) AS (
    VALUES
      ( 1 , 'NAME1' , 'DB0I' , 'ONLINE' )
    , ( 2 , 'NAME2' , 'DB2D' , 'ONLINE' )
    , ( 3 , 'NAME3' , 'DB2K' , 'BATCH'  )
    , ( 4 , 'NAME4' , 'DB34' , 'BAT-CH' )
    )
    , Table_B
    ( ID , col1 , col2 , col3 ) AS (
    VALUES
      ( 1 , 'NAME%' , 'DB2_' , 'ONL%' )
    , ( 2 , 'NAME%' , '%'    , 'ABCD' )
    , ( 3 , 'ABCD%' , '%'    , '%'    )
    )
    , Table_C
    ( ID , col1 , col2 , col3 ) AS (
    VALUES
      ( 1 , 'NAME1' , 'DB0I' , 'ONLINE' )
    )
    SELECT *
     FROM  Table_A AS a
     WHERE
      (    a.col1 NOT LIKE 'NAME%'
       OR  a.col2 NOT LIKE 'DB2_'
       OR  a.col3 NOT LIKE 'ONL%'
      )
      AND
      (    a.col1 NOT LIKE 'NAME%'
       OR  a.col2 NOT LIKE '%'
       OR  a.col3 NOT LIKE 'ABCD'
      )
      AND
      (    a.col1 NOT LIKE 'ABCD%'
       OR  a.col2 NOT LIKE '%'
       OR  a.col3 NOT LIKE '%'
      )
    EXCEPT
    SELECT * FROM Table_C
    ;
    ------------------------------------------------------------------------------
    
    ID          COL1  COL2 COL3  
    ----------- ----- ---- ------
              3 NAME3 DB2K BATCH 
              4 NAME4 DB34 BAT-CH
    
      2 record(s) selected.

Posting Permissions

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