Solved

Using NULLIF in SQLLoader

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Linked Server Using Oracle OPS$ Windows Authenticated Account 7 59
Oracle - Create Procedure with Paramater 16 56
Oracle SQL 6 48
Oracle Pivot Question 8 45
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now