SQL Looping Fun

From: Paul and Leslie Wasserman <mrpaulwass_at_hotmail.com>
Date: Wed, 16 Jan 2002 02:13:50 GMT
Message-ID: <yl518.341547$ez.49504081@news1.rdc1.nj.home.com>

Has anyone ever used a count_util table to do looping within a single SQL?  

I recently found this gem, and had a chance to use it to loop through a table n number of times in order to do some counts I needed.  

The basic idea is a cartesian of your data along with the count_util table.  

Create a table called count_util. The table only needs one column, called cnt. The cnt column should be a number column of course.  

Then insert values from zero to 1000 into the count_util table  

Next, write your SQL. Below is a sample. I'm writing this from memory, so don't shoot me if the syntax isn't perfect.  

SELECT DECODE(cnt,1,'COUNT MEN',
                  2,'COUNT WOMEN',
                  3,'COUNT CHILDREN AGE 2',
                  4,'COUNT SENIORS  AGE 70') FIELD_BEING_COUNTED
      ,COUNT(DECODE(cnt,1,DECODE(SEX,'M','1'),
                        2,DECODE(SEX,'W','1'),                            
                        3,DECODE(AGE,2,'1'),
                        4,DECODE(AGE,70,'1')
             )) MY_COUNTS

 FROM table_a, count_util
 WHERE TRUNC(last_purchased_date) > SYSDATE - 30

    AND cnt BETWEEN 1 AND 4                                                 

This will output something like  

FIELD_BEING_COUNTED        MY_COUNTS
    COUNT MEN                   40
    COUNT WOMEN                 39
    COUNT CHILDREN AGE 2        14
    COUNT SENIORS  AGE 70       26
 

You can even use the count_util more than once to create multiple loops. Just be careful when doing this with very large select statements. If your cnt is up to 4 (as shown above), and you have 8000 rows which match your where clause, your SQL will scan 32000 rows in all. Still, Oracle runs pretty quick for these types of sqls. I've been running through about 500,000 rows in Oracle 8i in under 30 seconds. This is a good bank for the buck!  

Email me at mrpaulwass_at_hotmail.com if you like it or need to see another sample. I'll do what I can.  

Good luck.

-- 
Paul 

Received on Tue Jan 15 2002 - 20:13:50 CST