Archives of the TeradataForum

Message Posted: Tue, 23 Nov 2004 @ 17:27:24 GMT

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

<-- Anonymously Posted: Tuesday, November 23, 2004 11:36 -->

I need to create a macro which will either insert to or update a table depending whether a row already exists in that table.

The table is as follows :

     Script_Id  char(6)
     Table_Name varchar(30)
     Updated_dt  date
     Updated_tm  time
     UPI = script_id, table_name

The macro will have parameters of :scriptid and :tablename inputed into it. If script_id and table_name already exist, the macro will update the updated_dt and updated_tm columns. If they don't exist, it will insert a new row into table.

Can macros cope with multiple sql statements and bteq commands within them, i.e.

     REPLACE MACRO PROD_BASE_VIEWS.JOB_STATUS
       (SCRIPTID  CHAR(6)
       ,TABLENAME CHAR(30))
     AS (
         UPDATE DATABASE.JOB_STATUS
         SET    UPDATED_DT = DATE
               ,UPDATED_TM = TIME
         WHERE  SCRIPT_ID  = :SCRIPTID
         AND    TABLE_NAME = :TABLENAME
         ;

         .IF ERRORCODE <> 0 THEN .QUIT ERRORCODE
         .IF ACTIVITYCOUNT = 1 THEN .GOTO EXITMACRO
         .IF ACTIVITYCOUNT > 1 THEN .GOTO ERRORFOUND

         INSERT INTO DATABASE.JOB_STATUS
           (SCRIPT_ID
           ,TABLE_NAME
           ,UPDATED_DT
           ,UPDATED_TM
           ,LATEST_PERIOD_LOADED)
         SELECT
            :SCRIPTID
           ,:TABLENAME
           ,DATE
           ,TIME
           ,NULL)
         ;

         .LABEL ERRORFOUND
         .QUIT 8

         .LABEL EXITMACRO
         );

The documentation I have seen does not mention bteq commands in relation to macros?

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