Home » RDBMS Server » Server Utilities » How to load two data columns into one column in table while loading using sqlloader (9i DB, OS Unix)
How to load two data columns into one column in table while loading using sqlloader [message #345059] Tue, 02 September 2008 04:05 Go to next message
jpeter
Messages: 3
Registered: September 2008
Junior Member
I have 3 columns in data file transaction code, debit amount and credit amt, Each transaction will either have debit amount or credit amount. I need to populate either dr/cr amount into single amount column in a table

6666,28.5,
7777,,32.6

Expected Records in table

Transaction Code : 6666
Amount :28.5

Transaction Code : 7777
Amount :32.6

Thanks in Adv
John
Re: How to load two data columns into one column in table while loading using sqlloader [message #345075 is a reply to message #345059] Tue, 02 September 2008 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use WHEN clause to insert one field or the other depending if col2 is null or not.
See ulcase5.ctl in your ORACLE_HOME/rdbms/demo directory.

Regards
Michel

Re: How to load two data columns into one column in table while loading using sqlloader [message #345084 is a reply to message #345075] Tue, 02 September 2008 04:56 Go to previous messageGo to next message
jpeter
Messages: 3
Registered: September 2008
Junior Member
Hi Michel,

Thanks for your prompt reply

How does this work for comma separated data file?

Regards,
John
Re: How to load two data columns into one column in table while loading using sqlloader [message #345106 is a reply to message #345084] Tue, 02 September 2008 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: How to load two data columns into one column in table while loading using sqlloader [message #345111 is a reply to message #345106] Tue, 02 September 2008 06:07 Go to previous messageGo to next message
jpeter
Messages: 3
Registered: September 2008
Junior Member
Hi,

I was attempting the following way,

LOAD DATA
INFILE *
INTO TABLE tab1
WHEN col2 = ''
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
( col1,
col2
)
INTO TABLE tab1
WHEN col2 != ''
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
( col1,
col2 FILLER INTEGER,
col2
)
BEGINDATA
21,211111,
22, ,211111
31,311111,
32, ,311111


but it errors out saying.
SQL*Loader-350: Syntax error at line 13.
Expecting "," or ")", found "FILLER".
col2 FILLER INTEGER,
^

Thanks,
John
Re: How to load two data columns into one column in table while loading using sqlloader [message #345130 is a reply to message #345111] Tue, 02 September 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
when col2 is null
when col2 is not null
if you use empty string.
or there is a space missing in your string constant ''.
In addition, I think your test is the opposite one and don't use the same column name in the second case. Also why not FILLER CHAR?

Use code tags to post your control file (see ./fa/4938/0/ button) otherwise space are unreable.

Regards
Michel

Re: How to load two data columns into one column in table while loading using sqlloader [message #345236 is a reply to message #345130] Tue, 02 September 2008 12:52 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- test.ctl:
LOAD DATA
INFILE  *
INTO TABLE tab1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(code,
debit   BOUNDFILLER,
credit  BOUNDFILLER,
amount  "GREATEST (NVL (TRIM (:debit), 0), NVL (TRIM (:credit), 0))")
BEGINDATA
21,211111,
22, ,211111
31,311111,
32, ,311111
6666,28.5,
7777,,32.6


SCOTT@orcl_11g> CREATE TABLE tab1
  2    (code	NUMBER,
  3  	amount	NUMBER)
  4  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM tab1
  2  /

      CODE     AMOUNT
---------- ----------
        21     211111
        22     211111
        31     311111
        32     311111
      6666       28.5
      7777       32.6

6 rows selected.

SCOTT@orcl_11g>

Previous Topic: Getting export error ORA-39212
Next Topic: imp
Goto Forum:
  


Current Time: Thu Dec 08 04:17:00 CST 2016

Total time taken to generate the page: 0.14627 seconds