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

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

On Nov 27, 6:12*pm, Ben Finney <bignose+hates-s... (AT) benfinney (DOT) id.au>
wrote:
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

ddf wrote on 30.11.2009 22:26:
You don't need SQL*Plus for the formatting, analytical functions are enough

But you have a good point about the stock_order table not being necessary:

select case
when lag(order_code) over (partition by order_code order by delivery_sequence) = order_code then ''
else order_code
end as display_code,
case
when lag(order_code) over (partition by order_code order by delivery_sequence) = order_code then null
else sum(amount) over (partition by order_code)
end as order_amount,
delivery_sequence,
delivery_amount
from stock_delivery
order by order_code, delivery_sequence

Runs on PostgreSQL and DB2 just as well.

Thomas

joel garry <joel-garry (AT) home (DOT) com> writes:

The job of a relational DBMS is to implement the relational model, by
implementing a logical set of rlations in a physical database
implementation, and to provide an interface to applications for managing
and querying the relations in that database. The output of those
operations should be either status messages or new relations.

I'm paraphrasing my understanding of the writings of E. F. Codd, C. J.
Date, and Hugh Darwen.

That seems fair, since it's not restricted to relational DBMS. I wasn't
specific before about the fact that we're talking about *relational*
database management systems behaviour, but I believe that's been the
topic consistently through this thread.

It's not; the job of the database is to be an implementation of a data
model. As you imply by making the distinction, DBMS â‰* database.

Not the relational DBMS. A DBMS might do *more* than the job of a
relational DBMS, but the distinction I'm making is that one should not
expect to find that behaviour in a relational DBMS since that's not its
job.

The output of a query into a relational DBMS should always be a relation
(or a status message). It's up to the application to use non-relational
means to turn that relation into whatever other form it requires.

A non-relational function shouldn't be part of a relational DBMS, no. An
application can *store* its non-relational functions in the *database*,
of course, and access them there; but database â‰* DBMS.

--
\ “Those who write software only for pay should go hurt some |
`\ other field.� —Erik Naggum, in _gnu.misc.discuss_ |
_o__) |
Ben Finney

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


Interesting. But you've changed it from a relational to a non-relational
result (some tuples now contain nulls). It also had some other errors
(referencing names that weren't defined).

I'm new to windowing operations (thank you for the motivation to learn
them), but I think this is an improvement of both the above points, plus
a bit clearer for me to read:

SELECT
CASE
WHEN (
LAG(order_code) OVER order_sequence
) = order_code THEN ''
ELSE order_code
END AS display_code,
CASE
WHEN (
LAG(order_code) OVER order_sequence
) = order_code THEN ''
ELSE CAST (
SUM(delivery_amount) OVER (PARTITION BY order_code)
AS VARCHAR)
END AS order_amount,
delivery_sequence,
delivery_amount
FROM (
SELECT
order_code,
sequence AS delivery_sequence,
amount AS delivery_amount
FROM stock_delivery
) AS d
WINDOW order_sequence AS (
PARTITION BY order_code ORDER BY delivery_sequence ASC)
ORDER BY order_code ASC, delivery_sequence ASC;

PostgreSQL 8.4 gives me:

=====
display_code | order_amount | delivery_sequence | delivery_amount
--------------+--------------+-------------------+-----------------
A | 100.00 | 1 | 50.00
C | 120.00 | 1 | 70.00
=====

--
\ “Pinky, are you pondering what I'm pondering?� “Uh, I think so, |
`\ Brain, but I get all clammy inside the tent.� —_Pinky and The |
_o__) Brain_ |
Ben Finney

On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney
<bignose+hates-spam (AT) benfinney (DOT) id.au> wrote:

[snip]

Addition of integral values is a function mapping two integral
values to an integral value. Note the lack of a relation in the RDBMS
sense. I think it would be a very useful thing for an RDBMS to have
it.

(How do you more precisely make the division between in and out?)

Sincerely,

Gene Wirchenko

Gene Wirchenko <genew (AT) ocis (DOT) net> writes:

This was far too broad, I now see. Originally it was in the context of a
*query* returning a non-relational result, which is really as far as I
should have taken it.

Right; of course, there are heaps of functions operating on attribute
values that can be used *within* relational operations to modify the
relation that will be returned. Such functions definitely belong as part
of the relational DBMS.

What I was trying to express was that relational operations — like the
various relational operations that ‘SELECT’ implements — should only
return data as relations (they might also return status responses). They
should never return non-relation data.

Transforming relational data into a non-relation is not the job of the
relational DBMS, but the job of applications that receive relational
data from the DBMS.

--
\ “Anyone who believes exponential growth can go on forever in a |
`\ finite world is either a madman or an economist.� —Kenneth |
_o__) Boulding |
Ben Finney