Elevation position integer external defaultif elevation

	,ch04.24702 Page 94 Wednesday, April 11, 2001 1:44 PM
	94

Is there any way to use DEFAULTIF to make SQL*Loader treat blank numeric fields as if they were zeros? There is, but it’s not a good solution. You can use PRESERVE BLANKS to preserve whitespace in all your fields. Whitespace won’t be trimmed, and assuming you don’t use a NULLIF clause, your DEFAULTIF clause will be evaluated. For example:

LOAD DATA
INFILE 'alger_nulltest.dat'
REPLACE PRESERVE BLANKS
INTO TABLE michigan_features
(
feature_name POSITION(4) CHAR(44),
short_feature_name POSITION(4) CHAR(10),
elevation POSITION(49)
INTEGER EXTERNAL(4)
DEFAULTIF elevation=BLANKS, feature_type POSITION(54) CHAR(9),
county POSITION(64) CHAR(5)
)

This is the Title of the Book, eMatter Edition

		95
		95

Dealing with “Short” Records

If the record ends in the middle of a field, that field’s value is based on whatever characters are present in the record for that field. The field’s length also corre-sponds to whatever characters are present. This has an interesting implication when you are loading character fields and using the PRESERVE BLANKS clause. When you use PRESERVE BLANKS, trailing blanks are normally preserved, and the length of the values loaded into the database is equal to the maximum field size.

	Load a CHAR(5) field using PRESERVE BLANKS, and every row loaded into your
	database will have a 5-byte long value. The one exception is—you guessed it—
	when the record is truncated in the midst of such a field. SQL*Loader won’t add
	the necessary spaces to bring the field length up to the maximum size.

	This is the Title of the Book, eMatter Edition
	Copyright © 2001 O’Reilly & Associates, Inc. All rights reserved.