Home » RDBMS Server » Server Utilities » How to use OR in WHEN clause? (SQL*Loader: Release 10.2.0.3.0)
How to use OR in WHEN clause? [message #352384] Tue, 07 October 2008 07:37 Go to next message
james77
Messages: 1
Registered: October 2008
Junior Member
Hi,
how can I use OR in WHEN clause.

I will describe my problem:
I have 5 columns and if whichever is not empty, i want to save whole row to DB, but only once.

In manual is that SQL*Loader does not allow the use of OR in the WHEN clause and is necessary to use multiple "INTO TABLE ... WHEN" clauses like

Example on 2 columns:

INTO TABLE my_selective_table
WHEN (01:01) <> ''
(
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (12:12) <> ''
(
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)

But there is error
ORA-00001: unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Anybody have some ideas how it can be solved?

Thanks

Jakub

Re: How to use OR in WHEN clause? [message #352452 is a reply to message #352384] Tue, 07 October 2008 14:31 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You could use 3 control files and do 3 consecutive loads using append or you could either use SQL*Loader or create an external table to put all of the data into a staging table, then insert into your target table by selecting from the staging table.
Previous Topic: How to combine multiple control files in SQL*Loader
Next Topic: Data Pump REMAP_SCHEMA priveleges issue
Goto Forum:
  


Current Time: Thu Dec 08 10:34:10 CST 2016

Total time taken to generate the page: 0.09404 seconds