Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Select query should return different row

    Hi;

    Below simple select query should return two different result set based on
    the condition
    Code:
    SELECT t1.p_loc
         , t1.p_nor
         ,p_det 
    
          from table1 t1
    Table1

    Code:
    p_loc   p_NOR        p_det
    A11     1234         aaa
    B11     1311         WWW
    DEF     1111         DDD
    DEF     2222         EEE
    We will pass one flag variable(:ws-chk) value to the query from screen whether it will be 'P' or 'T'

    Condition is

    1. if the :ws-chk = 'P' means the select should return the P_LOC <> 'DEF' rows

    2. if the :ws-chk = 'T' means the select should return the P_LOC = 'DEF' rows

    Expected output

    Based on condition 1
    Code:
    p_loc   p_NOR        p_det
    A11     1234         aaa
    B11     1311         WWW
    Based on condition 2
    Code:
    p_loc   p_NOR        p_det
    DEF     1111         DDD
    DEF     2222         EEE
    Please help

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    sorry - wrong entry
    Last edited by przytula_guy; 12-12-11 at 08:42. Reason: error
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Condition is

    1. if the :ws-chk = 'P' means the select should return the P_LOC <> 'DEF' rows

    2. if the :ws-chk = 'T' means the select should return the P_LOC = 'DEF' rows
    Generally speaking, a condition
    IF condition-1 THEN use condition-2 ELSE IF condition-3 THEN use condition-4
    can be implemented in SQL
    condition-1 AND condition-2 OR NOT condition-1 AND condition-3 AND condition-4

    In your condition, condition-1 and condition-3 are mutually exclusive,
    then "NOT condition-1 AND" can be removed.

    As a cosequence,
    your condition can be implemented in SQL like...
    condition-1 AND condition-2 OR condition-3 AND condition-4
    where
    condition-1 is :ws-chk = 'P'
    condition-2 is P_LOC <> 'DEF'
    condition-3 is :ws-chk = 'T'
    condition-4 is P_LOC = 'DEF'

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    For clarity, I recommend to add some parenthesis. Although AND binds stronger than OR, it is obvious to the casual reader to have:
    Code:
    ( condition-1 AND condition-2 ) OR ( condition-3 AND condition-4 )
    It's just about maintainability...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Stolze,

    I agree with you about OR.

    I feel parentheses(for readability) are not so neccesary for AND or NOT.

    For example:
    order of readable and easy to understand representations may be (a) >= (b) >= (c) > (d) > (e) > (f).

    (a) parenthesize conditions connected by OR and conditions prefiexed by NOT
    Code:
           ( condition-1 AND ( NOT condition-2 ) AND condition-3 )
       OR  ( condition-4 )
       OR  ( ( NOT ( condition-5 AND condition-6 ) ) AND condition-7 )
    /* is equivalent to */
           ( condition-1 AND ( NOT condition-2 ) AND condition-3 )
       OR  ( condition-4 )
       OR  ( ( ( NOT condition-5 ) OR ( NOT condition-6 ) ) AND condition-7 )
    or
    Code:
     ( c-1 AND ( NOT c-2 ) AND c-3 ) OR ( c-4 ) OR ( ( NOT ( c-5 AND c-6 ) ) AND c-7 )
    (b) parenthesize conditions connected by OR
    Code:
           ( condition-1 AND NOT condition-2 AND condition-3 )
       OR  ( condition-4 )
       OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
    /* is equivalent to */
           ( condition-1 AND NOT condition-2 AND condition-3 )
       OR  ( condition-4 )
       OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )
    (c) parenthesize compound conditions connected by OR
    Code:
           ( condition-1 AND NOT condition-2 AND condition-3 )
       OR  condition-4
       OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
    /* is equivalent to */
           ( condition-1 AND NOT condition-2 AND condition-3 )
       OR  condition-4
       OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )
    (d) parenthesize conditions of right to OR
    Code:
           condition-1 AND NOT condition-2 AND condition-3
       OR  ( condition-4 )
       OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
    /* is equivalent to */
           condition-1 AND NOT condition-2 AND condition-3
       OR  ( condition-4 )
       OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )
    (e) parenthesize compound conditions of right to OR
    Code:
           condition-1 AND NOT condition-2 AND condition-3
       OR  condition-4
       OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
    /* is equivalent to */
           condition-1 AND NOT condition-2 AND condition-3
       OR  condition-4
       OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )
    (f) only neccesary parentheses
    Code:
          condition-1 AND NOT condition-2 AND condition-3
      OR  condition-4
      OR  NOT ( condition-5 AND condition-6 ) AND condition-7
    /* is equivalent to */
          condition-1 AND NOT condition-2 AND condition-3
      OR  condition-4
      OR  ( NOT condition-5 OR NOT condition-6 ) AND condition-7
    or
    Code:
     c-1 AND NOT c-2 AND c-3 OR c-4 OR NOT ( c-5 AND c-6 ) AND c-7

    While I prefer (c) or (e) and sometimes use (b),
    some person may prefer (a) or (b).


    Stolze,

    do you like (b) or (c)?
    Last edited by tonkuma; 12-18-11 at 08:43. Reason: Add " and sometimes use (b)"

Posting Permissions

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