Archives of the TeradataForum

Message Posted: Wed, 02 Oct 2002 @ 16:29:54 GMT

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

We are puzzled by this issue with a left join. Any ideas?

-table1 contains 237,181 rows and has a unique primary index on acct (acct is only col. on table)

-table2 is a view that does a SELECT * from the source table (no joins in the view). The source table contains 30M rows and has a unique primary index on acct and phone.

this query does not work correctly and returns 113,217 rows:

sel
t1.acct,
t2.acct as acct2,
phone,
Con_actend_date,
combill_status,
churn_rank,
Cgsa,
Bus_seg,Bus_ind

from table1 t1
                left outer join
                table2 t2
                on t1.acct=t2.acct

this query works correctly and returns all of the 237,181 rows:

select *
from
(sel acct as acct2
from table1) t1

left join

(sel acct,phone,Con_actend_date,combill_status
,churn_rank,Cgsa,Bus_seg,Bus_ind
from table2) t2

on t1.acct2=t2.acct

The weird part is that if I eliminate one of the columns on the incorrectly working query (say I take out con_actend_date or any other column other than acct) then the query starts working correctly again.

Our current Teradata manual (release 3) shows a left join example the same as the incorrectly running query above. Any ideas?

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