OraFAQ Forum: Server Utilities » How to load two data columns into one column in table while loading using sqlloader

Home » RDBMS Server » Server Utilities » How to load two data columns into one column in table while loading using sqlloader (9i DB, OS Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Michel Cadot
Messages: 68650
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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes.

Regards
Michel

Report message to a moderator

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

Report message to a moderator

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

Michel Cadot
Messages: 68650
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 /forum/fa/4938/0/

button) otherwise space are unreable.

Regards
Michel

Attachment: OrafaqCode.gif
(Size: 0.11KB, Downloaded 1226 times)

Report message to a moderator

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: 9092
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 13 00:27:31 CDT 2024