Archives of the TeradataForum
Message Posted: Wed, 02 Oct 2002 @ 16:29:54 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|