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

    select query filter

    Hi

    DB2 v9.5 z/os

    Please find the all scenario and guide me to write a Select query to filter the records.

    cond:
    1. eliminate all records which VOL_COUNT will have ZERO values
    2.Dispaly one record if all records having VOL_COUNT as ZERO values
    3. by all the times, the record which is having P.LOC as 'XXX' should be elimated if the NUMBER (NO_FIRST,NO_MID,NO_LAST)
    having more than one occurence

    4.Dispaly if the number(NO_FIRST,NO_MID,NO_LAST) having the only one record like P_LOC as 'XXX'
    Code:
    SELECT  
    
     T1.P_LOC                             
    ,T1.NO_FIRST                             
    ,T1.NO_MID                           
    ,T1.NO_LAST 
    ,T1.VOL_COUNT 
    
    
    FROM BASE_TABLE T1 
    
    
    WHERE 
    
        T1.NO_FIRST  = '111'                          
    AND T1.NO_MID    = '222'                        
    AND T1.NO_LAST   ='333'

    Code:
    BASE_TABLE
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    A11            111        222      333         1200
    B11            111        222      333         0
    C11            111        222      333         0
    XXX            111        222      333         0
    
    EXPECTED OUTPUT
    
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    A11            111        222      333         1200
    
    
    
    BASE_TABLE
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    A11            111        222      333         0
    B11            111        222      333         0
    C11            111        222      333         0
    XXX            111        222      333         0
    
    EXPECTED OUTPUT
    
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    A11            111        222      333         0
    
    
    BASE_TABLE
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    A11            111        222      333         1200
    B11            111        222      333         1300
    C11            111        222      333         1400
    XXX            111        222      333         0
    
    EXPECTED OUTPUT
    
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    A11            111        222      333         1200
    B11            111        222      333         1300
    C11            111        222      333         1400
    
    BASE_TABLE
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    XXX            111        222      333         0
    
    EXPECTED OUTPUT
    
    P.LOC        NO_FIRST    NO_MID   NO_LAST    VOL_COUNT
    
    XXX            111        222      333         0
    Thanks

  2. #2
    Join Date
    Oct 2011
    Posts
    1
    Hi,

    You can try this below SQL using UNION.

    This is not tested one so you may need tweak around WHERE clauses

    Kindly let us know if there is something missing in this query or if it works for you

    Code:
    (SELECT  
     T1.P_LOC                             
    ,T1.NO_FIRST                             
    ,T1.NO_MID                           
    ,T1.NO_LAST 
    ,T1.VOL_COUNT 
    FROM BASE_TABLE T1 
    WHERE 
        T1.NO_FIRST  = '111'                          
    AND T1.NO_MID    = '222'                        
    AND T1.NO_LAST   ='333'
    AND T1.VOL_COUNT > 0
    AND T1.P_LOC <> 'xxx'
    AND (SELECT COUNT(*) FROM
    	FROM BASE_TABLE T2
    	WHERE T2.NO_FIRST  = '111'                          
    	  AND T2.NO_MID    = '222'                        
    	  AND T2.NO_LAST   ='333'
                   AND T2.P_LOC <> 'xxx'
    	  AND T2.VOL_COUNT > 0 ) > 0)
    UNTION
    (SELECT  
     T1.P_LOC                             
    ,T1.NO_FIRST                             
    ,T1.NO_MID                           
    ,T1.NO_LAST 
    ,T1.VOL_COUNT 
    FROM BASE_TABLE T1 
    WHERE 
        T1.NO_FIRST  = '111'                          
    AND T1.NO_MID    = '222'                        
    AND T1.NO_LAST   ='333'
    AND T1.P_LOC <> 'xxx'
    AND (SELECT COUNT(*) FROM
    	FROM BASE_TABLE T2
    	WHERE T2.NO_FIRST  = '111'                          
    	  AND T2.NO_MID    = '222'                        
    	  AND T2.NO_LAST   ='333'
    	  AND T2.P_LOC <> 'xxx'	
    	  AND T2.VOL_COUNT > 0 ) = 0
    FETCH FIRST 1 ROWS ONLY)
    UNTION
    (SELECT  
     T1.P_LOC                             
    ,T1.NO_FIRST                             
    ,T1.NO_MID                           
    ,T1.NO_LAST 
    ,T1.VOL_COUNT 
    FROM BASE_TABLE T1 
    WHERE 
        T1.NO_FIRST  = '111'                          
    AND T1.NO_MID    = '222'                        
    AND T1.NO_LAST   ='333'
    AND T1.P_LOC = 'xxx'
    AND (SELECT COUNT(*) FROM
    	FROM BASE_TABLE T2
    	WHERE T2.NO_FIRST  = '111'                          
    	  AND T2.NO_MID    = '222'                        
    	  AND T2.NO_LAST   ='333') = 1);
    Regards,
    Chandan
    Last edited by chandanyadav; 12-04-12 at 00:47.

  3. #3
    Join Date
    Nov 2011
    Posts
    317
    I think the rule can be simplified to this:
    1, there must be one record for each partition (NO_FIRST,NO_MID,NO_LAST)
    2, if sum of vol_count is 0 for each partition , then pick one record.
    else eliminate the record with 'loc XXX' and 'vol_count 0'

    So, plz try the following sql
    Code:
    select loc,no_first,no_mid,no_last,vol_count 
        from (
          select t.*
                ,min(loc) over ( partition by no_first,no_mid,no_last ) as min_loc
                ,count(*) over ( partition by no_first,no_mid,no_last ) as cnt
                ,sum(VOL_COUNT) over ( partition by no_first,no_mid,no_last ) as tot
            from t )
      where ( cnt = 1 ) 
         or ( tot = 0 and min_loc = loc )
         or ( loc <> 'XXX' and vol_count <> 0 )
      order by no_first, loc;
    if db2 for z/os does not support olap function ,you can try this
    Code:
    select  loc,no_first,no_mid,no_last,vol_count
         from (
          select t.loc,t.no_first,t.no_mid,t.no_last,t.vol_count
                 ,t2.min_loc,t2.cnt,t2.tot
            from t,
               ( select no_first,no_mid,no_last
                        ,min(loc) as min_loc
                        ,count(*) as cnt
                        ,sum(VOL_COUNT) as tot
                   from t
                  group by no_first,no_mid,no_last ) t2
           where t.no_first = t2.no_first
             and t.no_mid   = t2.no_mid
             and t.no_last  = t2.no_last  )
      where ( cnt = 1 ) 
         or ( tot = 0 and min_loc = loc )
         or ( loc <> 'XXX' and vol_count <> 0 )
      order by no_first, loc;
    and order by clause is not necessary.

  4. #4
    Join Date
    Sep 2011
    Posts
    207
    Thanks to all, i tried Chandan example and working fine

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    To make it little bit nicer, you can change:

    ...WHERE
    T1.NO_FIRST = '111'
    AND T1.NO_MID = '222'
    AND T1.NO_LAST ='333'
    AND T1.P_LOC = 'xxx'
    AND (SELECT COUNT(*) FROM
    FROM BASE_TABLE T2
    WHERE T2.NO_FIRST = T1.NO_FIRST
    AND T2.NO_MID = T1.NO_MID
    AND T2.NO_LAST = T1.NO_LAST) = 1);

Posting Permissions

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