Fixed width file

krisna · Post by **krisna** » Thu Jun 26, 2008 11:17 pm

my project requirement is like this

I have a fixed width file. Now data has to be tranferred from fixed width file to oracle. to achive this Iam using

Sequential file --> Transformer --> oracle enterprise

sample data

+320+20070301+01+00012+00000000+00000000+00001
+319+20070401+01+00012+00000000+00000000+00001
+318+20070301+01+00012+00000000+00000000+00001

in transformer Iam using
field(DSlink1.field[1,4],"+",2)
field(DSlink1.field[5,9],"+",2)
and so on.

Iam using 'field' for removing '+'
In oracle stage I used write method as upsert.

Problem is when I run the job,
ex :
I have 10 records
for first run 10 records are loaded. So total records are 10 now.
for second run again 10 records are loaded (old data only). So total records are 20 now.

but actually it should be only 10 records. I mean to say insertion is happening but not the update.

Note :- Bold column data is of date datatype.

Please provide me the solution ASAP

keshav0307 · Post by **keshav0307** » Thu Jun 26, 2008 11:43 pm

what is your update query?

do you have unique index on the key columns you may have checked the oracle stage unput coulmns?

ray.wurlod · Post by **ray.wurlod** » Fri Jun 27, 2008 12:23 am

What is your upsert setting?

Create a reject link from the Oracle Enterprise stage.

krisna · Post by **krisna** » Fri Jun 27, 2008 12:41 am

I don't have any key columns, normal update

krisna · Post by **krisna** » Fri Jun 27, 2008 12:48 am

Hi,

write method = upsert
upsert order = insert then update
upsert mode = user defined update and insert

if I use upsert order as 'update then insert' its throwing an error that

'cannot recognize - update first'

thanks

keshav0307 · Post by **keshav0307** » Fri Jun 27, 2008 1:16 am

you must have key columns for upsert

read parallel job advanced developers guide page 27 "Oracle Database Tips"

krisna · Post by **krisna** » Thu Jul 03, 2008 4:11 am

The above mentioned solutions are not working. Please give the solution

AmeyJoshi14 · Post by **AmeyJoshi14** » Thu Jul 03, 2008 4:56 am

Hi,
can you please paste the INSERT and UPDATE queries which you are using in the job...along with sample records..which are insert into the table...

keshav0307 · Post by **keshav0307** » Thu Jul 03, 2008 5:43 am

The above mentioned solutions are not working. Please give the solution

i am sorry but this is wrong statement.

probably you have not implemented it properly. this is solution because it works.