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 |
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 #345111 is a reply to message #345106] |
Tue, 02 September 2008 06:07 |
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 #345236 is a reply to message #345130] |
Tue, 02 September 2008 12:52 |
|
Barbara Boehmer
Messages: 9101 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>
|
|
|
Goto Forum:
Current Time: Tue Dec 24 10:26:15 CST 2024
|