Re: sql loader --Need Help

In article <869.990795302194_at_iw0.mailusenet.com>, anuragmin says...
>
>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
>

it is comparing "NA " to "NA". You only want to compare two bytes -- not all of them (or add blanks to the "NA" string)

actualus position(66:85) char NULLIF ((66:67) = "NA") "TO_NUMBER(:actualus,'999,999.9999999')")

will work -- just compare 66:67

load data
infile *
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 ((66:67) = "NA") "TO_NUMBER(:actualus,'999,999.9999999')") begindata

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

>
>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!

Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Tue May 29 2001 - 15:37:38 CDT