I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:

SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;

The “100” value can be replaced with whatever number you need.

So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:

• Print multiple labels for a single row based on the quantity value in that row.
• Splitting some orders into multiple records for shared commissions.
• Creating a series of dates from a single date record.
• Separating a multi-value field into separate single value records.
• To repeat all records several times, grouped differently each time.

If you have a task like this and want some help, give me a call.