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.
Thanks for any help.
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.
Thanks for any help.
Comment