 Command for sql*loader to merge columns' values into new column [message #384848] |
Thu, 05 February 2009 07:40  |
Dreamsie
Messages: 2
Registered: February 2009
|
Junior Member |
|
|
Hi guys,
I am trying to manipulate data while loading a flat file, where client id is spread through 3 non-consequent text columns. As there are 2 flat files delivered from different systems to be reconciled, I have to get the client id into 1 new column that I can then match on.
As I have no access to development environment, I can only put a one-line string as a control interface function. Here's an example of such a function: "DECODE(:UDF_01,'small','big,:UDF_01)".
Here's how my data looks like:
UDF_01 UDF_02 UDF_03 UDF_04 UDF_05 UDF_06
MW 20090203 PO07 847ZH BR18778 target new column
HTD 20080618 FR956 75BE BR8761
UDF_01, 03 & 05 together are the client id.
What I tried so far was: "UPDATE :UDF_06 SET :UDF_06=:UDF_01+:UDF_03+:UDF_05" (returns a 'missing expression' error). As I'm not an IT specialist, I'm totally lost and don't know what to try... Will be very thankful for any help!
|
|
|
|
| Re: Command for sql*loader to merge columns' values into new column [message #384935 is a reply to message #384848] |
Thu, 05 February 2009 15:23  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I guess that you should substitute the + sign with double pipe (||) (a concatenation operator):SQL> CREATE TABLE test (
2 udf_01 VARCHAR2(3),
3 udf_02 NUMBER,
4 udf_03 VARCHAR2(5),
5 udf_04 VARCHAR2(5),
6 udf_05 VARCHAR2(10),
7 client_id VARCHAR2(30)
8 );
Table created.
A control file:
SQL> $type test.ctl
load data
infile *
replace
into table test
fields terminated by ','
trailing nullcols
( udf_01,
udf_02,
udf_03,
udf_04,
udf_05,
client_id ":udf_01 || :udf_03 || :udf_05"
)
begindata
MW,20090203,PO07,847ZH,BR18778
HTD,20080618,FR956,75BE,BR8761
Run and test:SQL> $sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Vel 5 22:20:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
SQL> select * From test;
UDF UDF_02 UDF_0 UDF_0 UDF_05 CLIENT_ID
--- ---------- ----- ----- ---------- ------------------------------
MW 20090203 PO07 847ZH BR18778 MWPO07BR18778
HTD 20080618 FR956 75BE BR8761 HTDFR956BR8761
SQL> |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
