?
Solved

Using NULLIF in SQLLoader

Posted on 1997-09-17
2
Medium Priority
?
3,348 Views
Last Modified: 2008-04-29
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?
0
Comment
Question by:meadj01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:meadj01
ID: 1080619
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
 

Accepted Solution

by:
fercho earned 200 total points
ID: 1080620
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question