Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First create sample data:</p> <pre><code>create table my_table (product_id number , quantity number); insert into my_table(product_id, quantity) values(1,3); insert into my_table(product_id, quantity) values(2,5); </code></pre> <p>And now run this SQL:</p> <pre><code> SELECT product_id, quantity FROM my_table tproducts ,( SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL &lt;= (SELECT MAX(quantity) FROM my_table)) tbl_sub WHERE tbl_sub.lvl BETWEEN 1 AND tproducts.quantity ORDER BY product_id, lvl; PRODUCT_ID QUANTITY ---------- ---------- 1 3 1 3 1 3 2 5 2 5 2 5 2 5 2 5 </code></pre> <p>This question is propably same as this: <a href="https://stackoverflow.com/questions/3584028/how-to-calc-ranges-in-oracle/3584813">how to calc ranges in oracle</a></p> <p><strong>Update solution, for Oracle 9i</strong>:</p> <p>You can use pipelined_function() like this:</p> <pre><code>CREATE TYPE SampleType AS OBJECT ( product_id number, quantity varchar2(2000) ) / CREATE TYPE SampleTypeSet AS TABLE OF SampleType / CREATE OR REPLACE FUNCTION GET_DATA RETURN SampleTypeSet PIPELINED IS l_one_row SampleType := SampleType(NULL, NULL); BEGIN FOR cur_data IN (SELECT product_id, quantity FROM my_table ORDER BY product_id) LOOP FOR i IN 1..cur_data.quantity LOOP l_one_row.product_id := cur_data.product_id; l_one_row.quantity := cur_data.quantity; PIPE ROW(l_one_row); END LOOP; END LOOP; RETURN; END GET_DATA; / </code></pre> <p>Now you can do this:</p> <pre><code>SELECT * FROM TABLE(GET_DATA()); </code></pre> <p>Or this:</p> <pre><code>CREATE OR REPLACE VIEW VIEW_ALL_DATA AS SELECT * FROM TABLE(GET_DATA()); SELECT * FROM VIEW_ALL_DATA; </code></pre> <p>Both with same results.</p> <p>(Based on my article <a href="http://martin-mares.cz/2010/08/oracle-db-pipelined-function/" rel="nofollow noreferrer" title="pipelined function">pipelined function</a>)</p>
    singulars
    1. This table or related slice is empty.
    1. POHow can I return multiple identical rows based on a quantity field in the row itself?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USMartin Mares
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COdo you use 11g? I was never able to run subquery in `connect by level` clause in 9i.
      singulars
      1. PO
      1. USbe here now
    2. COyou are right, on Oracle 9i raise error: ORA-01473. I'll write a better solution for Oracle 9i... wait i minute, I'll update my "answer" :)
      singulars
      1. PO
      1. USMartin Mares
    3. COnow answer is updated for Oracle 9i solution.
      singulars
      1. PO
      1. USMartin Mares
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload