FASTLOAD SCRIPT ISSUE

Tools & Utilities
Fan

FASTLOAD SCRIPT ISSUE

I have an issue with my fastload script. It loads the data into my target table however the characters like '*' in my fixed width flat file  need to be converted to null.

My client  requirement is that multiple '********' in fixed width file need to be treated as null while loading to table . Could anyone help on this.

Thanks .Appreciate your answers.

12 REPLIES
Teradata Employee

Re: FASTLOAD SCRIPT ISSUE

Hi Gans,

Not sure about your question but if records in the input file, have a field with value '********' and you want to load it as a NULL, then you can use NULLIF clause in the DEFINE command.

Thomas

Teradata Employee

Re: FASTLOAD SCRIPT ISSUE

Use NULLIF command in Fastload. Check Manual for details.

Fan

Re: FASTLOAD SCRIPT ISSUE

 trans_id (char (28) , NULLIF = '*' ),

 item_name (char (127), NULLIF ='*'),

This is how i am defining my statement ... but still the table is loaded with  '*******' from the fixed width file. 

Teradata Employee

Re: FASTLOAD SCRIPT ISSUE

FastLoad does an exact match, byte-for-byte.

It is not pattern matching.

So, if you field will have 7 '*'s, then your NULLIF clause must have 7 '*'s.

If each row will have a different number of them, then the NULLIF feature will not work.

-- SteveF
Fan

Re: FASTLOAD SCRIPT ISSUE

Thanks feinholz. It really works..  One problem is i do have some columns which has more 127 '*' loading to varchar(127).

I tried using 127 '*' in NULLIF statement. 

Got the error :"Nullif value too long for column:     "

 Is this can be solved ?

Teradata Employee

Re: FASTLOAD SCRIPT ISSUE

Currently, FastLoad supports NULLIF character value up to 80 bytes.

Teradata Employee

Re: FASTLOAD SCRIPT ISSUE

As indicated above, the match has to be exact (in content and length).

With VARCHARs that is difficult because each row may have a different length for that field.

Thus, the NULLIF feature does not work well with VARCHAR fields.

-- SteveF
Fan

Re: FASTLOAD SCRIPT ISSUE

Thanks Thomas and feinholz. This really helps.

Enthusiast

Re: FASTLOAD SCRIPT ISSUE

Fast export script issue :

I have to export a file having more than a crore records. whem am running the fast export script i am getting below error. Kindly advice.

UTY4014 : Access module error '34' received during 'write operation: 'pmunxWBuf; fell error ( system error message dectectd : 27 )

Error attempting to write file