SQL Query - Z/OS DB2 QMF

[Rick_McGee]

I inherited a query that needs modifying. The query seems standard with a 'Select field1, field2 from TABLE_A where blah, blah, blah....'

Then it has the following statements as a part of the query:

HAVING (SUM(CASE                                          
           WHEN S.LVLP1 NOT IN ('A','B','C','D')  THEN 1  
           ELSE 0 END)                                    
     + SUM(CASE                                           
           WHEN S.LVLP1 IN('A','B','C','D') THEN 1        
           ELSE 0 END)                                    
     ¬= SUM(CASE                                          
           WHEN S.LVLP1 NOT IN('A','B','C','D') THEN 1    
           ELSE 0 END))                                   
AND SUM(CASE                                              
       WHEN S.LVLP1 IN('A','B','C','D') THEN 1            
       ELSE 0 END) > 0

I cannot understand what the SUM(CASE) statements are doing. They seem redundant, but it's a query that's been used for sometime. Can someone please shed some light on what these statements are accomplishing? Thank you.

 

[fredericofonseca]

That is a standard having clause for a group by select.

but what that is doing is.

based on the group clause
block 1
case 1 - sum 1 for all records where s.lvlp1 is not in a,b,c,d
case 2 - sum 1 for all records where lvlp1 in a,b,c,d
not equal
case 3 - sum 1 for all records where s.lvlp1 is not in a,b,c,d

block 2
case 4 - sum 1 for all records where lvlp1 in a,b,c,d
greater 0 (zero)

so only the records that match this criteria after the group by clause is applied will be selected.

but unless I am misreading the above is a bit redundant.

a + b <> a is only true if b > 0
but having clause is filtering for cases where b > 0 so the first block is always true hence could be removed



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886