If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > select query filter

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 179
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 310
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 179
Thanks to all, i tried Chandan example and working fine
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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);
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On