| Problem whit nulls and sql loader [message #309375] |
Thu, 27 March 2008 04:43  |
Load
Messages: 3
Registered: March 2008
Location: Spain
|
Junior Member |
|
|
First sorry my english is not very good
I want to make the next sentence into file ctl of the loader
INTO TABLE MSISDNS
WHEN COD_MODO_PAGO = NULL
(
MSISDN POSITION(1:15) CHAR,
FEC_ALTA POSITION(16:34) CHAR
FEC_DESDE POSITION(35:53) CHAR,
COD_MODO_PAGO POSITION(54:55) CHAR,
COD_USUARIO CHAR "USER",
FEC_ULT_MOD CHAR "SYSDATE"
)
The problem is when i make COD_MODO_PAGO=NULL the loader no permit this sentence i try tu put COD_MODO_PAGO= ' ' OR COD_MODO_PAGO = BLANKS OR COD_MODO_PAGO= " "
but nothing the problem not solved,
I whant that only the record with this field (cod_modo_pago) is null, go to the database
Someone know how to make this?
Thanks
PD: Sorry but my english is very poor 
|
|
|
|
|
|
|
|
| Re: Problem whit nulls and sql loader [message #309415 is a reply to message #309410] |
Thu, 27 March 2008 06:33  |
 |
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
What do you mean by "blanks is not valid"?
Here's an example: first, contents of Scott's DEPT table:SQL> select * From dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
This is a control file; I want to load records whose 'loc' column is empty (it is the third one, "21,BI Team"):load data
infile *
append
into table dept
when loc = blanks
fields terminated by ','
(deptno, dname, loc)
begindata
1,BI Team, Zagreb
11,IT Team, Zagreb
21,BI Team,
22,IT Team,Split
Here's an excerpt of the log file:Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Table DEPT:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
3 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Finally, a new DEPT table:SQL> select * From dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
21 BI Team
SQL>
So, it DOES work in my example.
Could you produce your own one? Could you post it? If possible, base it on Scott's schema so that we could easily reproduce it. If not, post CREATE TABLE statement(s) as well as sample input data, control file and all other relevant information.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
