Archives of the TeradataForum
Message Posted: Tue, 23 Nov 2004 @ 17:27:24 GMT
<-- 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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|