Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Where clause CASE

    Hi;

    Please find the below query and need to filter the rows based on the
    working storage filter value
    Code:
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         ,am_cost
         
     FROM  
    TABLE1
    
    WHERE  
    
      FILTER_PT = :ws-filter
    
      and 
       AM_COST > 0
    Code:
    TABLE1
    
     POLICY_LOC POLICY_NO FILTER_PT    AM_COST    
    --------  ---------- ----------- ----------- 
      AAA          1111      A       400   
      BBB          2222      I       500
      CCC          3333      A      1700
      DDD          4444      A      5600
      EEE          5555      A      6100
      FFF          6666      A      4600
      GGG          7777      I      1600
    The query should return the rows based on the :ws-filter



    EXPECTED RESULT set
    Code:
    IF :WS-FILTER = A(means FILTER_PT has only 'A' rows)
    
     POLICY_LOC POLICY_NO FILTER_PT    AM_COST    
    --------  ---------- ----------- ----------- 
      AAA          1111      A       400   
      CCC          3333      A      1700
      DDD          4444      A      5600
      EEE          5555      A      6100
      FFF          6666      A      4600
      
    
    IF :WS-FILTER = I (means all row either FILTER_PT has 'A' or 'I")
    
    POLICY_LOC POLICY_NO FILTER_PT    AM_COST    
    --------  ---------- ----------- ----------- 
      AAA          1111      A       400   
      BBB          2222      I       500
      CCC          3333      A      1700
      DDD          4444      A      5600
      EEE          5555      A      6100
      FFF          6666      A      4600
      GGG          7777      I      1600
    Please help

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Billa007 View Post
    Hi;

    Please find the below query and need to filter the rows based on the
    working storage filter value
    Code:
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         ,am_cost
         
     FROM  
    TABLE1
    
    WHERE  
    
      FILTER_PT = :ws-filter
    
      and 
       AM_COST > 0
    Code:
    TABLE1
    
     POLICY_LOC POLICY_NO FILTER_PT    AM_COST    
    --------  ---------- ----------- ----------- 
      AAA          1111      A       400   
      BBB          2222      I       500
      CCC          3333      A      1700
      DDD          4444      A      5600
      EEE          5555      A      6100
      FFF          6666      A      4600
      GGG          7777      I      1600
    The query should return the rows based on the :ws-filter



    EXPECTED RESULT set
    Code:
    IF :WS-FILTER = A(means FILTER_PT has only 'A' rows)
    
     POLICY_LOC POLICY_NO FILTER_PT    AM_COST    
    --------  ---------- ----------- ----------- 
      AAA          1111      A       400   
      CCC          3333      A      1700
      DDD          4444      A      5600
      EEE          5555      A      6100
      FFF          6666      A      4600
      
    
    IF :WS-FILTER = I (means all row either FILTER_PT has 'A' or 'I")
    
    POLICY_LOC POLICY_NO FILTER_PT    AM_COST    
    --------  ---------- ----------- ----------- 
      AAA          1111      A       400   
      BBB          2222      I       500
      CCC          3333      A      1700
      DDD          4444      A      5600
      EEE          5555      A      6100
      FFF          6666      A      4600
      GGG          7777      I      1600
    Please help
    Since 'A' < 'I':

    Code:
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         ,am_cost
    FROM TABLE1
    WHERE FILTER_PT <= :ws-filter
    If the implicit ordering doesn't hold in general you have to invent one that you can use
    --
    Lennart

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply...it is not giving the expected result

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Billa007 View Post
    Thanks for the reply...it is not giving the expected result
    I'm overwhelmed by the amount of information you provided. It will take me sometime to wade through all of it, but as soon as I have grasped the essence of it I will return with a definite answer. If you want to speed up this process, please provide either insert statements or a cte with sample data, adn expected output
    --
    Lennart

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Solution

    Solution is very easy and understandable:

    Code:
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         , am_cost
         
     FROM  TABLE1
    WHERE  
      case when :ws-filter = 'A' 
           then 'A'
           when :ws-filter = 'I' 
           then FILTER_PT 
      end  = FILTER_PT 
    and AM_COST > 0
    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Another Solution

    Could have the different expressions with the same result:

    Code:
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         , am_cost
         
     FROM  TABLE1
    WHERE  
        ifnull(nullif(:ws-filter, 'I'), FILTER_PT) = FILTER_PT 
    and AM_COST > 0
    Lenny

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    Thanks for the reply...it is not giving the expected result
    Lennart's example returned exactly same as "EXPECTED RESULT set" you showed, on my DB2 9.7.5 for LUW.

    Please explain "it is not giving the expected result".
    On what environment, what results did you got?

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      TABLE1(POLICY_LOC , POLICY_NO , FILTER_PT , AM_COST) AS (
    VALUES
      ( 'AAA' , 1111 , 'A' ,  400 )  
    , ( 'BBB' , 2222 , 'I' ,  500 )
    , ( 'CCC' , 3333 , 'A' , 1700 )
    , ( 'DDD' , 4444 , 'A' , 5600 )
    , ( 'EEE' , 5555 , 'A' , 6100 )
    , ( 'FFF' , 6666 , 'A' , 4600 )
    , ( 'GGG' , 7777 , 'I' , 1600 )
    )
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         ,am_cost
    FROM TABLE1
    WHERE FILTER_PT <= /*:ws-filter*/ 'A';
    ------------------------------------------------------------------------------
    
    POLICY_LOC POLICY_NO   FILTER_PT AM_COST    
    ---------- ----------- --------- -----------
    AAA               1111 A                 400
    CCC               3333 A                1700
    DDD               4444 A                5600
    EEE               5555 A                6100
    FFF               6666 A                4600
    
      5 record(s) selected.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      TABLE1(POLICY_LOC , POLICY_NO , FILTER_PT , AM_COST) AS (
    VALUES
      ( 'AAA' , 1111 , 'A' ,  400 )  
    , ( 'BBB' , 2222 , 'I' ,  500 )
    , ( 'CCC' , 3333 , 'A' , 1700 )
    , ( 'DDD' , 4444 , 'A' , 5600 )
    , ( 'EEE' , 5555 , 'A' , 6100 )
    , ( 'FFF' , 6666 , 'A' , 4600 )
    , ( 'GGG' , 7777 , 'I' , 1600 )
    )
    SELECT 
           POLICY_LOC
         , POLICY_NO
         , filter_pT
         ,am_cost
    FROM TABLE1
    WHERE FILTER_PT <= /*:ws-filter*/ 'I';
    ------------------------------------------------------------------------------
    
    POLICY_LOC POLICY_NO   FILTER_PT AM_COST    
    ---------- ----------- --------- -----------
    AAA               1111 A                 400
    BBB               2222 I                 500
    CCC               3333 A                1700
    DDD               4444 A                5600
    EEE               5555 A                6100
    FFF               6666 A                4600
    GGG               7777 I                1600
    
      7 record(s) selected.
    Last edited by tonkuma; 04-10-12 at 19:08. Reason: Separate two sample codes and results. Change the name to "Lennart"

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Tonkuma-sun, is your result based on host variable :ws-filter ?

    Maybe
    WHERE FILTER_PT <= :ws-filter
    is better ?
    But in this case very depended on 'I' > 'A'


    Thanks, Lenny
    Last edited by Lenny77; 04-10-12 at 15:40.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the difference in query results between the value was supplied by a host variable or by a constant,
    if both values were same?

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post

    Quote Originally Posted by tonkuma View Post
    What are the difference in query results between the value was supplied by a host variable or by a constant,
    if both values were same?
    The host variable could change the value in runtime, but constant stays the same.

    For example the host variable could be selected from another table, or could be an input parameter of the SP, or function.

    Lenny

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My intention was to ask the difference of (1-1) and (1-2) and/or the difference of (2-1) and (2-2).

    I used constans for ease of testing.

    Case 1:
    Compare
    (1-1) WHERE FILTER_PT <= /*:ws-filter*/ 'A';
    with
    (1-2) WHERE FILTER_PT <= :ws-filter; /* where :WS-FILTER = A */

    Case 2:
    Compare
    (2-1) WHERE FILTER_PT <= /*:ws-filter*/ 'I';
    with
    (2-2) WHERE FILTER_PT <= :ws-filter; /* where :WS-FILTER = I */

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Lenny77 View Post
    ...

    Maybe is better ?
    But in this case very depended on 'I' > 'A'


    Thanks, Lenny
    But, what language code supposes 'I' <= 'A'?
    Both of 'I' and 'A' are alphabetic characters and 'I' > 'A', even in Japanese language code system.

    By the way,
    I'm not sure 'A' > 'a' or 'A' < 'a'.

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink Still Easy

    Quote Originally Posted by tonkuma View Post
    But, what language code supposes 'I' <= 'A'?
    Both of 'I' and 'A' are alphabetic characters and 'I' > 'A', even in Japanese language code system.

    By the way,
    I'm not sure 'A' > 'a' or 'A' < 'a'.
    'A' < 'a' (mainframe). I like your jokes.

    You want say you don't understand me.

    But it's easy to understand even for begginers:

    I just said: When we used
    Code:
    WHERE FILTER_PT <= :ws-filter
    we made solution for case 'I', 'A', only but if in future we want to change spec to shown all when 'I' and only 'A' when 'A' we have to change condition on opposite:
    Code:
    WHERE FILTER_PT >= :ws-filter
    .
    For me this kind of condition has no sense, because is out human of logic.

    How it will if when 'A' and 'Z' - shown all and when 'B' shown only with 'B' ? Case will work with this.

    Lenny
    Last edited by Lenny77; 04-11-12 at 12:18.

  14. #14
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Lenny77 View Post
    How it will if when 'A' and 'Z' - shown all and when 'B' shown only with 'B' ? Case will work with this.
    Lenny
    If the implicit ordering doesn't hold in general you have to invent one that you can use. CASE is one way of doing this, another one would be to create a separate ordering relation (table).


    /Lennart
    --
    Lennart

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation

    Quote Originally Posted by lelle12 View Post
    If the implicit ordering doesn't hold in general you have to invent one that you can use. CASE is one way of doing this, another one would be to create a separate ordering relation (table).


    /Lennart
    You are right. Creating the special table is the universal way to solve this problem.

    Lenny

Posting Permissions

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