Load type custom

MAT · Post by **MAT** » Tue May 27, 2003 7:05 am

Hi all,

I would like to assign the value Current_Timestamp directly at the level of the loading stage. My first attempt of replacing the value ":MOD_DT_TM" by Current_Timestamp resulted in an error.

Mload Script:
dml label tdmload do update the existing rows;
UPDATE #ThisDataBase#.#Table#
SET EMAIL_ADDR = :EMAIL_ADDR,
MOD_DT_TM = current_timestamp
WHERE CUST_ACCT_ID = :CUST_ACCT_ID;
import INFILE #FilePath#/Dly/multiload/#ThisJob# format fastload
layout internal apply tdmload;

Thanks

MAT

ariear · Post by **ariear** » Tue May 27, 2003 2:54 pm

What is current_timestamp ? it's not a Datastage parameter (No # 'round it)
If it's a DataStage routine/Transform I don't believe the DataStage compiler will substitute it with a value at run time. You're running a Teradata utility based on a static input file after all

ray.wurlod · Post by **ray.wurlod** » Wed May 28, 2003 3:51 am

Agreed.

Calculate the current timestamp (once) in a stage variable
Oconv(@DATE,"D-YMD[4,2,2]"):" ":Oconv(@TIME,"MTS:")

Use this stage variable to deliver the value to the MOD_DT_TM column.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

MAT · Post by **MAT** » Wed May 28, 2003 6:14 am

Current_Timestamp is an Sql teradata variable. I Agree with you about the format of the TimeStamp in DataStage but I would like to use this current_Timestamp as it is more accurate.

When I see the Log report, I have this message :

"DML label name 'TDMLOAD' was not previously defined."

TDMLOAD is the Teradata Stage.

Thanks

MAT

MAT · Post by **MAT** » Fri May 30, 2003 1:24 pm

The Answer is :[:p]

.dml label tdmload;
UPDATE #ThisDataBase#.#Table#
SET EMAIL_ADDR = :EMAIL_ADDR,
MOD_DT_TM = current_timestamp
WHERE CUST_ACCT_ID = :CUST_ACCT_ID;
.import INFILE #FilePath#/Dly/multiload/xxx format fastload
layout internal apply tdmload;

with xxx : name of your stage. And the field "MOD_DT_TM" has to be declared in your Input file as if you don't take the value.

MAT