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

pamelafluente_at_libero.it wrote:
> Marshall ha scritto:
> > When I say "as small as possible" I literally mean that the
> > removal of even one table, or one column, or even just one
> > row would not demonstrate the issue any more.
>
> Here one small example I can think of.
> The minimum number of table is probably 3 because:
> 1 table: it's impossible to duplicate values
> 2 tables in 1-N relationship: placing a function on the dimension table
> would probably be too artificious an example
>
> 3 tables
> --------------
>
> ONE
> |
> TRANS
> |
> SUBTRANS
>
> Table: ONE
> Name
> David
> Marshall
>
> Table: TRANS
> Name Item Fact
> David IT2 1
> Marshall IT1 1
> Marshall IT1 1
>
> Table: SUBTRANS
> Item SubFact
> IT1 1
> IT2 1
> IT2 1
> IT2 1

I note that both TRANS and SUBTRANS have duplicate rows. Did you know that that is a really bad practice?

> Report:
> One.[Name] AS [Name],
> count(Trans.Fact) AS [FactCount],
> count(SubTrans.SubFact) AS [SubFactCount]
>
>
> David has 1 fact and Marshall 2 facts.
>
> "questionable" select:
>
> SELECT
> One.[Name] AS [Name],
> count(Trans.Fact) AS [FactCount],
> count(SubTrans.SubFact) AS [SubFactCount]
> FROM
> One,
> Trans,
> SubTrans
> WHERE
> One.[Name] = Trans.[Name] AND
> Trans.Item = SubTrans.Item
> GROUP BY
> One.[Name];

So your question is how to handle the repetition of values from TRANS when aggregating over a column in TRANS with a non-idempotent aggregate?

What is wrong with "don't do that" as the answer? Or do the aggregate before you join the third table.

Marshall Received on Sat Sep 16 2006 - 22:21:03 CEST