select query filter

Billa007 · #1 (**permalink**) 12-03-12, 11:01

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

chandanyadav · #2 (**permalink**) 12-04-12, 00:43

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

fengsun2 · #3 (**permalink**) 12-04-12, 04:46

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.

Billa007 · #4 (**permalink**) 12-04-12, 09:34

Thanks to all, i tried Chandan example and working fine

Lenny77 · #5 (**permalink**) 12-04-12, 18:19

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);

Internet Services

Web Development

Digital Marketing

Consumer Tech