Hello,

here is a short description of the schema :
- I've orders with an amount
- An order can be associated with several deliveries, each one having
also an amount (a delivery has a FK referencing the order).

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
.....


I would like to find a select request without using PL/SQL procedure if
it is possible.


Thanks a lot for your help.

Pif <nospam (AT) laposte (DOT) net> writes:

I don't use Oracle, but your question doesn't seem to have anything
Oracle-specific. I'll answer using PostgreSQL.

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);

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);

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

Ben Finney wrote on 28.11.2009 00:12:
This can be done a lot easier including the surpressing of repeating values
(works in Postgres and Oracle):

select case
when lag(o.code) over (partition by o.code order by d.sequence) = o.code then ''
else o.code
end,
case
when lag(o.code) over (partition by o.code order by d.sequence) = o.code then null
else o.amount
end as order_amount,
d.sequence as delivery_sequence,
d.amount as delivery_amount
from stock_order o
join stock_delivery d on o.code = d.order_code
order by o.code, d.sequence;

And even if the "blank" stuff should be in there I don't understand the complicated sub-query you were using

Thomas

Thomas Kellerer <OTPXDAJCSJVU (AT) spammotel (DOT) com> writes:

For didactic purposes. I prefer to have attribute names that make sense
in the context of the relation; and then use attribute renames in the
select so that a natural join will just work. Join conditions give me
hives; every join should be natural anyway :-)

--
\ “I fly Air Bizarre. You buy a combination one-way round-trip |
`\ ticket. Leave any Monday, and they bring you back the previous |
_o__) Friday. That way you still have the weekend.” —Steven Wright |
Ben Finney

Ben Finney wrote on 28.11.2009 01:36:
I agree

Hmm, natural joins give _me_ the creeps.

They are not relying on FK relations but on naming conventions. Now if I have a column ID in table product and that is referenced by a product_id in another table, the natural join would yield a cartesian join...

Ben Finney <bignose+hates-spam (AT) benfinney (DOT) id.au> writes:

Hmm. Interesting assertion. Why avoid things like outer joins?

I've recently been working on improving the performance of an
application and by far, the most beneficial performance gains I've made
has been from replacing really inefficient sub-queiries with a much
simpler and more efficient outer join.

From experience, I know that people seem to have problems with things
like outer joins, but I've never really understood what the issue is. I
could understand some confusion with older versions of Oracle that had
its own oracle method of defining outer joins, but now that it is more
standards compliant in its join syntax, I don't see what the problem
is. I also know you can get into trouble when working with different
RDMS as they can behave differently depending on how the join is
defined, which can be a little frustrating, but provided adequate
testing, is not a huge issue.

However, I find it odd that people will use a more complex and usually
inefficient sub-query rather than a simpler join. Am I missing something
here?

Tim





--
tcross (at) rapttech dot com dot au

Thomas Kellerer <OTPXDAJCSJVU (AT) spammotel (DOT) com> writes:

Right, foreign key relations are database constraints, not join
conditions. You can't rely on a foreign key anyway, you need to specify
it either with an explicit join condition or (as I've done) an explicit
name for a natural join.

Note that I'm explicitly using distinct attribute names in the select
operation, regardless of what the attributes are named in the base
relation. That also ensures that all the names in the ‘SELECT’ clause,
the ‘WHERE’ clause, et cetera are all unique without further
qualification.

That is, instead of:

SELECT DISTINCT
stock_order.code,
stock_item.code,
stock_item.name,
stock_order.amount,
stock_delivery.code,
stock_delivery.amount
FROM
stock_order
INNER JOIN stock_delivery
ON stock_order.code = stock_delivery.order_code
INNER JOIN stock_item
ON stock_order.item_code = stock_item.code
INNER JOIN stock_category
ON stock_item.category_code = stock_category.code
WHERE
stock_order.amount > 100
AND stock_category.name = 'widget'

I'm using names that are both shorter and don't need to be qualified
further:

SELECT DISTINCT
order_code,
item_code,
item_name,
order_amount,
delivery_code,
delivery_amount
FROM
(
SELECT DISTINCT
code AS order_code,
item_code,
amount AS order_amount
FROM stock_order
) AS o
NATURAL JOIN (
SELECT DISTINCT
code AS delivery_code,
amount AS delivery_amount
FROM stock_delivery
) AS d
NATURAL JOIN (
SELECT DISTINCT
code AS item_code,
category_code,
name AS item_name
FROM stock_item
) AS i
NATURAL JOIN (
SELECT DISTINCT
code AS category_code,
name AS category_name
FROM stock_category
) AS c
WHERE
order_amount > 100
AND category_name = 'widget'

I find that both more explicit, less repetitious, and much clearer, than
a bunch of table qualifications and join conditions; especially since
the same technique continues to work for arbitrarily complex
sub-selects.

--
\ “Pray, v. To ask that the laws of the universe be annulled in |
`\ behalf of a single petitioner confessedly unworthy.” —Ambrose |
_o__) Bierce, _The Devil's Dictionary_, 1906 |
Ben Finney

Tim X <timx (AT) nospam (DOT) dev.null> writes:

Because NULL in query results makes them non-relational.

Well, you're the one with that evidence and I don't have it to examine.
I won't deny that you've experienced what you claim.

But I would say two things about that: either the sub-selects *could* be
written a whole lot more efficiently that they were, before resorting to
outer joins; or the DBMS product isn't doing its job of making the best
execution plan from a declarative query.

I find natural joins with distinctly-named attributes from sub-selects
much clearer, not least because they are more explicit. There's no need
to visualise the different styles of join; the rules for a natural join
are clear.

As for efficiency: If two query expressions are semantically identical,
the DBMS should recognise that and yield identical execution plans. I
consider it to have a bug if that's not the case.

--
\ “When I was a kid I used to pray every night for a new bicycle. |
`\ Then I realised that the Lord doesn't work that way so I stole |
_o__) one and asked Him to forgive me.” —Emo Philips |
Ben Finney

On 2009-11-27, Pif <nospam (AT) laposte (DOT) net> wrote:

order_id sum delivery_id cost
perhaps something like this. (untested)

SELECT o.order_id,o_tot,d_delivery_id,d.cost
FROM ( select order.id, sum(cost) as tot, min(delivery_id) from order
join delivery on delivery.order_id = order.order_id ) as o
RIGHT OUTER JOIN delivery on delivery.order_id = o.order_id
ORDER BY delivery.order_id,delivery_id

but it does not strike me as a good idea.

On Nov 27, 3:12*pm, Ben Finney <bignose+hates-s... (AT) benfinney (DOT) id.au>
wrote:

So what is the job of the DBMS? From wikipedia (just 'cause it was
the first thing that popped up in google, you are welcome to cite
others):

"A Database Management System (DBMS) is a set of computer programs
that controls the creation, maintenance, and the use of the database
with computer as a platform or of an organization and its end users."

Turning relations into reports may or may not be the job of the
database, but by definition it quite well can be the job of the
Database Management System. In fact, I would go so far as to say
using a DBMS as a bit bucket is a mistake I've seen often. There are
some things apps may do better, and some things the DBMS may do
better. Which things may be debatable, but saying either should do
all or neither is wrong.

Can't a function be part of the DBMS?

jg
--
@home.com is bogus.
http://radar.oreilly.com/2009/11/ste...web-sites.html