Reader Feedback and Insights: He Likes Common Table Expressions

Hey, Ted:

Your article "Retrieve a Representative of a Group" is cool stuff! I recently had a situation in which common table expressions were great assets.

We have a menu system that logs menu entries, which means that each entry can appear many times.

The file structure:
AFUSRC     User                 
AFSYSC     System               

I was asked to produce a report showing the most used system per user. I ended up with this:

WITH
   EXP1 ( AFUSRC1, AFSYSC1, COUNT1 ) AS

     (SELECT AFUSRC, AFSYSC,COUNT(*) AS COUNT
        FROM GER031QA GROUP BY AFSYSC,AFUSRC),

   EXP2 (AFUSRC2,COUNT2) AS
     (SELECT  AFUSRC1,MAX(COUNT1)
        FROM EXP1 GROUP BY AFUSRC1 ) 

SELECT AFUSRC,AFSYSC, COUNT(*) AS COUNT 
  FROM GER031QA
 GROUP BY AFSYSC,AFUSRC 
HAVING COUNT(*) IN 
     (SELECT COUNT2
        FROM EXP2
       WHERE EXP2.AFUSRC2 = AFUSRC)
ORDER BY AFUSRC

The nice thing about common table expressions is that each table can serve as input for the next step.

Keep doing what you do!!!

--Eitan

Thanks, Eitan. Table expressions may be my favorite of all the newer capabilities of DB2/400 UDB. I hope the tips we've run have helped many readers of Midrange Guru learn how to use them.

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

E-Mail Report and File Distribution for the iSeries & AS/400 - ESEND - - Saves paper and shipping costs - - Eliminates report distribution hassles - - Improves information access - - Archives important information - - Saves YOUR time - Get a Free Trial today! Read More