SQL*Loader and NULL Numbers

[THoey]

I have a couple of new tables\data loads from an external system. I built the tables and wrote the scripts for SQL*Loader to perform nightly loads. After the data is loaded, several other queries are run to "massage" some of this new data. We just recently discovered that most of these massaged values were incorrect, due to a couple of fields that were supposed to have a numeric value being NULL.

I have set the default value for these fields to 0 (zero), so I didn't think this would be a problem, but it seems as is SQL*Loader doesn't care and makes it NULL.

Any ideas on how to make a field equal 0 (zero) when SQL*Loader reads a NULL? Terry M. Hoey

 

[rcurva]

Have you tried something like this in your control file?

col1 DEFAULTIF (col1 = BLANKS)
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[THoey]

What I ended up doing was the following for each numeric field in the SQL*Loader control file:

Fld1 position(1:2) INTEGER EXTERNAL "DECODEFld1, NULL, 0, :Fld1)",

I know this runs the DECODE function for each field and for each record, so any other ideas would be appreciated.

As for the DEFAULTIF, I don't see any documentation on it. Anyone know how it works in SQL*Loader? Terry M. Hoey

 

[jee]

The DEFAULTIF function is documented in the Oracle Utilities Manual. If you don't have a copy and you don't have an Oracle documentation CD, you can access the manuals online at:

http://otn.oracle.com/docs/products/oracle8i/content.html

 

[srishan]

DECODEFld1, NULL, 0, :Fld1)" Should work fine. I use SQL functions with in SQL Loader all the time with out any performance problems.

 

[eliassonsr]

Use NVLfield1,' ') function in the control file.
This will return the field1 value or space is a null value is found.

Example:
.
.
.
REPLACE
INTO TABLE test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
name, CHAR "NVLname,' ')",
phone CHAR "NVLdesc1,' ')"
)