Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Question Unanswered: select rows with conditions

    Hello Everyone

    I have a problem with DB2 : I can't find a way to make a select with conditional statement on the result. The fact is that there are two columns type1 and type2 in my table.

    I would like, in one query, to select the rows with type1=0 and type2=100, and, if the set of rows is empty (and only in this case), to select the rows with type1=1 and type2=100, and, if the set is empty too (and only in this case), to select the rows with type1=3 and type2=0.

    I can't see a way to use a UNION or even a ((type1=0 and type2=100) or (type1=1 and type2=100) or (type1=3 and type2=0) with a group by to have, at the end, only the first set of rows that is not empty among these three conditions.

    One solution could be to make the first query, fetch it, and, if the result is empty, the second, and, if it is empty, the third, but I would really appreciate a way to do it in one signle query.

    Thanks very much for your help.

    Simon

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Simon,
    Personally, I would probably do this as a stored procedure, where it would be simple enough to code and would run with minimal impact on performance.

    But if you truly want to do it as a query you could try something like this

    select columns from table where
    ((1,100) in (select type1,type2 from table))
    or
    (((1,100) NOT IN (select type1,type2 from table)) and ((1,100) IN (select type1,type2 from table)) )
    or
    (((1,100) NOT IN (select type1,type2 from table)) and ((1,100) NOT IN (select type1,type2 from table)) and ((3,0) IN (select type1,type2 from table)))

    HTH

    Andy

  3. #3
    Join Date
    Apr 2003
    Posts
    3

    Thumbs up

    Hello Andy

    It seems to work exactly the way I need. Thanks very much for your help !!

    See you

    Simon

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How is the performance?

    Andy

    Originally posted by technique
    Hello Andy

    It seems to work exactly the way I need. Thanks very much for your help !!

    See you

    Simon

  5. #5
    Join Date
    Apr 2003
    Posts
    3

    Post

    So far, not that bad, since the table is rather small...

    See you

    Simon

Posting Permissions

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