Re: [Oracle] Sql request
Date: Sat, 28 Nov 2009 10:12:43 +1100
Message-ID: <87skbzwoic.fsf_at_benfinney.id.au>
Pif <nospam_at_laposte.net> writes:
> here is a short description of the schema :
I don't use Oracle, but your question doesn't seem to have anything Oracle-specific. I'll answer using PostgreSQL.
> - I've orders with an amount
pif_orders=> CREATE TABLE stock_order (
code VARCHAR NOT NULL, amount NUMERIC(10,2) NOT NULL, PRIMARY KEY (code) ); pif_orders=> INSERT INTO stock_order (code, amount) VALUES ('A', 100.00), ('B', 200.00), ('C', 120.00);
> - An order can be associated with several deliveries, each one having
> also an amount (a delivery has a FK referencing the order).
pif_orders=> CREATE TABLE stock_delivery (
order_code VARCHAR NOT NULL REFERENCES stock_order (code), sequence INTEGER NOT NULL, amount NUMERIC(10,2), PRIMARY KEY (order_code, sequence) ); pif_orders=> INSERT INTO stock_delivery (order_code, sequence, amount) VALUES ('A', 1, 50.00), ('A', 2, 30.00), ('A', 3, 20.00), ('B', 1, 200.00), ('C', 1, 70.00), ('C', 2, 50.00);
> I would like to produce following result that joins both tables
> without repeating order amounts :
>
> Order A $100 Develiery1 $50
> Develiery2 $30
> Develiery3 $20
> Order B $200 Develiery4 $200
> Order C $120 Develiery5 $70
> Develiery6 $50
That's not a relation (some tuples are incomplete), so it's not something you should expect the relational DBMS to produce. Sometimes a particular DBMS product might have something that can be distorted to do that kind of thing, but it will always be limited and usually lead to frustration since it's not the role of the DBMS to produce generic reports.
Instead, use a ‘SELECT’ to produce the relation:
pif_orders=> SELECT DISTINCT
order_code, order_amount, delivery_sequence, delivery_amount FROM ( SELECT code AS order_code, amount AS order_amount FROM stock_order ) AS o NATURAL JOIN ( SELECT order_code, sequence AS delivery_sequence, amount AS delivery_amount FROM stock_delivery ) AS d
ORDER BY order_code, delivery_sequence;
order_code | order_amount | delivery_sequence | delivery_amount
------------+--------------+-------------------+----------------- A | 100.00 | 1 | 50.00 A | 100.00 | 2 | 30.00 A | 100.00 | 3 | 20.00 B | 200.00 | 1 | 200.00 C | 120.00 | 1 | 70.00 C | 120.00 | 2 | 50.00(6 rows)
and then use a function or user application to process the relation into whatever output text you like for the report. Turning relations into reports isn't the job of the DBMS.
-- \ “It's up to the masses to distribute [music] however they want | `\ … The laws don't matter at that point. People sharing music in | _o__) their bedrooms is the new radio.” —Neil Young, 2008-05-06 | Ben FinneyReceived on Fri Nov 27 2009 - 17:12:43 CST