Re: SQLLOADER trick

FC wrote:

> Folks,
> I've got the following problem in a SQL loader script (Oracle PE on Win 98
> 8.1.7.0.0 and EE 8.1.7.0.0 on Win 2K) :
>
> given the following source line
>
> 200203120101002000000400000101056395000000000000814030002W635
> 0000023053000028310
>
> the statement
>
> POS POSITION(52:53) INTEGER EXTERNAL "GREATEST(TO_NUMBER(:POS,
> '99'), 1)"
>
> returns 3 (correct answer, from my viewpoint)
> while
>
> POS POSITION(52:53) INTEGER EXTERNAL "GREATEST(:POS, 1)"
>
> returns 1 (wrong answer, from my viewpoint)
>
> Column POS is defined as a number in my table.
>
> Well, the trick lies either in the implicit type conversion performed by
> function GREATEST or in the missing type conversion performed by SQLloader
> on the data being read, whichever comes first.
>
> Even if you declare the field of type integer, it is still converting the
> second number into a string, rather than converting :POS to a number, unless
> you explicitly force the conversion using the function TO_NUMBER.
>
> In other words, be careful when converting between data types on the fly,
> you could end up spending hours trying to figure out why you are getting
> such funny results.
>
> Flavio

Never do implicit conversions.

Unless you like surprises.

Daniel Morgan Received on Tue May 21 2002 - 17:24:48 CDT