Archives of the TeradataForum

Message Posted: Wed, 20 Jun 2012 @ 12:22:49 GMT

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

There's two ways that I know of doing this :

1) Have the Date value defined in a file and then employ the USING functionality within Bteq, which can then use that value in your Procedure.

I recently needed to do this recently, so I know it works.

Here's my example :

     USING (DB_NM VARCHAR(30)
           ,TB_NM VARCHAR(30)
           ,FILE_CT VARCHAR(9)
           ,INS_CT VARCHAR(9)
           ,UPD_CT VARCHAR(9)
           ,DEL_CT VARCHAR(9)
           ,BEFORE_UPD_TD VARCHAR(19)
           ,BEFORE_INS_TD VARCHAR(19)
           ,AFTER_INS_TD VARCHAR(19))
     EXEC ${ENV}DBA_X.LOAD_LOG_M (:DB_NM
                                  ,:TB_NM
                                  ,:FILE_CT
                                  ,:INS_CT
                                  ,:UPD_CT
                                  ,:DEL_CT
                                  ,:BEFORE_UPD_TD
                                  ,:BEFORE_INS_TD
                                  ,:AFTER_INS_TD);

You'll notice that I made all my DateTime Columns character, as I couldn't get it working with a definition of Timestamp(0) for some reason.

2) Have your bteq defined as a set of in-line text within a shell script, so that variable substitution can then work.

Example ....

     -------------------------------------------------------------------------

     bteq << EOI

     .Logon $TDPID/$ENV$DBASEU,$DBASEP;

     .SET ERROROUT STDOUT;

     Select Count(*) From $ENV$UTIL_DB.CORART01_ERR1; .If activitycount <> 0 Then .GOTO ERR1_CPY; .GOTO ERR2;

     .Label ERR1_CPY
     CALL ${ENV}DBA_X.MOVE_ERR_TABLE_P
              ( '$UTIL_DB'
              , 'CORART01_ERR1'
              , '${C3}'
              , ResultCode
              , ResultMsg
              , SqlMsg);
     Select Count(*)
       From ${ENV}GTT_T.MOVE_ERR_TABLE_STATUS_GTT
       HAVING COUNT(*) > 0;

     .If activitycount <> 0 Then .Exit 12;

     .REMARK 'ERR1 should have been copied to Table CORART01_ERR1 with a suffix of ${C3} and DateTime'

     -------------------------------------------------------------------------

As you'll see, some of the inputs to this Procedure are Shell script variables, materialized at run time.

Hope that helps.

Regards

David Clough
Senior Database Designer
Database Design Group

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