OraFAQ Forum: Server Utilities » CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS

Home » RDBMS Server » Server Utilities » CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS (SQL LOADER)

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

CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467715]

Mon, 26 July 2010 20:17

omcolorado
Messages: 2
Registered: July 2010
Location: El Salvador

Junior Member

Hi i can not English but I need your help urgently want to load a delimited file that contains many records which contained within the table where I'm going to load a date type field and I need to do this by concatenating three fields
field1 = 1 - this is the day
field2 = 11 - this corresponds to the month
field3 = 5 - this corresponds to the year

I need is in the field Save as type date 01/11/2005 i don´t know how to do it but I tried as follows but I get error loading.

"TO_DATE (TO_CHAR (: field1 ||'/'|| DECODE (: field2, 1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR', 5, 'MAY', 6 , 'JUN', 7, 'JUL', 8, 'AUG', 9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC': field2 )||'/'|| : field3)) "

I would appreciate your help.

Report message to a moderator

Re: CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467716 is a reply to message #467715]

Mon, 26 July 2010 20:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

While I kind of understand what you need, I need to see about a half dozen (4-8) sample records to see actual raw data & know which date they represent.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Post results of
SELECT * from v$version;

Consider using EXTERNAL TABLE instead of SQLLOADER

[Updated on: Mon, 26 July 2010 20:46]

Report message to a moderator

Re: CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467733 is a reply to message #467715]

Tue, 27 July 2010 00:14

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

-- test.ctl:

load data
infile *
into table tabla
fields terminated by whitespace
trailing nullcols
(dia boundfiller,
mes boundfiller,
ano boundfiller,
fecha expression "to_date ((:dia || '/' || :mes || '/' || (2000 + :ano)), 'dd/mm/yyyy')")
begindata:
1 11 5

SCOTT@orcl_11gR2> create table tabla
  2    (fecha date)
  3  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> alter session set nls_date_format = 'dd/mm/yyyy'
  2  /

Session altered.

SCOTT@orcl_11gR2> select * from tabla
  2  /

FECHA
----------
01/11/2005

SCOTT@orcl_11gR2>

Report message to a moderator

Re: CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467858 is a reply to message #467733]

Tue, 27 July 2010 15:53

omcolorado
Messages: 2
Registered: July 2010
Location: El Salvador

Junior Member

Gracias muy agradecido

Report message to a moderator

Previous Topic:	Oracle Data Pump import error.
Next Topic:	Export Error.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 14:35:39 CST 2024