All Forums Tools
ssteckley 10 posts Joined 07/08
29 Jul 2008
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

Fred 1096 posts Joined 08/04
29 Jul 2008

How about using MultiLoad instead? That would support COALESCE.

ssteckley 10 posts Joined 07/08
30 Jul 2008

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

ssteckley 10 posts Joined 07/08
12 Aug 2008

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.

You must sign in to leave a comment.