Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 01 Jul 2004 @ 15:20:17 GMT


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


Subj:   Re: Stored procedures and updating seq number with a cursor for loop
 
From:   Glen Blood

This is a topic that comes up fairly often. I found a solution the other day that seems fairly simple and I don't think that it has been mentioned. It involves the use of the ROW_NUMBER() function.

Basically I built two tables a Staging table and the final one.

The staging table (call it T_STAG_DIM_DEFN) would have one column, DIM_CD (could have all of the keys) with an UPI on that code.

The final table (call it T_DIM_DEFN) has the DIM_CD and DIM_ID. I used VARCHAR for DIM_CD and INTEGER for DIM_ID.

Load the data into T_STAG_DIM_DEFN and since T_DIM_DEFN may or may not be empty, Row NUMBER runs from 1 to ... I need the current maximum value in the table:

Then run

     SELECT (ROW_NUMBER()  OVER (ORDER BY DIM_CD))  + (COALESCE(MAXDIM_ID,0)),
     DIM_CD, DIM_NM
     FROM T_STAG_DIM_DEFN,
     (SELECT MAX(DIM_ID) FROM T_DIM_DEFN) maxdim(MAXDIM_ID)
     WHERE DIM_CD NOT IN (SELECT DIM_CD FROM T_DIM_DEFN)

Since you have a write lock on the table, there are no semaphore issues. and it should run in parallel.

Glen



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016