Results 1 to 4 of 4

Thread: error in row()

  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: error in row()

    Hi;

    Please find the below query .it is not returning the row for the below datas

    Code:
    SELECT                                              
                 T1.PROD                                     
                ,T1.PROD_NO                                 
                                    
             ,T1.INV_DT_IN                        
             ,T1.INV_DT_OUT                       
             , ROW_NUMBER()    
                             
                     OVER(PARTITION BY                  
                      T1.PROD                                     
                     ,T1.PROD_NO        
                      ORDER BY                          
         T1.INV_DT_IN DESC NULLS LAST) AS RN1    
             , ROW_NUMBER() 
    
                     OVER(PARTITION BY                  
                      T1.PROD                                     
                     ,T1.PROD_NO         
                      ORDER BY                          
         T1.INV_DT_OUT DESC NULLS LAST) AS RN2  
    
    
    
              FROM TABLE1 T1        
                  
       WHERE                                                
              T1.PROD  ='AAA'                                   
         AND T1.PROD_NO  =234
    AND RN1=1
    AND RN2=1
    Code:
    TABLE1
    
    PROD   PROD_NO INV_DT_IN    INV_DT_OUT
    AAA    234     06-02-2007   02-04-2008
    AAA    234     06-02-2007   02-04-2008
    AAA    234     10-03-2007   22-12-2007
    Code:
    EXPECTED OUTPUT
    
    PROD   PROD_NO INV_DT_IN    INV_DT_OUT
    AAA    234     10-03-2007    02-04-2008
    Please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry! You wanted largest INV_DT_IN.

    Your expeced output took largest INV_DT_IN and largest INV_DT_OUT in different rows.
    So, your way might not work.

    Another issue might be in that OLAP specifications are applied to the result rows of a SELECT clause.
    So, use the returned values of OLAP specifications in WHERE clause of same subselect is usually meaningless.
    Last edited by tonkuma; 06-27-12 at 12:52.

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the suggestion..But i need the MAX INV_DT_IN and INV_DT_out with same row..
    Please give the guidelines how to perform the query..

    DB2V9.1 z/os

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    TABLE1
    
    PROD   PROD_NO INV_DT_IN    INV_DT_OUT
    AAA    234     06-02-2007   02-04-2008
    AAA    234     06-02-2007   02-04-2008
    AAA    234     10-03-2007   22-12-2007
    an idea is to use GROUP BY, MAX(INV_DT_IN) and MAX(INV_DT_OUT)

    or, you selected a pair of (PROD , PROD_NO), like...
    Code:
       WHERE                                                
              T1.PROD  ='AAA'                                   
         AND T1.PROD_NO  =234
    then another idea is to use MAX(PROD), MAX(PROD_NO), MAX(INV_DT_IN) and MAX(INV_DT_OUT) without explicit GROUP BY clause.

Posting Permissions

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