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

    Unanswered: Case in where clause

    Hi;

    DB2 v9.1 z/os

    Code:
    SELECT DISTINCT                     
              
        t1.POLICY_LOC
      , t1.POLICY_NO
      , T1.POLICY_VAL
      , T2.POLICY_CAP   
      , t2.RECV_DT
     
                  
    fROM                               
        (SELECT  *
    
     FROM  
           TABLE1      T1       
          WHERE         
                      T1.POLICY_VAL <>0)T1           
     
     LEFT OUTER JOIN                                     
      (SELECT 
           t2.POLICY_LOC
         , t2.POLICY_NO
         , T2.POLICY_CAP 
          , t2.RECV_DT                                    
         ,ROW_NUMBER()                                   
          OVER (PARTITION BY                             
             t2.POLICY_LOC
         , t2.POLICY_NO
         
         , QUARTER(t2.RECV_DT)                  
         , YEAR ( t2.RECV_DT)                         
              ORDER BY t2.RECV_DT DESC NULLS LAST) AS RN
       ON  T2.POLICY_LOC = T1.POLICY_LOC
       AND T2.POLICY_NO  = T1.POLICY_NO
      AND  T1.CALE_YEAR=CHAR(YEAR(T2.RECV_DT)) 
      AND RN=1                                              
                 
      WHERE                                                 
          T1.CALE_YEAR ='2012'
    The above query will perform based on the :ws-order value

    if the :ws-order ='A' then query has to return all the rows

    if the :ws-order ='B' then query has to return only the T2.POLICY_CAP > 0 rows

    if the :ws-order ='c' then query has to return only the T2.POLICY_CAP <= 0 rows

    if the :ws-order ='d' then query has to return only
    when the T1.POLICY_VAL< T2.POLICY_CAP rows

    if the :ws-order ='e' then query has to return only
    when the T1.POLICY_VAL > T2.POLICY_CAP rows
    Code:
    TABLE1
    
    POLICY_LOC POLICY_NO      POLICY_VAL   CALE_YEAR        
    
    AAA        1234            2500          2012 
    BBB        3333            5000          2012  
    CCC        4444            0500          2012
    DDD        5555            2400          2012
    EEE        1111            5000          2013
    
    TABLE2
    
    POLICY_LOC  POLICY_NO          POLICY_CAP RECV_DT        
    
    AAA         1234                1000      2012-03-13  
    BBB         3333                2000      2012-04-12 
    CCC         4444                0700      2012-04-11
    DDD         5555                 0        2012-05-10
    EEE         1111                4550      2013-01-15
    Code:
    Expected result ( if :ws-order ='A' means)
    
    POLICY_LOC   POLICY_NO   POLICY_VAL POLICY_CAP       RECV_DT        
    
    AAA         1234         2500        1000           2012-03-13  
    BBB         3333         5000        2000           2012-04-12 
    CCC         4444         0500        0700           2012-04-22
    DDD         5555         2400        0              2012-05-10
    
    
    Expected result ( if :ws-order ='B' means)
    
    POLICY_LOC   POLICY_NO   POLICY_VAL POLICY_CAP       RECV_DT        
    
    AAA         1234         2500        1000           2012-03-13  
    BBB         3333         5000        2000           2012-04-12 
    CCC         4444         0500        0700           2012-04-22
    
    
    Expected result ( if :ws-order ='C' means)
    
    POLICY_LOC   POLICY_NO   POLICY_VAL POLICY_CAP       RECV_DT        
    
    DDD         5555         2400        0              2012-05-10
    
    
    Expected result ( if :ws-order ='D' means)
    
    POLICY_LOC   POLICY_NO   POLICY_VAL POLICY_CAP       RECV_DT        
    
    
    CCC         4444         0500        0700           2012-04-22
    
    Expected result ( if :ws-order ='E' means)
    
    POLICY_LOC   POLICY_NO   POLICY_VAL POLICY_CAP       RECV_DT        
    
    
    AAA         1234         2500        1000           2012-03-13  
    BBB         3333         5000        2000           2012-04-12
    PLEASE HELP ON how to implement the logic in the WHERE clause..

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "If <cond-1> Then <cond-2>"
    would be replaced by
    "<cond-1> AND <cond-2>"

    "If <cond-1> Then <cond-2> Else If <cond-3> Then <cond-4>"
    would be replaced by
    "<cond-1> AND <cond-2> OR <cond-3> AND <cond-4>"

    so on...

    Then try the query by adding the following conditions to the outmost(final) WHERE condition.
    Code:
       AND
      (    :ws-order = 'A'
       OR  :ws-order = 'B'
       AND T2.POLICY_CAP >  0
       OR  :ws-order = 'C'
       AND T2.POLICY_CAP <= 0
       OR  :ws-order = 'D'
       AND T1.POLICY_VAL < T2.POLICY_CAP
       OR  :ws-order = 'E'
       AND T1.POLICY_VAL > T2.POLICY_CAP
      )
    Anyway, why POLICY_LOC = 'DDD' was not in "Expected result ( if :ws-order ='E' means)"?

Posting Permissions

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