Home » RDBMS Server » Server Utilities » Issue in SQL loader control file (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit windows)
Issue in SQL loader control file [message #663405] Fri, 02 June 2017 03:08 Go to next message
arpit0508
Messages: 6
Registered: June 2015
Location: Mumbai
Junior Member
Hi All,

LOAD DATA
APPEND
INTO TABLE SMD_INDEX_COMPOSITION_B3
WHEN index_ric_code = '.MCX'
FIELDS TERMINATED BY '|'
(
dummy FILLER,
index_ric_code "TRIM(:index_ric_code)",
ric_code "TRIM(:ric_code)",
constituent_name "TRIM(:constituent_name)",
ntpa_code "TRIM(:ntpa_code)",
business_date EXPRESSION "TO_DATE('##COB_DATE##','YYYYMMDD')",
run_id CONSTANT '##RUN_ID##'
)
INTO TABLE SMD_INDEX_COMPOSITION_B3
WHEN index_ric_code = '.FTMIB'
FIELDS TERMINATED BY '|'
(
dummy FILLER,
index_ric_code "TRIM(:index_ric_code)",
ric_code POSITION (1) "TRIM(:ric_code)",
constituent_name "TRIM(:constituent_name)",
ntpa_code "TRIM(:ntpa_code)",
business_date EXPRESSION "TO_DATE('##COB_DATE##','YYYYMMDD')",
run_id CONSTANT '##RUN_ID##'
)
~

I am trying to load the data from txt file into table. above mentioned control file is working fine with no error. But it loaded the data only for condition WHEN index_ric_code = '.MCX' not for index_ric_code = '.FTMIB'.if i mentioned index_ric_code = '.FTMIB' condition first then its loaded data for index_ric_code = '.FTMIB' condition. I want this control file should load the data for both the condition.
Could you please help me on this.
Re: Issue in SQL loader control file [message #663406 is a reply to message #663405] Fri, 02 June 2017 03:45 Go to previous messageGo to next message
arpit0508
Messages: 6
Registered: June 2015
Location: Mumbai
Junior Member
LOAD DATA
APPEND
INTO TABLE SMD_INDEX_COMPOSITION_B3
WHEN index_ric_code = '.MCX'
FIELDS TERMINATED BY '|'
(
dummy FILLER,
index_ric_code "TRIM(:index_ric_code)",
ric_code "TRIM(:ric_code)",
constituent_name "TRIM(:constituent_name)",
ntpa_code "TRIM(:ntpa_code)",
business_date EXPRESSION "TO_DATE('##COB_DATE##','YYYYMMDD')",
run_id CONSTANT '##RUN_ID##'
)
INTO TABLE SMD_INDEX_COMPOSITION_B3
WHEN index_ric_code = '.FTMIB'
FIELDS TERMINATED BY '|'
(
dummy FILLER POSITION(1),
index_ric_code "TRIM(:index_ric_code)",
ric_code "TRIM(:ric_code)",
constituent_name "TRIM(:constituent_name)",
ntpa_code "TRIM(:ntpa_code)",
business_date EXPRESSION "TO_DATE('##COB_DATE##','YYYYMMDD')",
run_id CONSTANT '##RUN_ID##'
)
I have modify the control file and it is working fine. I have just added the POSITION(1) after the FILLER (dummy column).
Thanks
Re: Issue in SQL loader control file [message #663407 is a reply to message #663406] Fri, 02 June 2017 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.
Before your next question, please read How to use [code] tags and make your code easier to read (as already told you).

Re: Issue in SQL loader control file [message #663456 is a reply to message #663406] Sat, 03 June 2017 10:42 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
Looks like you found a workable solution but not necessarily the optimal one. Since you didn't show us what did not work, we can't comment on what might have been wrong. However, according to the official documentation, you should be able to use a single INTO TABLE construct, with multiple WHEN conditions joined by and "AND".

Quoting from the 11g Utilities manual:

Loading Records Based on a Condition

You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record.

The WHEN clause appears after the table name and is followed by one or more field conditions. The syntax for field_condition is as follows:
Description of fld_cond.gif follows
Description of the illustration fld_cond.gif

For example, the following clause indicates that any record with the value "q" in the fifth column position should be loaded:

WHEN (5) = 'q'

A WHEN clause can contain several comparisons, provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND. For example:

WHEN (deptno = '10') AND (job = 'SALES')
Re: Issue in SQL loader control file [message #663461 is a reply to message #663456] Mon, 05 June 2017 00:04 Go to previous messageGo to next message
arpit0508
Messages: 6
Registered: June 2015
Location: Mumbai
Junior Member
Thanks EdStevens for your suggestion.
Yes Michel for next time i will take care of this.
Re: Issue in SQL loader control file [message #663751 is a reply to message #663456] Thu, 15 June 2017 17:04 Go to previous message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
Ed,

In this case, what is needed is an OR condition, not AND, like so:

WHEN index_ric_code = '.MCX' OR index_ric_code = '.FTMIB'

but SQL*Loader only supports AND, so two WHEN clauses is the optimal solution.

Barbara
Previous Topic: Export/Import Criteria
Next Topic: Schema Expired & Locked after import
Goto Forum:
  


Current Time: Wed Dec 13 03:38:24 CST 2017

Total time taken to generate the page: 0.06865 seconds