Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Tue, 14 May 2002 @ 22:59:43 GMT


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


Subj:   Re: Sequential number generation
 
From:   John K. Wight

Here an example of using CSUM when inserting rows into a table. It assume that the new row's sequences are unique within the day.

INSERT INTO
         TMPInput
        (
        UNIQKEY
        ,BusinessDate
        ,CompanyID
        ...             /* More columns */
        ,TxnType
        )
SELECT
        CSUM    (               /* Unique row id */
                1
                ,dly.TtyleCode
                ,dly.ColorCode
                ,dly.MarketingCode
                )
        ,stg.CurrentBusinessDate                /* Current BUS_DT */
        ,dly.CompanyID
        ,...                    /* More columns */
        ,dly.TxnType
FROM
         Stageing.TMP_BUS_DT  stg
        , Stageing.DailySales dly
;

Here is an example when inserting into a table that needs to start with the next highest sequence number:

INSERT INTO
        InventoryDB.MasterTable
        (
                UNIQUE_KEY
                ,BUsinessDate
                ,CompanyID
                ...
                ,TxnType
        )
SELECT
        IMTSK.StartKey + CSUM(1,CompanyID,..., ProductReasonNumber)
        ,stg_dt.CurrentDate
        ,SIDS.CompanyID
        ....
        ,SIDS.TxnType
FROM
        (
        SELECT MAX(UNIQUE_KEY) as StartKey
        FROM
                Inventory.MasterTable
        ) AS IMTSK
        ,Staging.StageInDailySales  SIDS
;

Hope this helps.



     
  <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