Re: Sql request
Date: Mon, 30 Nov 2009 13:26:34 -0800 (PST)
Message-ID: <6816f73c-c337-4e53-8e8c-2924ce1f07d5_at_j14g2000yqm.googlegroups.com>
On Nov 27, 6:12 pm, Ben Finney <bignose+hates-s..._at_benfinney.id.au> wrote:
> Pif <nos..._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 Finney
Yet it does have something Oracle-specfic: the ability, through SQL*Plus, to suppress repeating output, and analytic functions to compute the sum in-line so the stock_order table isn't necessary (columns are renamed, as necessary, to remove reserved words):
break on order_cde on ttl_ord_amt
select 'Order '||order_code as order_cde.
sum(delivery_amount) over (partition by order_code order by order_seq) as ttl_ord_amt,
'Delivery '||delivery_seq as delivery_seq,
'$'||amount
from stock_delivery
order by 1, 3;
Of course the stock_order table can be used:
break on order_cde on ttl_ord_amt
select 'Order '||d.order_code as order_cde.
o.amoount as ttl_ord_amt,
'Delivery '||d.delivery_seq as delivery_seq,
'$'||d.amount
from stock_delivery d join stock_order o on (d.order_code = o.code) order by 1, 3;
There is nothing wrong with the relation as all 'tuples' are complete; you're confusing display magic with missing data and there are no missing values in that result set, simply suppressed output courtesy of SQL*Plus.
David Fitzjarrell Received on Mon Nov 30 2009 - 15:26:34 CST