Problem with reporting and queries

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.AF (bscinc@.Yahoo_NoSpam.com) writes:

Quote:

Originally Posted by

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.

It's of course impossible to debug a tool that I have never seen.
I can think of lots of reasons, including user errors on your
part, errors in the tool you use, or in the data model you access.

If I understood your story correctly, the second report rendered the
filter on office void and useless. That's some kind of clue, but enough
to say "Aha!".

You could at least post the queries, to give us something to work with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

problem with query where column name is the same as a keyword

hi

I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.

in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)

CREATE PROCEDURE topcat.getTransHistory
(
@.contact_id numeric(9)
)
AS
BEGIN
DECLARE @.phone_no varchar(255)

set @.phone_no = (select top 1 phone_num from topcat.class_contact where _id = @.contact_id)

select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @.contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @.phone_no

END
GO

Cheers
James :)wrap your column name that is a keyword in square brackets eg [desc]|||thank you
thank you
thank you

you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.

:)|||no worries,... try to avoid keywords in the future would be my advise though... ;)|||i was on a contract once and there converted database had tables and columns all over the place named with keywords.

i will never forget they had a column called select in a table called order.
nightmare to say the least.