I have a problem whereby we have an order line interface into an
external system.

This external system doesn't understand the concept of quantity > 1, so
if I wanted to order 2 widgets and send it to that system, I need to
send two lines of a widget of quantity 1. The internal system has a
quantity field.

I can easily write a stored procedure to take care of this, and
iteratively go through a loop cursor and send duplicate lines, however
the system which is sending the order cannot have stored procedures, it
can only use SQL.

I can undertake a project to either 1) Make the sending system take
stored procedures, or 2) Make the receiving system take quantities.

Before I do these larger projects, I was wondering if anyone knew of a
way which would accomplish this in straight sql.

My example is as follows:

Send an order for 2 widgets to the interface:

select part_number, price, quantity
from order_line_table
where order_number = 12345


part_number price quantity
123-456 1.00 2


The SQL should be able to return:

part_number price
123-456 1.00
123-456 1.00


Any help would be greatly appreciated!

Eric Levinson
Turin Networks, Inc.

Oracle LazyDBA home page