 | |
#11
 
| |||
| |||
|
Re: Sql request -
11-30-2009
, 04:26 PM
|
Pif <nos... (AT) laposte (DOT) 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 |
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
#12
| |||
| |||
|
|
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): |
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
#13
| |||||||
| |||||||
|
|
On Nov 27, 3:12*pm, Ben Finney <bignose+hates-s... (AT) benfinney (DOT) id.au wrote: 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. So what is the job of the DBMS? |
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.
Quote:
|
From wikipedia (just 'cause it was the first thing that popped up in google, you are welcome to cite others): |
Date, and Hugh Darwen.
Quote:
|
"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." |
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.
Quote:
|
Turning relations into reports may or may not be the job of the database |
model. As you imply by making the distinction, DBMS * database.
Quote:
|
but by definition it quite well can be the job of the Database Management System. |
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.
Quote:
|
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. |
(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.
Quote:
|
Can't a function be part of the DBMS? |
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
#14
| ||||
| ||||
|
|
But you have a good point about the stock_order table not being necessary: […] |
Quote:
|
Runs on PostgreSQL and DB2 just as well. |
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
Quote:
|
| 2 | 30.00 | 3 | 20.00 B | 200.00 | 1 | 200.00 |
Quote:
|
| 2 | 50.00 (6 rows) |
--
\ “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
#15
| |||
| |||
|
|
joel garry <joel-garry (AT) home (DOT) com> writes: |
Quote:
|
Can't a function be part of the DBMS? 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. |
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
#16
| |||
| |||
|
|
On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney bignose+hates-spam (AT) benfinney (DOT) id.au> wrote: joel garry <joel-garry (AT) home (DOT) com> writes: [snip] Can't a function be part of the DBMS? A non-relational function shouldn't be part of a relational DBMS, no. |
*query* returning a non-relational result, which is really as far as I
should have taken it.
Quote:
|
Addition of integral values is a function mapping two integral values to an integral value. |
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
#17
| |||
| |||
|
|
Gene Wirchenko <genew (AT) ocis (DOT) net> writes: On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney bignose+hates-spam (AT) benfinney (DOT) id.au> wrote: joel garry <joel-garry (AT) home (DOT) com> writes: [snip] Can't a function be part of the DBMS? A non-relational function shouldn't be part of a relational DBMS, no. 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. |
claim to be able to define the split myself. I know how difficult it
can be to rigourously define something.)
Quote:
|
Addition of integral values is a function mapping two integral values to an integral value. 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. |
select clicode,cliname from clients order by clicode
because of the order by clause.
Having to go through a sort stage when the DBMS might well have
been able to easily handle it would be counterproductive.
For example, a group by in SQL forces the result to be sorted by
the grouping unless otherwise overridden.
Quote:
|
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. |
Sincerely,
Gene Wirchenko
#18
| |||
| |||
|
|
On Tue, 01 Dec 2009 13:45:44 +1100, Ben Finney bignose+hates-spam (AT) benfinney (DOT) id.au> wrote: 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. A relation does not have order. This would not be a relation select clicode,cliname from clients order by clicode because of the order by clause. |
relation; but I certainly take the point about ‘ORDER BY’ requesting
order be imposed on an orderless relation.
I guess I would want to say that a ‘SELECT’ result, though it is ordered
(either implicitly or at the request of the query author), is
nevertheless *compatible with* a relation, as distinct from the way that
the desired “omit some of the values to make the report look closer to
what I want” that started this thread is incompatible with relations.
Quote:
|
Having to go through a sort stage when the DBMS might well have been able to easily handle it would be counterproductive. |
I'm glad I've been asked about my definitions, it has led to some
clarification. Does anyone have a better definition they'd like to offer
of what “the job of a relational DBMS” is?
--
\ “I do not believe in immortality of the individual, and I |
`\ consider ethics to be an exclusively human concern with no |
_o__) superhuman authority behind it.” —Albert Einstein, letter, 1953 |
Ben Finney
#19
| |||
| |||
|
|
Interesting. But you've changed it from a relational to a non-relational result (some tuples now contain nulls). |
And it is the same solution which I have already posted, just without the join (but the basic idea and technique ist the same)
It also had some other errors
Quote:
|
(referencing names that weren't defined). |
I rather rename columns than create unnecessary sub-selects
Putting the partition into the WINDOW clause at the end will only work for Postgres, not for Oracle or DB2 (they don't support that). An for just a single usage of the window it doesn't add to the clarity in my opinion. But formatting (and the usage of unnecessary sub-select) is a matter of personal style!
Regards
Thomas
#20
| |||
| |||
|
|
Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com> writes: And even if the "blank" stuff should be in there I don't understand the complicated sub-query you were using 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 |
using natural joins are diasters waiting to happen. All you need is
for someone to add a new column name to one of the tables that also
happens to have a corresponding but probably unrelated column with the
same name in the other table and now you join is garbage.
IMHO -- Mark D Powell --
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.