Archives of the TeradataForum

Message Posted: Tue, 26 Jun 2001 @ 23:46:43 GMT

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

The problem that you are encountering is because the nature of a macro. A macro is "precompiled" (for lack of a better word) when it is created. When the macro is created if the database is not specified the current default database is inserted into the generated macro code that is stored in the CreateText column in DBC.TVM. When you do a SHOW MACRO it displays the contents of RequestText which has an image of the DDL that was run to create the macro. Below I have included a sample macro and the "compiled" version of it. Notice that the database names have been hard-coded into the generated version of the code along with expanding the statements.

When you restore a macro, view, stored procedure, or trigger to a different database the CreateText is restored intact. Therefore, it will always point back to the default database that was in effect at the time of creation and not the default database at runtime.

A further reason that this is necessary is that at runtime the user will most likely have a different default database than the one that was in effect when the DBA created the macro. The intent of a macro, view, etc. is for it to run exactly like the DBA intended it to run when it was created. Otherwise the objects referenced in the macro would always be relative to the users default database at runtime.

ORIGINAL DDL STATEMENT:
-----------------------

REPLACE MACRO PPOL_AddCarrierCode
     (  carrierCode      (CHAR(2))
       ,carrierName      (CHAR(60))
       ,codeSharePartner (CHAR(1),DEFAULT 'Y')
       ,effDate          (DATE,DEFAULT DATE,FORMAT 'YYYY-MM-DD')
       ,userName         (CHAR(30),DEFAULT USER)  )  AS (

UPDATE PPOL_CarrierCode
   SET expDate         = :effDate
      ,lastUpdate      = CURRENT_TIMESTAMP(0)
      ,updateByUser    = :userName
 WHERE carrierCode     = :carrierCode
   AND :effDate       >= effDate
   AND :effDate        < expDate;

DELETE FROM PPOL_CarrierCode
 WHERE carrierCode     = :carrierCode
   AND effDate         > :effDate;

INSERT INTO PPOL_CarrierCode  VALUES (
       :carrierCode
      ,:carrierName
      ,:codeSharePartner
      ,:effDate
      ,DATE '2199-12-31'
      ,CURRENT_TIMESTAMP(0)
      ,:userName
      ,CURRENT_TIMESTAMP(0)
      ,:userName );
);

CONTENTS OF DBC.TVM.CreateText COLUMN:
--------------------------------------

REPLACE MACRO "BRIEDEV1"."PPOL_AddCarrierCode" (
      "carrierCode" (CHAR(2), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'X(2)', NULL),
      "carrierName" (CHAR(60), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'X(60)', NULL),
      "codeSharePartner" (CHAR(1), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'X(1)', NULL, DEFAULT 'Y'),
      "effDate" (DATE, FORMAT 'YYYY-MM-DD', NULL, DEFAULT DATE ),
      "userName" (CHAR(30), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'X(30)', NULL, DEFAULT USER )) AS (

UPDATE "BRIEDEV1"."PPOL_CarrierCode"
  FROM "BRIEDEV1"."PPOL_CarrierCode"
   SET "expDate" = :"effDate",
       "lastUpdate" = CURRENT_TIMESTAMP(0),
       "updateByUser" = :"userName"
 WHERE (("BRIEDEV1"."PPOL_CarrierCode"."carrierCode" =  :"carrierCode" )
   AND  (:"effDate" >=  "BRIEDEV1"."PPOL_CarrierCode"."effDate" ))
   AND  (:"effDate" <  "BRIEDEV1"."PPOL_CarrierCode"."expDate" );

DELETE "BRIEDEV1"."PPOL_CarrierCode"
  FROM "BRIEDEV1"."PPOL_CarrierCode"
 WHERE ("BRIEDEV1"."PPOL_CarrierCode"."carrierCode" =  :"carrierCode" )
   AND  ("BRIEDEV1"."PPOL_CarrierCode"."effDate" >  :"effDate" );

INSERT INTO "BRIEDEV1"."PPOL_CarrierCode" (
       :"carrierCode" ,
       :"carrierName" ,
       :"codeSharePartner" ,
       :"effDate" ,
       DATE '2199-12-31',
       CURRENT_TIMESTAMP(0),
       :"userName" ,
       CURRENT_TIMESTAMP(0),
       :"userName" );

);

John......

---------------------------------------------------------------
E.John Aldrin, Senior Consultant II
NCR Corporation
New Berlin, WI 53151-0677
---------------------------------------------------------------

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