Using NULLIF in SQLLoader

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 sqlloader '.ctl' file like this:

cls     POSITION(*)     INTEGER EXTERNAL NULLIF (cls NOT BETWEEN 0 AND 5),

I get the error:

SQL*Loader-350: Syntax error at line 10.
Expecting = or "<>", found "NOT".
cls     POSITION(*)     INTEGER EXTERNAL NULLIF (cls NOT BETWEEN 0 AND 5),

Does anyone see the problem here?
meadj01Asked:
Who is Participating?
 
ferchoConnect With a Mentor Commented:
You can't do that with the NULLIF clause (see the limitations
on the syntax in the Utilities Guide). Use a SQL expression
like:
     cls INTEGER EXTERNAL "decode(:cls,'0',0,'1',1,'2',2,'3',3,
                                  '4',4,'5',5,null)"
I know, it's cumbersome. Another solution is to make a function
like:

create fucntion cls_value(cls in varchar2) return number
is
begin
  if cls between '1' and '5' then
     return to_number(cls);
  else
     return null;
  end if;
end;

Then in .ctl file:
    cls INTEGER EXTERNAL "cls_value(:cls)"


0
 
meadj01Author Commented:
Now I got it to work, but it only tests one number, not a range between 0 and 5.
Here is the SQL*Loader line:

cls     INTEGER EXTERNAL NULLIF (cls != "5"),

Can I do ranges in that test?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.