Archives of the TeradataForum

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

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

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:

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

Dieter

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