×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Query - Z/OS DB2 QMF

SQL Query - Z/OS DB2 QMF

SQL Query - Z/OS DB2 QMF

(OP)
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:


CODE -->

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.

RE: SQL Query - Z/OS DB2 QMF

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close