Home » RDBMS Server » Server Utilities » CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS (SQL LOADER)
CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467715] Mon, 26 July 2010 20:17 Go to next message
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.
Re: CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467716 is a reply to message #467715] Mon, 26 July 2010 20:42 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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>

Re: CONCATENATE A DATE WITH SQL LOADER FROM THREE FIELDS [message #467858 is a reply to message #467733] Tue, 27 July 2010 15:53 Go to previous message
omcolorado
Messages: 2
Registered: July 2010
Location: El Salvador
Junior Member
Gracias muy agradecido
Previous Topic: Oracle Data Pump import error.
Next Topic: Export Error.
Goto Forum:
  


Current Time: Fri Mar 29 10:12:52 CDT 2024