 Using a condition in SQL Loader for comma delimited file [message #294613] |
Fri, 18 January 2008 03:36  |
bhalotias
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
Hi
1. I want to load a comma delimited file using a condition (WHEN clause). But when i tried it, only the first condition was loaded and rest ignored. How do I load the file?
2. I have a table with cols
Batch,
CC_Count,
P_Count
But my .csv file has jus two fields
batch, count
Now I wish to load the data into my table which has 3 cols. This should be based on the batch. For Eg. If batch is 2 then load count into CC_Count and set P_Count = 0 and if batch is 3, then load count into P_Count and set CC_count to 0.
I could ve done this if point 1 (when clause) worked. But how to do it when thats not working?
Can this be done? How? Kindly help me out.
regards
Saurabh
[Updated on: Fri, 18 January 2008 03:39] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Using a condition in SQL Loader for comma delimited file [message #294625 is a reply to message #294618] |
Fri, 18 January 2008 04:07  |
bhalotias
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
Control File
LOAD DATA
INFILE 'c:/testfiles/testdata.csv'
discardfile ''c:/testfiles/test.log'
APPEND
INTO TABLE recon
WHEN(BATCH='2')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH, BATCH_DESC FILLER, CC_CNT, PRIMRY_CNT CONSTANT "0")
WHEN(BATCH='3')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH, BATCH_DESC FILLER, PRIMRY_CNT, CC_CNT CONSTANT "0")
Data File
2,CC Foreign,33
3,Primary Domestic,09
4,Primary Foreign,64
RECON Table has 3 cols:
Batch
CC_CNT
PRIMRY_CNT
[Updated on: Fri, 18 January 2008 04:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Using a condition in SQL Loader for comma delimited file [message #294661 is a reply to message #294652] |
Fri, 18 January 2008 06:33 |
bhalotias
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
|
I do not think decode is going to help the problem. Because, since its a csv file, the second position should state the col name in which the count is to be loaded. Using decode I can populate CC_cnt correctly, but what about Primry_cnt. Column names cant be swapped right. I hope you have understood what I exactly require.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Using a condition in SQL Loader for comma delimited file [message #294759 is a reply to message #294687] |
Sat, 19 January 2008 00:23 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need one INTO clause for each WHEN clause and you need to reset the position of the first field after any WHEN clauses after the first one.
LOAD DATA
INFILE 'testdata.csv'
discardfile 'test.log'
APPEND
INTO TABLE recon
WHEN (batch='2')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH, BATCH_DESC FILLER, CC_CNT, PRIMRY_CNT CONSTANT "0")
INTO TABLE recon
WHEN (BATCH='3')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH position (1), BATCH_DESC FILLER, PRIMRY_CNT, CC_CNT CONSTANT "0")
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
