Archives of the TeradataForum

Message Posted: Wed, 26 Nov 2003 @ 19:19:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Has anyone experienced this problem. I have already opened an incident with NCR, but wanted to through this out to see if anyone else has encountered this problem.

I would have expected all 3 of these queries to return 1 row for branch 1111 since there is only 1 branch 1111 in the branch_tbl table.

Here is a summary of my 3 queries.

Query 1

select b.group_id,
         sd.branch_id,
         sum(sd.sales_amount)
from dss_tables.sales_detail sd
inner join dss_tables.branch_tbl b
on sd.branch_id = b.branch_id
group by 1,2

This is an inner join query not specifying anything in the where clause. This query returned 28 rows for branch 1111. If you add the sales_amount up for all 28 rows, it totals to the amount in query 2 and 3.

Query2

select b.group_id,
         sd.branch_id,
         sum(sd.sales_amount)
from dss_tables.sales_detail sd
left outer join dss_tables.branch_tbl b
on sd.branch_id = b.branch_id
group by 1,2

This is the same as query 1 except using a left outer join. This retured 1 row for branch 1111.

Query 3.

select b.group_id,
         sd.branch_id,
         sum(sd.sales_amount)
from dss_tables.sales_detail sd
inner join dss_tables.branch_tbl b
on sd.branch_id = b.branch_id
where sd.branch_id = 1111
group by 1,2

This is an inner join query which specified branch_id = 1111 in the where clause. This also returned 1 row.

TIA

Craig

	<Prev	Next>		<<First	<Prev	Next>	Last>>