Home » RDBMS Server » Server Utilities » Problem whit nulls and sql loader (SQl loader)
Problem whit nulls and sql loader [message #309375] Thu, 27 March 2008 04:43 Go to next message
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 Sad
Re: Problem whit nulls and sql loader [message #309406 is a reply to message #309375] Thu, 27 March 2008 06:11 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It should be
WHEN cod_modo_pago = BLANKS


Here is SQL*Loader documentation; perhaps you'd want to read it in order to learn how to properly use its capabilities.
Re: Problem whit nulls and sql loader [message #309410 is a reply to message #309406] Thu, 27 March 2008 06:18 Go to previous messageGo to next message
Load
Messages: 3
Registered: March 2008
Location: Spain
Junior Member

Blanks is not valid , thanks for the help

I don´t know why, but if I put when cod_modo_pago !='' the loader discard the records that have this field with null value but if i put cod_modo_pago = '' don´t pass anything.

Sad

I try to make a decode in the when condition or put is null or other functions but the result is the same , error Sad
Re: Problem whit nulls and sql loader [message #309415 is a reply to message #309410] Thu, 27 March 2008 06:33 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: Problem whit nulls and sql loader [message #309433 is a reply to message #309375] Thu, 27 March 2008 07:26 Go to previous message
Load
Messages: 3
Registered: March 2008
Location: Spain
Junior Member

I make a second test with blanks and this time it works.

Thanks i supouse that i make errors when i make the ftp to the unix machine

Thanks!!!!

a lot of thanks!!!
Previous Topic: 9i data import to 10g
Next Topic: Commit point reached - logical record count 64
Goto Forum:
  


Current Time: Tue Dec 06 12:19:43 CST 2016

Total time taken to generate the page: 0.14228 seconds