Solved

Using NULLIF in SQLLoader

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

856 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