OraFAQ Forum: Server Utilities » Issue in SQL loader control file

Home » RDBMS Server » Server Utilities » Issue in SQL loader control file (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issue in SQL loader control file [message #663405]

Fri, 02 June 2017 03:08

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.

Report message to a moderator

Re: Issue in SQL loader control file [message #663406 is a reply to message #663405]

Fri, 02 June 2017 03:45

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

Report message to a moderator

Re: Issue in SQL loader control file [message #663407 is a reply to message #663406]

Fri, 02 June 2017 03:50

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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).

Report message to a moderator

Re: Issue in SQL loader control file [message #663456 is a reply to message #663406]

Sat, 03 June 2017 10:42

EdStevens
Messages: 1376
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')

Report message to a moderator

Re: Issue in SQL loader control file [message #663461 is a reply to message #663456]

Mon, 05 June 2017 00:04

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.

Report message to a moderator

Re: Issue in SQL loader control file [message #663751 is a reply to message #663456]

Thu, 15 June 2017 17:04

Barbara Boehmer
Messages: 9101
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 02:51:39 CST 2024