Re: Using NULLIF in SQL*Loader
Date: 1997/09/19
Message-ID: <3422e9c7.4855541_at_www.sigov.si>#1/1
On 17 Sep 97 16:16:30 -0500, "John-Paul" <meadj01_at_popmail.med.nyu.edu> wrote:
>I am trying to load an Oracle7 database with data from a text file.
>One of the columns contains either numerical or character data.
>I would like to ignore the character data, and only load the numerical data
>(between 0 and 5).
>I placed a statement in my SQL*Loader '.ctl' file like this:
>
>cls POSITION(*) INTEGER EXTERNAL NULLIF (cls NOT BETWEEN 0 AND 5),
>
>Does anyone see the problem here?
If you look in your Oracle Utilities manual you'll find that when using field conditions you can test only for equality or nonequalty, i.e. you can use only the = or != opeators. So neither NOT or BEETWEEN or any other operator is allowed in the NULLIF condition.
You can't use OR's in a condition or compare the field to a list of values (cls <> 0,1,2,3,4,5 !!???) as some others have suggested.
The most obvious soluttion of your problem is using DECODE inside sql string as Frank Hubeny allready sugested.
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Sep 19 1997 - 00:00:00 CEST