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 Go to next message
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 #501243 is a reply to message #501242] Mon, 28 March 2011 02:55 Go to previous messageGo to next message
Littlefoot
Messages: 21624
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hello, welcome to the OraFAQ Forum!

I wouldn't know; however, I'm not an expert (perhaps you should wait until Barbara wakes up /forum/fa/1774/0/ (she sleeps in the USA) (or someone else who might know-how)).

However: if possible, use external tables feature instead of SQL*Loader. Doing so, you'd be able to select any information you want, using pure SQL.
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501246 is a reply to message #501243] Mon, 28 March 2011 03:03 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
i thought so, but aren't external tables greedy in time treatment?
I guess it's a lot longer to look in a text file, than directly in a table.... the csv changes each day, so i'm affraid there would be a lot of treatments...

I wanted to do a fast sorting before inserting. (I thought it would be easy to just find a string in a field....)

Thanks for your fast reply.

[Updated on: Mon, 28 March 2011 03:04]

Report message to a moderator

Re: SQLLoader: Condition on field [country Like '%String%'] [message #501247 is a reply to message #501246] Mon, 28 March 2011 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but aren't external tables greedy in time treatment?

No, as fast as SQL*Loader and even faster when complex treatments are required.

Quote:
I wanted to do a fast sorting before inserting.

You can do while inserting with an external table.

Regards
Michel
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501249 is a reply to message #501247] Mon, 28 March 2011 03:27 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Thanks Michel. But concerning the sorting, is it possible to have the condition i described? => presence of String "France%" in a field?
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501250 is a reply to message #501249] Mon, 28 March 2011 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
All what is possible in SQL or PL/SQL is possible with external because you use SQL or PL/SQL to load an external table.

Regards
Michel
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501251 is a reply to message #501250] Mon, 28 March 2011 03:45 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Actually my original question was for a normal insertion with SQLLoader via a CTL file.
A sort of When like this

LOAD DATA
INFILE blabla
INTO TABLE LIST
WHEN country = 'FRANCE%'
( BLABLA
country TERMINATED BY ';'
BLABLA
)

My field starts with France and is completed by BLANKS.

Thx
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501252 is a reply to message #501251] Mon, 28 March 2011 03:47 Go to previous messageGo to next message
Littlefoot
Messages: 21624
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, SQL*Loader has some limitations. External tables are VERY flexible (as Michel said (as well as me, previously), you can use all (PL/)SQL commands to manipulate with data stored in an external table.
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501253 is a reply to message #501251] Mon, 28 March 2011 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert into mytable 
select whatIwant 
from externaltable
where country like 'FRANCE%'
/


Regards
Michel
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501254 is a reply to message #501253] Mon, 28 March 2011 04:09 Go to previous messageGo to next message
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 #501259 is a reply to message #501254] Mon, 28 March 2011 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 21624
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The question is clear, but it seems that answers are not Smile

Valid operators are "equal" and "not equal", so - no "LIKE" is allowed. Therefore, either use external tables, or first load data "as is" into a stagging table, and then extract what you want (which is one step too much).
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501262 is a reply to message #501259] Mon, 28 March 2011 04:39 Go to previous messageGo to next message
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 #501271 is a reply to message #501262] Mon, 28 March 2011 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing strange when you think when SQL*Loader was designed and developed.
For you who comes 25 years later it seems strange.
And this why Oracle introduces the external table to get rid of very old SQL*Loader limits.

Regards
Michel
Re: SQLLoader: Condition on field [country Like '%String%'] [message #501287 is a reply to message #501271] Mon, 28 March 2011 07:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
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 #501320 is a reply to message #501296] Mon, 28 March 2011 09:28 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Quote:
This, however, seems greedy isn't it?


Why? I don't understand your point.


I juste thought a trigger to control each row was more greedy in treatment than a simple condition on a field value (INTO TABLE when country = 'FRANCE')

Thanks for eveything everyone

[Updated on: Mon, 28 March 2011 09:29]

Report message to a moderator

Re: SQLLoader: Condition on field [country Like '%String%'] [message #501362 is a reply to message #501320] Mon, 28 March 2011 12:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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

Re: SQLLoader: Condition on field [country Like '%String%'] [message #502279 is a reply to message #501367] Tue, 05 April 2011 09:31 Go to previous message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Sorry for the late reply.
I'm impressed/surprised that it works for you.
So it seems that if spaces complete the fiels value it works but not with tabs?
Thanks a lot for your reply.
I'll try to have a look.

Previous Topic: Transferring changed data from Database A to B by Data pump
Next Topic: SQL LOADER: format of an error returned by a command line
Goto Forum:
  


Current Time: Thu Dec 03 23:51:20 CST 2020