Re: Trim number fields as well as blank in SQL LOADER

"Val" <vegas_girlie_at_hotmail.com> schrieb im Newsbeitrag news:7d04f924.0307090542.50baaffa_at_posting.google.com...
> Hi -
> I have a problem in SQL LOADER where a NUMBER field is coming in with
> leading blanks or all blanks. how can i format it in the control file
> to either take the blanks or trim the value. A sample of the leading
> blanks is "| 2003" (Without the quotes) or simple with spaces like
> "| ". is there a way to specify the condtion where it would either
> trim or get rid of the leading spaces. I have tried putting both the
> LTRIM function as well the NULLLIF function but I still get an error
> message saying that it was an ivalid number.
>
> This is what I tried...
> FIELD_A NULLIF FIELD_A=BLANKS "LTRIM(:FIELD_A)"
> Thanks in advance!

Hi Val,

if I understand You correct, You have blank-seperated fields in Your input file.
This can be a problem when you have Null values - how to distinct them from the delimiters ...
You may better use semikolons as delimiters, if you can change it ...

Or You may change your ctl into something like this:

LOAD DATA
INFILE 'myfile.txt'
replace
INTO TABLE mytable(

 col1  POSITION (  1:  5),
 col2  POSITION (  6: 10),
 col3  POSITION ( 11: 34),

On the other hand,"invalid number" indicates that you try to enter characters into a number field,
which is of course not allowed. Check the Loader Log on which data row the error occurred.
I had "invalid number" today, too, and just had to I eliminate a file header from the input file.

hth, Jan Received on Thu Jul 10 2003 - 13:16:48 CEST