Home » RDBMS Server » Server Utilities » SQL*LOAD - manipulating data
SQL*LOAD - manipulating data [message #164016] |
Tue, 21 March 2006 05:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pday@tullib.com
Messages: 11 Registered: March 2006 Location: London
|
Junior Member |
|
|
HI
Have a problem with loader that I assume should be easy to sort but can't seem to find any reference for it.
My table has say 5 col's A,B,C,D,E
My Data file that I'm trying to load has 3 col's A,C,E and is comma delimeted
What I want to see is col's B and D being loaded with data derived from col A.
col A contains data in the format 123456/654321
Everything before the / is col B and everything after is col D
I've worked out the sql to manipulate the data and this works fine is sqlplus
for col B
SUBSTR(A,1,INSTR(A,'/')-1)
for D
SUBSTR(A,INSTR(A,'/')+1,LENGTH(A))
My current ctl file looks like this:
load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E)
I have tried adding the following (with various combinations quotes) but with no sucess.
load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
(A,
B SUBSTR(:A,1,INSTR(:A,'/')-1),
C 'DD/MON/YYYY HH24:MI',
D SUBSTR(:A,INSTR(:A,'/')+1,LENGTH(:A))
E)
any help would be appreciated.
thanks
|
|
|
Re: SQL*LOAD - manipulating data [message #164021 is a reply to message #164016] |
Tue, 21 March 2006 06:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pday@tullib.com
Messages: 11 Registered: March 2006 Location: London
|
Junior Member |
|
|
I've just found a ref to the keywork 'EXPRESSION'
I've tried the following:
.....
B EXPRESSION "SUBSTR(:A,1,INSTR(:A,'/')-1)",
....
but get syntax error
Expecting "," or ")", found "EXPRESSION".
thanks
|
|
|
Re: SQL*LOAD - manipulating data [message #164064 is a reply to message #164021] |
Tue, 21 March 2006 10:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Columns in the control file need to be in the same order as values in the csv file. Any columns that are calculated based on other columns need to go at the bottom. You also need to include trailing nullcols, so that it does not stop looking for column definitions after the end of the physical record. I also added date in front of your date format.
load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E,
B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
D "SUBSTR(:A,INSTR(:A,'/')+1)")
|
|
|
Re: SQL*LOAD - manipulating data [message #164067 is a reply to message #164016] |
Tue, 21 March 2006 10:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pday@tullib.com
Messages: 11 Registered: March 2006 Location: London
|
Junior Member |
|
|
many thanks - that worked a treat.
One more question if I may, the last step I need to do update one more col, this time using data from another database table but using the field B as the primary key.
I have tried the following:
load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E,
B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
D "SUBSTR(:A,INSTR(:A,'/')+1)"),
F "select tag from tag_tab tt where tt.tag = "SUBSTR(:A,1,INSTR(:A,'/')-1)"
I have also tried
F "select tag from tag_tab tt where tt.tag = :B"
I get the error missing expression.
thanks in advance.
|
|
|
Re: SQL*LOAD - manipulating data [message #164098 is a reply to message #164067] |
Tue, 21 March 2006 16:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can create a function to select the data, then use that function in your control file. Since you are selecting the same thing that you are comparing to, it appears you are attempting to do some sort of validation. If there is no match (no_data_found exception) then you will need to decide how you want to handle that. In the example below, I just returned null. If the column you are attempting to insert the validated tag into, has a not null constraint, then the insert of that row will be rejected and go into your bad file, which I assume is the desired result.
create or replace function validate_tag
(p_tag in number)
return number
as
v_tag number;
begin
select tag
into v_tag
from tag_tab
where tag = p_tag;
return v_tag;
exception
when no_data_found then
return null;
end validate_tag;
/
load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E,
B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
D "SUBSTR(:A,INSTR(:A,'/')+1)",
F "validate_tag (SUBSTR(:A,1,INSTR(:A,'/')-1))")
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 06:03:15 CDT 2024
|