Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> SQL Looping Fun
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
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.
-- PaulReceived on Tue Jan 15 2002 - 20:13:50 CST