teardata fastload and null values.....

Tools & Utilities

teardata fastload and null values.....

Hi All,
I have a data file whose contents are as follows :-

1,aaaaaaaa
2,

fastload can load the first record correctly but cannot load the second record correctly because of the string of nulls.

Is there any way for fastload to load this file?

rajar11
2 REPLIES
Enthusiast

Re: teardata fastload and null values.....

In your Fastload script, try using the keyword INDICATORS in your define statement. Fastload has a problem handling nulls without this keyword. Here's an example:

LOGON ODBC/UserName/Password;

DEFINE Column1 (INTEGER)
FILE = C:\flat_file.txt;

BEGIN LOADING Database.Table
ERRORFILES Database.ErrorTable1, Database.ErrorTable2
INDICATORS /* <------------------------------------- RIGHT HERE */
CHECKPOINT 1000000;

INSERT INTO Database.Table VALUES
( :Column1);

END LOADING;
LOGOFF;

If that doesn't work, you could also try tacking on a ZEROIFNULL command around the column when you import it to the flatfile, then Use NULLIFZERO in your insert statement (This would only work however if the column in the base table didn't have any 0's to begin with).

Enthusiast

Re: teardata fastload and null values.....

Hi,
There is no way you can pass a string of NULLs to or in a file. The point here is that the NULLs get converted to blank spaces. In your Mulitload script, you can use the CASE statement as follows to convert the spaces back to NULL values
CASE WHEN :val = ' ' THEN NULL ELSE :val END
This will do the appropriate conversion. Use this only if your source field does not have blank spaces as a possibility. You can specify 1, 2, 3 or more blank spaces to exactly recognise a blank space and a NULL conversion field as you deem appropriate.