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.