Archives of the TeradataForum

Message Posted: Tue, 24 Nov 2004 @ 02:02:21 GMT

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

If on v2r5 or later, use the MERGE statement to UPSERT the row based on the UPI.

     replace macro prod_base_views.job_status(script_id char(6),
                                              table_name char(30))
     as (

     MERGE INTO JOB_STATUS as js
     USING values( :SCRIPT_ID ,
                   :TABLE_NAME) as s(script_id, table_name)
          on js.script_id = s.script_id
         and js.table_name = s.table_name
     WHEN MATCHED THEN UPDATE
          SET updated_dt = date,
              updated_tm = current_time
     WHEN NOT MATCHED THEN
          INSERT (script_id,
                  table_name,
                  updated_dt,
                  updated_tm,
                  latest_period_loaded)
          VALUES (s.script_id,
                  s.table_name,
                  date,
                  current_time,
                  null);
     );

Kyle Prescott
UnumProvident - Data Strategies, 3-South

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