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

    Unanswered: NOT EXISTS Problem with TIMESTAMP

    Hi,

    Why when we use NOT EXISTS with timestamp comparison behaving as not expected?

    For Example

    Code:
          SELECT COL1,COL2,TS_COL1 FROM TABLE_A A
              WHERE NOT EXISTS
                           (SELECT 1 FROM TABLE_B B
                                   A.COL1=B.COL1
                               AND A.COL2=B.COL2
                               AND A.TS_COL1=B.TS_COL1)
    The above query doesn't work properly as expected. Please suggest and let me know for any alternative.
    I am using DB2 Z/OS 10

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The above query doesn't work properly as expected.
    What result did you expected?
    And, what result acually did you got?

    Please publish test(or sample) data, and the result you expected and the result you got from the test(or sample) data.

  3. #3
    Join Date
    May 2006
    Posts
    20
    Quote Originally Posted by tonkuma View Post
    What result did you expected?
    And, what result acually did you got?

    Please publish test(or sample) data, and the result you expected and the result you got from the test(or sample) data.
    Code:
    SELECT  A.COL1,A.COL2,A.COL3  
    			FROM TABLE_A A
          WHERE NOT EXIST (SELECT 1 FROM 
          			   TABLE_B B
          			   WHERE A.COL1=B.COL1
          			   AND A.COL2=B.COL2
          			   AND A.TS_COL1=B.TS_COL1
          			   AND A.COL3 LIKE '%')
          								       
              
              
              
              
    Table_A          
    COL1                         	 COL2    	  TS_COL1														COL3
    DB2_TEST	                     DB2    		2012-11-02-15.37.52.000000        ABCD23                                                                          
    DB2_TEST	                     DB2        02.11.2012-16.12.44.000000				ADECK1                                                                           
    
    
                                                                                        
    Table_B     
    COL1                         	 COL2    	  TS_COL1														COL3												
    DB2_TEST	                     DB2        2012-11-02-16.12.44.000000        %
    
    
    EXPECTED OUTPUT 
    COL1                         	 COL2    	  TS_COL1														COL3
    DB2_TEST	                     DB2    		2012-11-02-15.37.52.000000        ABCD23
    Is this is because there is no comparison of Primary key?
    And how to resolve this? I want the format of the query should in the same manner using NOT EXISTS only.

    Is this is possible?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What result(output) did you got?

    I got your expected output from your data on DB2 9.7.5 for Windows,
    by adding A.TS_COL1 into (final) SELECT list.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Table_A
    ( COL1 , COL2 , TS_COL1 , COL3 ) AS (
    VALUES
      ( 'DB2_TEST' , 'DB2' , TIMESTAMP('2012-11-02-15.37.52.000000') , 'ABCD23' )
    , ( 'DB2_TEST' , 'DB2' , TIMESTAMP('2012-11-02-16.12.44.000000') , 'ADECK1' )
    )
    , Table_B
    ( COL1 , COL2 , TS_COL1 , COL3 ) AS (
    VALUES
      ( 'DB2_TEST' , 'DB2' , TIMESTAMP('2012-11-02-16.12.44.000000') , '%' )
    )
    SELECT A.COL1 , A.COL2 , A.TS_COL1 , A.COL3
     FROM  TABLE_A A
     WHERE NOT EXISTS
           (SELECT 1
             FROM  TABLE_B B
             WHERE A.COL1    = B.COL1
               AND A.COL2    = B.COL2
               AND A.TS_COL1 = B.TS_COL1
               AND A.COL3 LIKE '%'
           )
    ;
    ------------------------------------------------------------------------------
    
    COL1     COL2 TS_COL1                    COL3  
    -------- ---- -------------------------- ------
    DB2_TEST DB2  2012-11-02-15.37.52.000000 ABCD23
    
      1 record(s) selected.

  5. #5
    Join Date
    May 2006
    Posts
    20
    Table A doesn't have any key, the record we are distinguishing between the timestamp only. I am getting the output as both the record from Table A.

    Not sure why its so. Not only with the Timestamp field. If I define the field as Date and table A doesn't have any Key. The result give both records.

    Please suggest. Is this is because of TableA doesn't have any Key column and when we write a NOT EXISTS it is not able to distinguish between the records?

    Thanks

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Existence of some key(and use of it in the query) may not influence to the result.

    Table A doesn't have any key, the record we are distinguishing between the timestamp only.
    Though, values of col1 and col2 of all rows were same in your data.
    You used col1 and col2 additional to timestamp(s_col1), to distinguish rows.

    I am getting the output as both the record from Table A.
    I got one row on DB2 9.7.5 for Windows from your provided data, like I showed in my previous post.
    So, please publish exact data and query you used.
    (1-1) CREAE TABLE statements(for Table A and Table B).
    (1-2) CREAE INDEX statements, if used.
    (2) INSERT statements to populate the tables.
    (3) Copy and paste exact query you executed.
    (4) Copy and paste the output you got.

  7. #7
    Join Date
    May 2006
    Posts
    20
    Sorry I missed one entry to give in the tableb, so that is the reason you have got a single record.

    Below are the tables

    Code:
    TABLE_A
    COL1		CHAR(30)
    COL2		CHAR(5)
    TS_COL1	TIMESTAMP
    COL3		CHAR(10)
    
    TABLE_B
    COL1		CHAR(30)
    COL2		CHAR(5)
    TS_COL1	TIMESTAMP
    COL3		CHAR(10)
    Query
    Code:
    SELECT  A.COL1,A.COL2,A.COL3  
    			FROM TABLE_A A
          WHERE NOT EXIST (SELECT 1 FROM 
          			   TABLE_B B
          			   WHERE A.COL1=B.COL1
          			   AND A.COL2=B.COL2
          			   AND A.TS_COL1=B.TS_COL1
          			   AND A.COL3 LIKE '%')
    Data
    Code:
    Table_A          
    COL1                         	 COL2    	  TS_COL1														COL3
    DB2_TEST	                     DB2   		2012-11-02-15.37.52.000000        ABCD23
    DB2_TEST	                     DB2      	2012-11-02-16.12.44.000000				ADECK1
    
    
    Table_B     
    COL1                         	 COL2    	  TS_COL1														COL3
    DB2_TEST	                     DB2        2012-11-02-15.37.52.000000        AXCD
    DB2_TEST	                     DB2        2012-11-02-16.12.44.000000        %
    
    
    EXPECTED OUTPUT 
    COL1                         	 COL2    	  TS_COL1														COL3
    DB2_TEST	                     DB2    		2012-11-02-15.37.52.000000        ABCD23

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you expeced the result?
    Code:
    EXPECTED OUTPUT 
    COL1                         	 COL2    	  TS_COL1														COL3
    DB2_TEST	                     DB2    		2012-11-02-15.37.52.000000        ABCD23
    Because, there was a row having same col1, col2 and ts_col1 with the result row in Table B(first row).
    Code:
    Table_B     
    COL1                         	 COL2    	  TS_COL1		COL3
    DB2_TEST	                     DB2        2012-11-02-15.37.52.000000        AXCD
    In other words,
    for both rows in Table A, there exist a corresponding row in Table B.
    So, no row should be returned, according to my understandings of your requirements.
    Last edited by tonkuma; 06-12-13 at 09:28. Reason: Replace a row in Table B.

  9. #9
    Join Date
    May 2006
    Posts
    20
    Yes as you said no rows are returning.

    Because, there was a row having same col1, col2 and ts_col1 with the result row in Table B(first row).
    There is a single record exists with the filter and Timestamp combination.

    I wanted the record because the difference there in the timestamp and when apply filter with the like condition.

    Am I missing something with the way expected and used the query with not Exists?

    Thanks

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes as you said no rows are returning.

    Because, there was a row having same col1, col2 and ts_col1 with the result row in Table B(first row).
    There is a single record exists with the filter and Timestamp combination.
    If you agree with the result I got,
    where was your issue?
    (What I want to say, was that my query might be a solution for your requirement.)


    I wanted the record because the difference there in the timestamp and when apply filter with the like condition.
    "the difference there in the timestamp" ?
    My question is "the timestamp of which rows?"

    Do you want to compare which rows with which rows?

    Please clarify you requirements to compare which row with another which row, like....
    Compare the row(which saisfy some conditions) in Table A
    with the row(which saisfy (may be or may not be) other conditions) in Table B(or in Table A).


    "filter with the like condition" is a new condition.
    No LIKE condition was mentioned until now, except " AND A.COL3 LIKE '%' ".
    (" AND A.COL3 LIKE '%' " might be no influence into the result, at least from your supplied sample data.)
    If there was such condition, you should explain the condition by concrete example.


    Anyway,
    I couldn't undersand what were your requirements.
    (1) Your data were too small, and it is not clear what consrains were in your tables.
    I hope you publish more sample data including some exceptional data.

    (2) The rational to get the results you want from your sample data.


    As a consequence,
    I thought that I might be better to be silent, until I could understood the rational which were in your requirements.
    Last edited by tonkuma; 06-12-13 at 13:15.

Posting Permissions

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