sql loader --Need Help

From: anuragmin <anurag_minocha_at_hotmail.com>
Date: Tue, 29 May 2001 12:48:42 -0700 (PDT)
Message-ID: <869.990795302194@iw0.mailusenet.com>

Hi,
I need to import a file every day with 6-7 million records using sql loader. two columns in the file contain numeric data in the form of 999,99 and if no data is available contains a NA. Please note that NA is different from a 0 here.

I need to import the data in such a way that the formatted 999,99.9999 is imported as 99999.99 and zero's are imported as it is but I need to insert a null if NA is present in the column. I am using the to_number to convert 999,99.99 to a number but the problem is the moment it encounters a NA it gives a invalid number error.

Please find below the control file , a sample data file and the error I am getting. Please suggest a workaround as this needs to be a scheduled process and any manual intervention is not possible to replace NA with a null. I tried all possible altrernatives using translate, decode, replace etc but without any success.

Please Help.
Thanks
Anurag
aminocha_at_herold.com

Control File

load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dcs'
truncate
into table test
(company position(01:10) char,

time position(11:20) char,
region position(21:30) char,
lineitem position(31:45) char,
actual position(46:65) char NULLIF (actual = "NA") "TO_NUMBER(:actual,'999,999.999999')", actualus position(66:85) char NULLIF (actual = "NA") "TO_NUMBER(:actualus,'999,999.9999999')")

Sample Data

MBOC YR_1991 REGROLL BSPSIEB 1.000000 1.000000 
ALLEP YR_1991 REGROLL COFYE 24.000000 24.000000 
ALLEP YR_1991 REGROLL COLOP 48.000000 48.000000 
ALLEP YR_1991 REGROLL COEPAM 8.000000 8.000000

ALLEP YR_1991 REGROLL COCAI 5.000000 5.000000 ALLEP YR_1991 REGROLL COTWNE 9,274.000000 9,274.000000
ALLEP YR_1991 REGROLL ISHDR 1.000000 NA Error while importing the last record in the log file

Column ACTUAL is NULL if ACTUAL = 0X4e41
(character 'NA')

Column ACTUAL had SQL string
"TO_NUMBER(:actual,'999,999.999999')"
applied to it.
Column ACTUALUS is NULL if ACTUAL = 0X4e41
(character 'NA')

Column ACTUALUS had SQL string
"TO_NUMBER(nvl(:actualus,0),'999,999.9999999')" applied to it.

Record 8: Rejected - Error on table TEST, column ACTUALUS.
ORA-01722: invalid number

Posted via http://www.etin.com - the FREE public USENET portal on the Web Complete SEARCHING, BROWSING, and POSTING of text and BINARY messages! Received on Tue May 29 2001 - 14:48:42 CDT