COALESCE in FastLoad Script

Tools

COALESCE in FastLoad Script

I am trying to load data into a terdata database from a flat file (comma delimeted). The one problem I have encountered is character data. If a value is not supplied in the file, fast loads interprets this to null. However, the columns I am trying to load into do not allow nulls by design. I would rather fast load insert an empty string ("").

I have defined a default value, but that to does not overtake the null since I have to supply the column since some values to exist in the input file. I have also tried using the COASESCE function in the script, but I get an error, "expression not allowed in Fast Load Insert, column RESTRICTACCESS".

Can anyone assist me in a work around for this w/o having to change the column definition to accept null values?

Here is the part of the script using COALESCE.

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

VALUES ( :ACCOUNTNUMBER,
:TYPE_ID,
:LASTFMDATE,
:OPENDATE,
:CLOSEDATE,
:BRANCH,
COALESCE(:RESTRICTACCESS, ''),
:REFERENCE,
:WARNINGCODE1,
:WARNINGCODE2,
:WARNINGCODE3,
:WARNINGCODE4,

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

Thanks.
ss
3 REPLIES
Teradata Employee

Re: COALESCE in FastLoad Script

How about using MultiLoad instead? That would support COALESCE.

Re: COALESCE in FastLoad Script

Thanks for the suggestion. I was trying to use FastLoad and didn't know if that was possible.

ss

Re: COALESCE in FastLoad Script

Just to close the topic, I had a Teradata class this week and this is not possible in FastLoad. Nor is any other SQL function in this manner (trim, etc.). You must use multiload or BTEQ.