Solved

Using NULLIF in SQLLoader

Posted on 1997-09-17
2
3,340 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 50 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 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