Posted by Ed Murphy on 11/20/07 10:02

AF wrote:

> I am having a problem with several reports at work. We use an SQL
> generator package where we fill in a template, and the system
> generates SQL code.
>
> The reports I have been running at a low level return a sales value of
> $96,000 for a specific office for 2006.
>
> Here is my filter,
>
> Office = 23
>
> Region = Northeast
>
> Product Cat = (several different categories)
>
> Year = 2006
>
> When I added some additional columns, the sale for the same office
> went to over $9 Million. When I analyzed this further, I found all
> offices in the region were being returned for the second report, and
> thus I ended up with the sales for all of the regions sales.
>
> What I am really confused about is how using the exact same filter, a
> simple report can show one number and then by adding some facts or
> columns my sales went up. (and I did confirm character by character
> we are using the same filter.)
>
> Is this explainable based on some principle of SQL I am unfamiliar
> with?
>
> One explanation I received from IT, who is too busy to look at my
> problem, is that by adding additional columns, I essentially asked our
> SQL generator to set up a larger join than I expected.
>
> If this were true, wouldn't the filter still eliminate records that
> don't meet the filter requirements?
>
> I suspect the SQL generator applied the filter at the wrong spot. I
> tried looking at the SQL: code, but it is very complicated.
>
> So I am turning to this forum to see if anyone can think of a logical
> explanation that would allow SQL to in effect return a larger dataset
> than my original report.

Joins imply multiple tables. "Larger join" may indicate that the
links between these tables are incomplete, causing the newly added
tables to be insufficiently restricted.

Real-world example: Sales order history is stored in a pair of
tables (header and lines). However, the header table has one row
for every time the order was invoiced, and the line table has one
or more rows for every time the order was invoiced. If you don't
include the invoice number in the linkage, then you get duplication.

Can you post DDL for the tables involved? Also, out of curiosity,
what SQL generator are you using?

[Back to original message]