Fastload Inserting Default User and Date

Tools
Enthusiast

Fastload Inserting Default User and Date

I have the following fastload script

CREATE MULTISET TABLE "TABLE" ( FIELD1              VARCHAR(25) CHARACTER SET UNICODE CASESPECIFIC ,

                                               FIELD2                  VARCHAR(25) CHARACTER SET UNICODE CASESPECIFIC ,

                                               FIELD3                  VARCHAR(25) CHARACTER SET UNICODE CASESPECIFIC ,

                                               FIELD4                  VARCHAR(25) CHARACTER SET UNICODE CASESPECIFIC ,

                                               LOAD_USER                  VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC DEFAULT USER,

                                               LOAD_DATE                  TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ) ;

BEGIN LOADING "TABLE"

    ERRORFILES TABLE_errors1, TABLE_errors2

    INDICATORS ;

AXSMOD Oledb_Axsmod "noprompt jobid=1";

DEFINE FIELD1             (VARCHAR(765)),

       FIELD2                  (VARCHAR(765)),

       FIELD3           (VARCHAR(765)),

       FIELD4          (VARCHAR(765)),

       LOAD_USER                  (VARCHAR(765)),

       LOAD_DATE                  (DATE), FILE=Untitled ;

INSERT INTO "TABLE" ( FIELD1, FIELD2, 

                                     FIELD3, FIELD4,

                                     LOAD_USER, LOAD_DATE )

    VALUES ( :MITS_CLAIM_ID, :PLAN_CODE, :MBR_MEDICAID_NBR, 

             :DTE_FIRST_SVC_HDR, :DEFAULT, :DEFAULT ) ;

But it crashes, saying DEFAULT and DEFAULT  are not defined.  I've tried variations, including USER(), USER, DATE, etc, but they always come back as "not defined".  And if I just leave the fields out of my VALUES statement, I receive the error message that my column name list longer than value list.  I'm trying to default the current user and date.  I've tried every variation I cna think of, including trying to "hardcode" "USER" instead of :DEFAULT, for example, with no luck.

Tags (1)
1 REPLY
Teradata Employee

Re: Fastload Inserting Default User and Date

Have you tried DEFAULT instead of :DEFAULT?

Also, since you have defaults assigned on the CREATE TABLE, if you leave out the column references from the INSERT and the VALUES clauses, it should work, as in:

INSERT INTO "TABLE" (FIELD1, FIELD2, FIELD3, FIELD4) VALUES(:MITS_CLAIM_ID, :PLAN_CODE, :MBR_MEDICAID_NBR,:DTE_FIRST_SVC_HDR);

That should enable defaults to be applied to the LOAD_USER and LOAD_DATE columns.

-- SteveF