Inflation table query for Oracle

Thursday 25 March 2021 @ 3:53 pm

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.









Leave a Reply

Recrystallize Pro