| SQL Loader - How to combine 2 fields ? [message #70444] |
Fri, 14 June 2002 06:42  |
Fabio Felipe
Messages: 3
Registered: June 2002
|
Junior Member |
|
|
Hi,
I am trying to combine entry_date (YYYYMMDD) with entry_time (hh24miss) and load the result into the column entry_date datatype "date", but the script below is not working.
Does anybody have any idea how I can do it ?
Thanks
-Fabio
load data
infile '${INPUT_FILE}'
truncate into table par_order
(
INDICATOR position(001:001) char,
ENTRY_DATE position(027:034) char "to_date(:ORDER_ENTRY_DATE||:ORDER_ENTRY_TIME),'YYYYMMDDHH24MISS')",
ALLOC_NBR position(035:035) char,
FIRM_ACR position(036:039) char,
ENTRY_TIME filler position(040:045) char
)
|
|
|
|
| Re: SQL Loader - How to combine 2 fields ? [message #70448 is a reply to message #70444] |
Fri, 14 June 2002 09:34  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Oracle has already defined this case.
from docs
sample data fileM
18-May-1999,15:05:59,334
11-Jun-1999,10:10:33,206
10-Aug-1999,14:39:46,336
10-Aug-1999,14:39:46,335
10-Aug-1999,9:41:51,207
15-Sep-1999,16:21:15,208
Table description:
Name Type
-------------------- -------
Test_ID Number(4)
Entry_Date Date
Entry_Time Varchar2(10)
LOAD DATA
INFILE *
Append
INTO TABLE Test_datetime
FIELDS TERMINATED BY ','
(
ENTRY_DATE char "TO_DATE(:ENTRY_DATE||:ENTRY_TIME, 'DD-MON-YYYYHH24:MI:SS')",
ENTRY_TIME CHAR "DECODE(:ENTRY_TIME,'','',NULL)",
Test_ID
)
BEGINDATA
18-May-1999,15:05:59,334
11-Jun-1999,10:10:33,206
10-Aug-1999,14:39:46,336
10-Aug-1999,14:39:46,335
10-Aug-1999,9:41:51,207
15-Sep-1999,16:21:15,208
where table column ENTRY_DATE is of datatype DATE and table column ENTRY_TIME
is of datatype VARCHAR2.
|
|
|
|
| Re: SQL Loader - How to combine 2 fields ? [message #70451 is a reply to message #70448] |
Fri, 14 June 2002 13:46 |
Fabio Felipe
Messages: 3
Registered: June 2002
|
Junior Member |
|
|
I am using 8.1.6
SqlLoader does not give any error message, it simple does not load the record.
If I remove the "FILLER" and create a column for ENTRY_TIME, then it works fine.
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table TEMP_PAR_ORDER:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
|
|
|
|
| Re: SQL Loader - How to combine 2 fields ? [message #70459 is a reply to message #70448] |
Mon, 17 June 2002 06:24 |
Fabio Felipe
Messages: 3
Registered: June 2002
|
Junior Member |
|
|
I am doing pretty much what you mentioned, combining the columns, but I am leaving the unwanted column there. It is not a big deal for my processing.
Thank You very much for your help.
By the way, I noticed you reply to most of the emails. Do you work replying to the emails or you do that just for fun ?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
