| SQL Loader related question (urgent) [message #368104] |
Wed, 29 March 2000 13:27  |
Anita
Messages: 21
Registered: March 2000
|
Junior Member |
|
|
I am loading the data from fixed length file to Oracle Database.
My control file is something like this:
LOAD DATA
APPEND
INTO TABLE Table1
(
row_id SEQUENCE(1),
Field1 POSITION(24:85),
Field2 POSITION(88:110),
numField "FLOOR((rownum-1)/20000)+100",
)
INTO TABLE Table2
(
row_id SEQUENCE(1),
Field1 POSITION(24:85),
Field2 POSITION(88:110),
numField "FLOOR((rownum-1)/20000)+100",
)
Everything works fine, except the numField. I want to load different number into this field for every 20000 rows. With the above equation it should start from 100 and go on. But looks like rownum is always being returned as 0 and hence it puts 99 into numField for every row.
Could anybody please let me know what is the problem and solution for this. If you have any other way of doing this also fine, please suggest.
Thanks in advance.
|
|
|
|
| Re: SQL Loader related question (urgent) [message #368105 is a reply to message #368104] |
Thu, 30 March 2000 08:32  |
hmg
Messages: 40
Registered: March 1999
|
Member |
|
|
Hi,
look at this ctl-file. It worked on my environment
LOAD DATA
infile 'd:\temp\loader\anita.txt'
badfile 'd:\temp\loader\anita.bad'
discardfile 'd:\temp\loader\anita.dsc'
replace
INTO TABLE test
(
row_id SEQUENCE(1),
col1 position(1:2),
numField "floor((:row_id-1)/10) + 100"
)
|
|
|
|
| Re: SQL Loader related question (urgent) [message #368106 is a reply to message #368105] |
Thu, 30 March 2000 12:43 |
Anita
Messages: 21
Registered: March 2000
|
Junior Member |
|
|
Thanks for the reply, I appreciate it.
You are using row_id, not necessarily it starts with 1 (Am I right?)
The number goes into numField has to start with a fixed number. I should know that in advance. If I use row_id, I will not know that in advance.
Or did I not get anything right here?
The equation, I used using rownum works fine in a UPDATE sql query, but it doesn't work if I specify it in the SQL Loader control file.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
