Archives of the TeradataForum

Message Posted: Fri, 31 Oct 2003 @ 19:02:36 GMT

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

Here you go:

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 + csum(1,STG.LOCATION_ID,STG.LONG_DIST_PROVIDER_ID)
  ,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
  -- MIGHT WANT AN "ON" CLAUSE HERE!
 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>>