Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 19 Nov 2012 @ 22:58:37 GMT

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

Subj:   Re: How to generate Surrogate Key using CSUM?
From:   Dieter Noeth

Dipti Prakash Lenka wrote:

     > SELECT
     >    RANK(subline_id ASC)+ MAX_EDW_ID,  --AUTO GENERATED EDW_ID
     >    instance_id,
     >    test
     > FROM
     >     ttmp.test_dipti_csum  AS STAGE
     >         , (
     >             SELECT COALESCE(MAX(subline_id), 0)
     >             FROM ttmp.test_dipti_csum
     >           ) AS TARGET ( MAX_EDW_ID );

Never use CSUM(1,1) (which will skew), use ROW_NUMBER instead of RANK.

And in TD13 you can avoid the product join using a Scalar Subquery, which should result in a DISPATCHER RETRIEVE step instead:

         ROW_NUMBER () OVER (ORDER BY subline_id)
         + (SELECT COALESCE(MAX(subline_id) FROM ttmp.test_dipti_csum), 0),
     FROM ttmp.test_dipti_csum  AS STAGE


  <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: 28 Jun 2020