sql*loader question

[Nicolas Dumeige]

Hi,

We're using sql*loader to load a file with 13 fileds delimited whit '|'.

We want to load all record that have the last field equal NULL, non caracter at all.
Problem is, there is no such keyword when using a WHEN CLAUSE.


LOAD DATA
REPLACE
INTO TABLE INTERFACE_FRONT_ECO_PREV
WHEN (1:7) != 'DEFFILE'
AND (1:7) != 'FTRFILE'
AND (1:6) != 'DEFLOT'
AND (1:6) != 'FTRLOT'
****** HERE ************
AND (13) = BLANKS
AND (13) = NULL
************************
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
IDENTIFIANT CHAR,
PERIOD DATE(8) "DDMMRRRR",
TYPE_PRESTATION CHAR,
DEVISE CHAR,
CODE_SEG CHAR,
CODE_STAT CHAR,
CODE_DEPARTEMENT CHAR,
SECT_GEO CHAR,
TYPE_CLIENT CHAR,
CANAL_RESA CHAR,
SOURCE_RESA CHAR,
VALEUR DECIMAL EXTERNAL,
SOURCE CONSTANT BOCOSAEXPBFRIJ09030625162920.TXT
)


Does anybody can provide some help,
thanks in advance,

[Pete Randall]

According to this:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x6fb8506d69a7d711abdc0090277a778c,00.html

the problem is solved.

[Yogeeraj_1]

hi,

Are you using Oracle 8i or 9i?

In 9iR1 and up with external tables you have the ability to query a flat file using SQL. I find the flexibility of SQL over SQLLDR control files to be so useful that I would opt for an external table over sqlldr in most all cases. Use SQLLDR when SQL cannot be used to accomplish the job.

e.g.
create or replace directory data_dir as '/tmp/'

create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by '|' )
location ('emp.dat')
)
/

In Oracle8i and SQLLoader:

Consider the following example:

My control file:
=====================================
LOAD DATA
INFILE *
INTO TABLE dept
append
FIELDS TERMINATED BY '|'
(
deptno position(001:002),
dname position(004:013),
loc position(014:023) "nvl(:loc,'XX')",
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH |DALLAS
30|SALES |RESTON
40|OPERATIONS|
=====================================

Just load the data, the last column having null value will be substituted by 'XX'.

$ sqlldr userid=yd/yd@dev control=yd.ctl log=mlog


SQL> select * from dept;

DEPTNO DNAME LOC BUILDING
---------- ------------------------------ ------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES RESTON
40 OPERATIONS XX




After that, i can just do a delete where loc = 'XX'

delete dept where loc=''XX';


This is the easiest way (brute force).

Will need to investigate for other possibilities.

Hope this helps
regards
Yogeeraj

[Yogeeraj_1]

sorry! did not notice that this problem was already solved ;)

cheers
Yogeeraj