Re: [Oracle] Sql request

From: Ben Finney <bignose+hates-spam_at_benfinney.id.au>
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 Finney
Received on Fri Nov 27 2009 - 17:12:43 CST

Original text of this message