Archives of the TeradataForum

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

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

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>>