Archives of the TeradataForum

Message Posted: Fri, 31 Oct 2003 @ 17:57:35 GMT

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

Here is the Query. JOB_ID is the Primary Key in the table and should be a running sequence number. The batch process every-day inserts rows into LU_JOB_INFO (look up table) from STG_JOB_INFO which is refreshed and gets new data everyday. The JOB_ID then will go as the reference key in all other FACTS table. Please see the ROWNUM in the SELECT-list that is what I want to replace with a Teradata function or psedo-column to get a running sequence number. I don't care about the order, but it should be a running sequence number.

Thanks for your help.

Ganga Palakattil

INSERT INTO $masterdb.LU_JOB_INFO

(JOB_ID,LOCATION_ID,LONG_DIST_PROVIDER_ID,SERVICE_CARRIER_ID,CONTACT_TYPE,

CONTACT_METHOD,METRIC_TYPE,SCHEDULED_DATE,REPORTING_MONTH,REPORTING_YEAR,

PANEL_SEQ_ID,TESTSPEC_SET_ID,CONTACT_TECHNOLOGY,IMPORT_LOCATION_ID,
            RES_FILE_NAME,MAP_FILE_NAME,MIN_FILE_NAME)
     SELECT (MAX.MAX_JOB_ID +
ROWNUM),STG.LOCATION_ID,STG.LONG_DIST_PROVIDER_ID,

VW.CARRIER_ID,STG.CONTACT_TYPE,STG.CONTACT_METHOD,STG.METRIC_TYPE,

STG.SCHEDULED_DATE,STG.REPORTING_MONTH,STG.REPORTING_YEAR,STG.PANEL_SEQ_ID,

STG.TESTSPEC_SET_ID,STG.CONTACT_TECHNOLOGY,STG.IMPORT_LOCATION_ID,
            STG.RES_FILE_NAME,STG.MAP_FILE_NAME,STG.MIN_FILE_NAME
       FROM $stagedb.STG_JOB_INFO STG
            INNER JOIN (SELECT ZEROIFNULL(MAX(JOB_ID)) MAX_JOB_ID
                          FROM $masterdb.LU_JOB_INFO) MAX
            INNER JOIN $masterdb.VW_OBC_CARRIER_MARKET VW
         ON VW.OCTS_MARKET                  = STG.MARKET_NAME
        AND VW.OCTS_CARRIER                 = STG.CARRIER_NAME

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