Re: SQL Question: duplicating Records

On Fri, 3 Mar 2000 14:02:34 -0500, "Steven C. Kondolf" <stevekondolf_at_hatespam.rochestermidland.com> wrote:

>Hi All -
>
>I have been asked to generate labels for products in our inventory based on
>the number of containers in stock. For example, if I have 8 pails of
>product A, I need 8 labels. 25 bbls of B, 25 labels, etc. My plan is to
>somehow generate duplicate records, one for each package for each item in
>our inventory. So product A would have 8 duplicated records, B would have
>25, etc. I can then feed this into Oracle Reports and replicate the labels.
>
>I have tables with the product information and another with the inventory
>information. Is there a way thru sql to accomplish this or do I have to
>write some sort of plsql script to do this. My thought was to create a
>temporary table and write some sort of plsql loop for each item and insert
>into the temp table during each loop.
>
>Is there an easier way??? I'm pretty new to writing sql code. Thanks.

Try creating an table with integers from 1 to the maximum on-hand value plus some extra. Say items_of containing a column item_of.

A query like:

SELECT p.product_id
,io.item_of
,i.on_hand
FROM products p
,items_of io
,inventory i
WHERE p.product_id = i.product_id
AND i.on_hand >= io.items_of
ORDER BY product_id, items_of
/

Should give you the right number of records for each product. Received on Sat Mar 04 2000 - 01:48:00 CET