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

pamelafluente_at_libero.it schrieb:
> The duplication I am talking about is the one caused by joining tables
> in 1-N relationship. When you make the join ONE-TRANS-SUBTRANS the
> values in TRANS are replicated due to the relationship 1-N with
> SUBTRANS . So (non idempotent) functions computed on TRANS consider the
> same values more times they should (duplicated values). This yeld a
> wrong result in the report for these functions.

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.

> Smart software is perfectly capable to split this query in subqueries,
> as also Alexander pointed out in the previous post. My original
> question was:
>
> 1. how is this problem called in the theory (are there references to
> study?)
> 2. what is the algorithm to do that (or is it some kind of industry
> secret that only few leader softwares implements?)

I think that it is not a theoretical problem but the problem of 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.

> -P
>

http://conceptoriented.com Received on Sun Sep 17 2006 - 12:52:19 CEST