Fastload with timestamp columns

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.

Fastload with timestamp columns

Hi All,

 

I have a scenario in which I have to load a flat file into a Teradata table through Fastload.

Here are the details:

 

Table Definition:

CREATE TABLE A02_PX_DUP_SCRIPTS_JUN15
(
PATIENT_ID INTEGER,
CLAIM_ID VARCHAR(27),
CLAIM_LINE_ITEM INTEGER,
CLAIM_TYPE VARCHAR(5),
PROCEDURE_CODE VARCHAR(58),
PROCEDURE_DATE TIMESTAMP,
PLACE_OF_SERVICE VARCHAR(72),
PLAN_TYPE VARCHAR(20),
PRACTITIONER_ID INTEGER,
UNITS_ADMINISTERED INTEGER,
CHARGE_AMOUNT INTEGER,
DELETION_FLAG VARCHAR(2),
MTX_PRIOR INTEGER,
DRUG_NAME VARCHAR(17),
CLASSIFICATION VARCHAR(23),
CLAIM_ID_UNIQUE VARCHAR(75),
PHYSICIAN_ID INTEGER,
SPECIALTY_CODE VARCHAR(4),
SPECIALTY_DESCRIPTION VARCHAR(90),
RX_FILL_DATE TIMESTAMP,
RX_DRUG VARCHAR(32)
);

 

FlatFile Content:

400343916,1141430010380989,44,UB92,J9250,06/23/2014 00:00:00,OUTPATIENT,COMMERCIAL,3969216,5,11.28,,1,METHOTREXATE,Non-biologic DMARDs,114143001038098944,252044,PHO,PEDIATRIC HEMATOLOGY ONCOLOGY,06/29/2014 00:00:00,METHOTREXATE

 

 

FastLoad Script:

LOGON TD_Dev/***,*****;
DATABASE Practice;
BEGIN LOADING Practice.A02_PX_DUP_SCRIPTS_JUN15
ERRORFILES A02_PX_DUP_SCRIPTS_JUN15_ET, A02_PX_DUP_SCRIPTS_JUN15_UV
CHECKPOINT 10;
SET RECORD VARTEXT ",";
DEFINE in_PATIENT_ID (VARCHAR(27)),
in_CLAIM_ID (VARCHAR(27)),
in_CLAIM_LINE_ITEM (VARCHAR(27)),
in_CLAIM_TYPE (VARCHAR(5)),
in_PROCEDURE_CODE (VARCHAR(58)),
in_PROCEDURE_DATE (VARCHAR(200)),
in_PLACE_OF_SERVICE (VARCHAR(72)),
in_PLAN_TYPE (VARCHAR(20)),
in_PRACTITIONER_ID (VARCHAR(27)),
in_UNITS_ADMINISTERED (VARCHAR(27)),
in_CHARGE_AMOUNT (VARCHAR(27)),
in_DELETION_FLAG (VARCHAR(2)),
in_MTX_PRIOR (VARCHAR(27)),
in_DRUG_NAME (VARCHAR(17)),
in_CLASSIFICATION (VARCHAR(23)),
in_CLAIM_ID_UNIQUE (VARCHAR(75)),
in_PHYSICIAN_ID (VARCHAR(27)),
in_SPECIALTY_CODE (VARCHAR(4)),
in_SPECIALTY_DESCRIPTION (VARCHAR(90)),
in_RX_FILL_DATE (VARCHAR(200)),
in_RX_DRUG (VARCHAR(32)),
FILE = /app/Informatica9.5.1/server/infa_shared/0101tdpfiicd/TgtFiles/A02_PX_DUP_SCRIPTS_JUN15.csv;
INSERT INTO A02_PX_DUP_SCRIPTS_JUN15(
PATIENT_ID,
CLAIM_ID,
CLAIM_LINE_ITEM,
CLAIM_TYPE,
PROCEDURE_CODE,
PROCEDURE_DATE,
PLACE_OF_SERVICE,
PLAN_TYPE,
PRACTITIONER_ID,
UNITS_ADMINISTERED,
CHARGE_AMOUNT,
DELETION_FLAG,
MTX_PRIOR,
DRUG_NAME,
CLASSIFICATION,
CLAIM_ID_UNIQUE,
PHYSICIAN_ID,
SPECIALTY_CODE,
SPECIALTY_DESCRIPTION,
RX_FILL_DATE,
RX_DRUG
)
VALUES(
:in_PATIENT_ID,
:in_CLAIM_ID,
:in_CLAIM_LINE_ITEM,
:in_CLAIM_TYPE,
:in_PROCEDURE_CODE,
:in_PROCEDURE_DATE (FORMAT 'MM/DD/YYYYBHH24:MI:SS'),
:in_PLACE_OF_SERVICE,
:in_PLAN_TYPE,
:in_PRACTITIONER_ID,
:in_UNITS_ADMINISTERED,
:in_CHARGE_AMOUNT,
:in_DELETION_FLAG,
:in_MTX_PRIOR,
:in_DRUG_NAME,
:in_CLASSIFICATION,
:in_CLAIM_ID_UNIQUE,
:in_PHYSICIAN_ID,
:in_SPECIALTY_CODE,
:in_SPECIALTY_DESCRIPTION,
:in_RX_FILL_DATE (FORMAT 'MM/DD/YYYYBHH24:MI:SS'),
:in_RX_DRUG
);
END LOADING;
LOGOFF;

 

 

Can someone please tell me what am i doing wrong in this fastload?

 

4 REPLIES
Senior Apprentice

Re: Fastload with timestamp columns

If you did something wrong there's probably some error message returned, would be nice to add this info :-)

 

The FORMAT you added is not valid for a VarChar, you need to add the datatype, too:

 (TIMESTAMP(0), FORMAT 'MM/DD/YYYYBHH24:MI:SS')

Btw, it's better to define a Timestamp with a proper subseconds, if you only get seconds you should use TIMESTAMP(0), currently you get a TIMESTAMP(6) (and it's hard to truncate the fractional part).

 

And never use a CHECKPOINT as low as 10, should be at least 100,000

Re: Fastload with timestamp columns

Thank you for the reply.

I think you are referring to these lines " in_PROCEDURE_DATE (VARCHAR(200))," and "in_RX_FILL_DATE (VARCHAR(200)),".

And yes, I am getting errors on these fields. So, are you suggesting that in these lines, I should replace VARCHAR with (TIMESTAMP(0), FORMAT 'MM/DD/YYYYBHH24:MI:SS')? 

Actually, I used varchar because in most examples present on web, date and timestamp fields were handled using VARCHAR initially and then casting them using the correct format.

 

Please suggest.

 

Re: Fastload with timestamp columns

Also, the records are going to error table 1 with message "6,760 IN_PROCEDURE_DATE"

Highlighted
Senior Apprentice

Re: Fastload with timestamp columns

When you define VarText you must define all input columns as VarChars.

 

The TIMESTAMP is added in the INSERT part:

:in_PROCEDURE_DATE (TIMESTAMP(0), FORMAT 'MM/DD/YYYYBHH24:MI:SS'),

 And in the column definition you should decrease the size to match the input like VARCHAR(20) instead of (200)