Issue in SQL loader control file [message #663405] |
Fri, 02 June 2017 03:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/17fb4ff94187834ed78b5a1e9e74370d?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/17fb4ff94187834ed78b5a1e9e74370d?s=64&d=mm&r=g) |
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 #663751 is a reply to message #663456] |
Thu, 15 June 2017 17:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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
|
|
|