Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?

Alexandr Savinov ha scritto:

> pamelafluente_at_libero.it schrieb:

> Some comments. The result is not wrong - your query returns precisely
> what it has to return. And records are repeated NOT because of the
> existence of 1-N relationship - they occur because your query produces
> them. In this sense your problem consists in finding a way how to
> express things in SQL.

Yes I agree: this is exacly what I meant. The duplication "on side 1" is naturally created during the join process due to the relationship 1-N. But in a "chain" of joins
if you have some sum, count, ... functions on a side 1 you may get a result that *may or may not* be "correct" (depending on your "interpretation"
of the function). My aim was to devise a general way to produce these queries
in such a way to be able to make the user aware of this possible problems and
to provide him with the various alternatives, including the various join of subqueries
when necessary.

This is what reporting software usually does.

>
> I think that it is not a theoretical problem but the problem of

The part I am talking about is the theoretical one. About a general method to create the queries and subqueries. Clearly, this is usually destined to be implemented, although I guess if could be performed manually on small designs.

> implementation, i.e., how to implement certain logic. It seems that you
> know what kind of result you want to get and the only difficulty is in
> writing the corresponding SQL query. I do not say that it is simple. I
> see several alternatives how it can be encoded in SQL including nested
> queries and conventional WHERE restrictions. Yet the main difficulty is
> in the performance of such queries. For a real report such a query may
> take many pages and the ability to optimize it can be very important.

Yes That's actually one of the many problems I have in mind and that I found out when coding these things, like also determining the optimal permutation of tables in a join under some constraints , etc...

But I do not dare to ask...

>
> > -P
> >
>
> http://conceptoriented.com
Received on Sun Sep 17 2006 - 13:58:25 CEST