Home » RDBMS Server » Server Utilities » Command for sql*loader to merge columns' values into new column (SQL*Loader: Release 10.1.0.4.0 / Oracle10g)
icon9.gif  Command for sql*loader to merge columns' values into new column [message #384848] Thu, 05 February 2009 07:40 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20894
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>
icon7.gif  Re: Command for sql*loader to merge columns' values into new column [message #385125 is a reply to message #384935] Fri, 06 February 2009 09:00 Go to previous message
Dreamsie
Messages: 2
Registered: February 2009
Junior Member
Thanks so much, I'll try that!
Previous Topic: Loading date into table with sqlldr (merged)
Next Topic: import error 00017,imp-00003
Goto Forum:
  


Current Time: Mon Dec 05 12:41:48 CST 2016

Total time taken to generate the page: 0.10614 seconds