Re: Converting one record into many

From: Thomas Kyte <thomas.kyte_at_oracle.com>
Date: 8 Apr 2005 06:54:08 -0700
Message-ID: <122968448.0000dc6a.043@drn.newsguy.com>

In article <d34cfv$2it$1_at_news.ya.com>, Cecilio Peral says...
>
>Hi all
>
>I have a table with Receipt lines for Purchase orders with the following
>structure:
>
>PO_ID NUMBER;
>PO_LINE_ID NUMBER;
>ITEM_ID NUMBER;
>LOC_ID NUMBER;
>QUANTITY NUMBER;
>
>In order to print labels for all the items received I need to convert every
>record in this table
>in as many record indicates.
>
>For instance:
>
>PO_ID PO_LINE_ID ITEM_ID LOC_ID QUANTITY
>12 156 9098888 65
>3
>
>I need to have:
>
>ITEM_ID LOC_ID
>9098888 65
>9098888 65
>9098888 65
>
>Of course there is a possibility using a temporary table but I wander if
>some of you have a way to to that
>without an intermediate table, just SQL code.
>
>Thanks in advance for your help
>
>Cecilio
>
>

No versions....
You need a table with N rows in it, where N >= max(quantity) in the PO table. then join.

In 9i you can do this (no extra table needed)

ops$tkyte_at_ORA9IR2> with
  2 row_generator
  3 as
  4 (select level r from dual connect by level < 100 )   5 select *
  6 from po, row_generator
  7 where po.quantity >= row_generator.r;

ITEM_ID LOC_ID QUANTITY R ---------- ---------- ---------- ----------

         1          1          3          1
         2          2          4          1
         1          1          3          2
         2          2          4          2
         1          1          3          3
         2          2          4          3
         2          2          4          4

7 rows selected.

ops$tkyte_at_ORA9IR2> create or replace type numTab as table of number 2 /
Type created.

ops$tkyte_at_ORA9IR2> create or replace function row_generator( p_num in number ) return numTab
  2 PIPELINED
  3 as
  4 begin

  5          for i in 1 .. p_num
  6          loop
  7                  pipe row(i);
  8          end loop;
  9          return;

10 end;
11 /

Function created.

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> select *
2 from po, table( row_generator(po.quantity) ) 3 /

ITEM_ID LOC_ID QUANTITY COLUMN_VALUE ---------- ---------- ---------- ------------

         1          1          3            1
         1          1          3            2
         1          1          3            3
         2          2          4            1
         2          2          4            2
         2          2          4            3
         2          2          4            4

7 rows selected.

ops$tkyte_at_ORA9IR2>

Otherwise, you can just use something like:

...., (select rownum r from some_big_table where rownum <= MAX ) row_generator

in any release to join to.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Fri Apr 08 2005 - 08:54:08 CDT