Archives of the TeradataForum

Message Posted: Sat, 27 Oct 2007 @ 13:48:03 GMT

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

Hope any1 can help me with my query. My requirement is such that from source tables I am getting fields acct_num, var1, var2, var3. I have to create a volatile table with unique seq_num and finally insert into a target table, a sequence number greter than the existing sequence number, after checking that the combination of var1, var2, var3 is not present in the target table.

The target table does not have acct_id field, while the volatile table should have the acct_id field after picking from the source table. Below is the concept we are using for it and attched is the actual code. In the below code dim1 is my target table and stg_tab is my volatile table. The blow logic works fine for small no. of rows but when the no. of rows increases we start getting spool space error. Any better approaches?

     select y.*, case when  b_dim1_id is not null  then b_dim1_id  else
                       max_dim1_id + sum(case when prev_attr = curr_attr then 0
                                                       else 1
                                                       end
                                                     ) over (order by curr_attr,
     prev_attr rows unbounded preceding)
     end new_key,
                      case when  b_dim1_id is not null  then b_dim1_id  else
     max_dim1_id + sum(case when prev_attr = curr_attr then 0
                                                       else 1
                                                       end
                                                     ) over (partition by
     b_dim1_id order by curr_attr,  prev_attr rows unbounded preceding)
                     end new_key1
     from
     (
     select x.*,  min(var1||':;'||var2||':;'||var3) over (order by var1, var2,
     var3 rows between 1 preceding and 1 preceding) prev_attr,
     var1||':;'||var2||':;'||var3 curr_attr

     from
     (
     select a.*, b.dim_id b_dim1_id, b.var1 b_var1, b.var2 b_var2, b.var3 b_var3
     from
     (select a.*,  c.*
     from prod_work.stg_tab a, (select max (dim_id)  max_dim1_id from
     prod_work.dim1) c
     ) a
     left outer join
     prod_work.dim1 b
     on a.var1 = b.var1
     and a.var2 = b.var2
     and a.var3 = b.var3
     )x
     ) y

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