Archives of the TeradataForum

Message Posted: Mon, 02 Apr 2007 @ 17:03:56 GMT

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

Hi ,

I have 2 tables call

Table A_acc_sp (5 million) and B_acc_tmp (200 records)

Table A_acc_sp have 6 columns (7) call Id, acc_col_1, acc_col_2, acc_col_3, acc_col_4, acc_col_5 and acc_col_6.

Table B_acc_tmp have 3 columns (3) call sur_id (surrogate key), acc_id and acc_cd

I want to do 6 left outer join conditions from Table A to Table B i.e.

     Left Outer Join on
     A_acc_sp .acc_col_1= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '2A'

     Left Outer Join on
     A_acc_sp .acc_col_2= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '3A'

     Left Outer Join on
     A_acc_sp .acc_col_3= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '4A'

     Left Outer Join on
     A_acc_sp .acc_col_4= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '5A'

     Left Outer Join on
     A_acc_sp .acc_col_5= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '6A'

     Left Outer Join on
     A_acc_sp .acc_col_6= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '7A'

Hear I have 6 different join conditions with same tables and extract B_acc_tmp.Id then insert into A_acc_sp. so Left Outer Join are happened 6 times (6 Spool spaces) with A_acc_sp (5 million records)

Is there any way to load this data with single spool space (one join condition) using CASE statement?

Appreciate for your help.

Regards,

Ramu.

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