Home » RDBMS Server » Server Utilities » SQLLoader: Condition on field [country Like '%String%'] (oracle 10g)
| SQLLoader: Condition on field [country Like '%String%'] [message #501242] |
Mon, 28 March 2011 02:48  |
 |
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
Hello all,
This is my first message on this forum, so 'ill try be as clear as i can.
I have a CSV file, aznd i try to create an oracle table that contains all those information.
I have no problem to save everything.
The problem is when i want to add a condition on the inertions.
The csv file, contains a list of buildings with their geographical information.
I have a column containing the country where is located the building, and i would like to insert in the DB only the buildings located in France.
i've done this:
LOAD DATA
INFILE blabla
INTO TABLE LIST
WHEN country = 'FRANCE'
( BLABLA
country TERMINATED BY ';'
BLABLA
)
The problem is that the column containing the country is 64 characters long....
And that it appears SQLLoader can't do any condition like "LIKE" in SQL: WHEN country = 'FRANCE%'
I found a way to do it, with the position of the String in order to do it:
LOAD DATA
INFILE blabla
INTO TABLE LIST
WHEN (124;130) = 'FRANCE'
( BLABLA
country TERMINATED BY ';'
BLABLA
)
this works but it's really bothering... I would really like to refer to the field directly.
Imagine if one of the field before changes size....
Another solution would be to declare the column country as 2 column;
LOAD DATA
INFILE blabla
INTO TABLE LIST
WHEN (124;130) = 'FRANCE'
( BLABLA
country TERMINATED BLANK,
ignore TERMINATED BY ';' FILLER,
BLABLA
)
But, this could cause problem if one day i want to use 'UNITED STATES'
Remark: Even if i trim the value of the field country, it is indeed saved without the blanks, But the "compare" takes place before the trimming so the condition isn't validated...
Anyway, my question is just to know if there is a clean way, to look for a certain String in a field? (Or an equivalent to startsWith() function)
Thanks a lot, and have a nice day.
James
PS: As you may noticed, i just summarized the problem, so if you see a mistake in the code, ignore it; thanks
[Updated on: Mon, 28 March 2011 02:58] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQLLoader: Condition on field [country Like '%String%'] [message #501254 is a reply to message #501253] |
Mon, 28 March 2011 04:09  |
|
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
Michel, in your last message this would be an SQL command to insert from a table to another.
I understand that external tables are interesting but that's not really what i wanted to ask.... (sorry)
I wanted to know, if in a CTL file, you could add a condition on the presence of a certain String.
From what i tried, you can search for an exact String, but not on the presence of a String in a field. Is that so?
I know SQL loader has limitations, but is it possible or not?
Maybe i'm not clear....
For the moment, i'm doing this:
LOAD DATA
INFILE blabla
INTO TABLE LIST
WHEN (124;130) = 'FRANCE'
( BLABLA
country TERMINATED BY ';'
BLABLA
)
Is it possible to do something like:
LOAD DATA
INFILE blabla
INTO TABLE LIST
WHEN (124;130) = 'FRANCE%'
( BLABLA
country TERMINATED BY ';'
BLABLA
)
if yes: how? (Since '%' doesn't seem to work.
if not, well it doesn't matter...
Thanks again to all of you. And sorry if i can't really explain myself clearly...
[Updated on: Mon, 28 March 2011 04:09] Report message to a moderator |
|
|
|
|
|
| Re: SQLLoader: Condition on field [country Like '%String%'] [message #501262 is a reply to message #501259] |
Mon, 28 March 2011 04:39 |
|
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
Thanks Littlefoot
I thought there would be something like
"When field = "StringBLANKS" or something else but....
Anyway it doesn't matter, i'll juste keep on using the positions of the string.
It's funny, because in a CTL file, you can use some SQL functions to change the value of a field before inserting it in a table.
For exemple, i can trim the field country of my CSV, so that the field only contains the country without the 60 blanks in the right side.
I guess it's strange you can't use the same funtions in the inserting conditions....
Thanks a lot to everyone!
[Updated on: Mon, 28 March 2011 04:39] Report message to a moderator |
|
|
|
|
|
| Re: SQLLoader: Condition on field [country Like '%String%'] [message #501287 is a reply to message #501271] |
Mon, 28 March 2011 07:17 |
|
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
I guess....
But there still are new versions of SQLLoader right? updates/releases?
What do you think of the use of a before-insert trigger?
Depending on the value of a field, there would be an insert or not?
This, however, seems greedy isn't it?
Regards
Ben
Ps: In the forum, is there a "solved" state for the messages that i have to click to say that i had the answer to my question?
[Updated on: Mon, 28 March 2011 07:18] Report message to a moderator |
|
|
|
| Re: SQLLoader: Condition on field [country Like '%String%'] [message #501296 is a reply to message #501287] |
Mon, 28 March 2011 07:39 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:But there still are new versions of SQLLoader right?
Just to support some new rdbms features not to extend the current sql*loader one.
They are maintenance releases. No new features are added.
Quote:What do you think of the use of a before-insert trigger?
Depending on the value of a field, there would be an insert or not?
It is useful in the case where it is appropriate.
Quote:This, however, seems greedy isn't it?
Why? I don't understand your point.
Quote:Ps: In the forum, is there a "solved" state for the messages that i have to click to say that i had the answer to my question?
There is no such thing. Topic is not owned by anybody, everyone can add an answer if it is useful.
But anyway, always feedback to say that your question is solved, it prevents from people continuing to post trying to solve something that is solved.
Regards
Michel
|
|
|
|
|
|
| Re: SQLLoader: Condition on field [country Like '%String%'] [message #501362 is a reply to message #501320] |
Mon, 28 March 2011 12:57 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Either there is a difference between 10g and 11g or your blanks are not chr(32) spaces, since it works for me as shown below.
LOAD DATA
INFILE *
INTO TABLE list
WHEN country = 'FRANCE'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(col1,
country,
col3)
begindata:
1;FRANCE ;2
3;UNITED STATE ;4
SCOTT@orcl_11gR2> CREATE TABLE list
2 (col1 NUMBER,
3 country VARCHAR2 (15),
4 col3 NUMBER)
5 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT list.*, LENGTH (country), DUMP (country) FROM list
2 /
COL1 COUNTRY COL3 LENGTH(COUNTRY)
---------- --------------- ---------- ---------------
DUMP(COUNTRY)
--------------------------------------------------------------------------------
1 FRANCE 2 15
Typ=1 Len=15: 70,82,65,78,67,69,32,32,32,32,32,32,32,32,32
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: SQLLoader: Condition on field [country Like '%String%'] [message #501367 is a reply to message #501362] |
Mon, 28 March 2011 13:05 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In the following example, I substituted tabs for spaces. I specified the country field as terminated by X'09' which is the hexadecimal representation of tab, then added a filler field terminated by ';' after that, to allow for any remaining tabs, spaces, or characters in the country field. Without those, it did not treat the tabs as spaces and did not load any rows. I suspect this is similar to what you have. You may need to do a dump or copy and paste of your whitespace to see what characters you have.
LOAD DATA
INFILE *
INTO TABLE list
WHEN country = 'FRANCE'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(col1,
country TERMINATED BY X'09',
fill1 FILLER,
col3)
begindata:
1;FRANCE ;2
3;UNITED STATES ;4
SCOTT@orcl_11gR2> CREATE TABLE list
2 (col1 NUMBER,
3 country VARCHAR2 (15),
4 col3 NUMBER)
5 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT list.*, LENGTH (country), DUMP (country) FROM list
2 /
COL1 COUNTRY COL3 LENGTH(COUNTRY)
---------- --------------- ---------- ---------------
DUMP(COUNTRY)
--------------------------------------------------------------------------------
1 FRANCE 2 6
Typ=1 Len=6: 70,82,65,78,67,69
1 row selected.
SCOTT@orcl_11gR2>
[Updated on: Mon, 28 March 2011 13:06] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 12:25:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
