Results 1 to 7 of 7

Thread: Except Clause

  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: Except Clause

    Hello,

    My requirement is that i need to retrieve data from a SINGLE table for a very specific condition. There is a column I_MOD_YR in the table. I need the data that exists for the year 2020 but not for the year 2021.
    Note: 1.) Data for 2021 is a subset of data for 2020.
    2.) I am using DB2 Version 10
    Can i use EXCEPT on a single table? I tried but it didn't help, it just returned the records for the first select query and ignored the second select query.

    The exact query:
    Select * from tableA
    where
    I_MOD_YR = '2020'
    EXCEPT ALL
    Select * from tableA
    where
    I_MOD_YR = '2021'

    Is there another option?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A column in a row can only have one value, it can't be both '2020' and '2021' at the same time. Your EXCEPT clause makes no sense to me. I'm almost certain that what you really want/need is:
    Code:
    SELECT *
       FROM tableA
       WHERE  '2020' = I_MOD_YR
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by smarawar27 View Post
    2.) I am using DB2 Version 10

    The exact query:
    Select * from tableA
    where
    I_MOD_YR = '2020'
    EXCEPT ALL
    Select * from tableA
    where
    I_MOD_YR = '2021'

    Is there another option?
    Use specific column list excluding I_MOD_YR(not to use "*") in SELECT clauses.
    If the table has a primary key or unique column(s), exclude such columns from the SELECT list.
    Because, "*" include I_MOD_YR.
    So, all resuls of first SELECT query are different(at least I_MOD_YR) from results of second SELECT query.
    Last edited by tonkuma; 05-07-14 at 10:29. Reason: Add " If the table has ..."

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want values of a primary key or unique column(s),
    please try NOT EXISTS predicate.

    Code:
    SELECT *
     FROM  tableA AS t1
     WHERE I_MOD_YR = '2020'
      AND  NOT EXISTS
           (SELECT 0
             FROM  tableA AS t2
             WHERE I_MOD_YR = '2021'
              AND  t2.col_x = t1.col_x
              AND  t2.col_y = t1.col_y
              AND  t2.col_z = t1.col_z
              ...
           )
    ;
    where col_x, col_y, col_z, so on... are the columns to be compared.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT r.*
     FROM  (SELECT s.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY col_x
                                       , col_y
                                       , col_z
                                       ...
                                ORDER BY I_MOD_YR DESC
                          ) AS rn
             FROM  tableA AS s
             WHERE I_MOD_YR IN ('2020' , '2021')
           ) AS r
     WHERE I_MOD_YR = '2020'
      AND  rn       = 1
    ;
    where col_x, col_y, col_z, so on... are the columns to be compared.
    Last edited by tonkuma; 05-11-14 at 02:28. Reason: Revise format of code.

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    hi,
    another solution might be:
    Select col_x,col_y,col_z,max(I_MOD_YR)
    from tableA
    where I_MOD_YR in ( '2020','2021')
    group by col_x,col_y,col_z
    having max(I_MOD_YR) = '2020'

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    fengsun2,

    Based on my simple test,
    Your query(using GROUP BY and HAVING) looks slightly better(less cost) than my query using ROW_NUMBER.

Tags for this Thread

Posting Permissions

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