Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: where condition with if

    I have a query like this

    select A
    from file.lib
    where h<>0

    I want to add conditon like if A = 'abc', where h <>1
    else where h<>0

    How can I do this in one query?

    Thanks,

    Lisa

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select A
    from file.lib
    where (h<>0 and A <> 'abc')
    or (h <> 1 and A = 'abc');
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2003
    Posts
    32
    Great!! Great!!

  4. #4
    Join Date
    Sep 2003
    Posts
    32
    I have another trouble:
    Actually, my query is more complicated than the example
    select A, B
    from file.lib
    where (h<>0 and A <> 'abc')
    or (h <> 1 and A = 'abc');

    But actually, I want field B's condition always be h<>0

  5. #5
    Join Date
    Sep 2003
    Posts
    32
    Actually, it is:
    select A, sum(B), sum(c)
    from file.lib
    where (h<>0 and A <> 'abc')
    or (h <> 1 and A = 'abc');

    For sum(B), the query result is correct. For sum(C), the conditon is always where (h<>0)

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Newer, try the CASE expression:
    Code:
    SELECT A
         , SUM(CASE WHEN H <> 0 THEN B 
                                ELSE 0
               END)
         , SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
                    WHEN H <> O               THEN C
                                              ELSE 0 
               END)
    FROM file.lib
    GROUP BY A

  7. #7
    Join Date
    Sep 2003
    Posts
    32
    Thank you so much. SQL could be so beautiful!
    Just another question, I want to the T as follow, is there any better way to write the SQL?

    SELECT A
    , SUM(CASE WHEN H <> 0 THEN B
    ELSE 0
    END)
    , SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
    WHEN H <> O THEN C
    ELSE 0
    END)
    , (SUM(CASE WHEN H <> 0 THEN B
    ELSE 0
    END)/SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
    WHEN H <> O THEN C
    ELSE 0
    END)) AS T
    FROM file.lib

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I haven't done the math to verify this but I believe this is equivalent:
    Code:
    SELECT A, SUM_B, SUM_C, SUM_B / SUM_C AS T
    FROM (
          SELECT A
               , SUM(CASE WHEN H <> 0 THEN B 
                                      ELSE 0
                     END) AS SUM_B
               , SUM(CASE WHEN H <> 1 AND A = 'abc' THEN C
                          WHEN H <> O               THEN C
                                                    ELSE 0 
                     END) AS SUM_C
          FROM file.lib
          GROUP BY A
         ) AS NT

  9. #9
    Join Date
    Sep 2003
    Posts
    32
    OK, never mind. I think the previous one without subquery and performance is probabely better.

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Actually, it isn't using a subquery. It is using a Nested table. You do need to compare both to see if there is a significant difference in performance but they should run in about the same time.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, for a performance boost change the else 0 to else null. Reason being is a zero is added to your sum, while a null is ignored. This has been shown in some cases to improve query performance by up to 40%.

    Dave Nance

Posting Permissions

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