Focal Point
about Data Migrator update table Using un-key fileds

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/9787035395

November 12, 2010, 03:12 AM
swell.wang
about Data Migrator update table Using un-key fileds
Dear all,
I have one TABLE like this:
index_key year week product_code customer_code order_qty
100 2010 45 AA001 B0004854 20
101 2010 45 AA001 B0004855 40

the filed index_key is primary key ,which automatic add 1 by 1 using sequence.
the fileds is unique index.

now ,I got one line data like this:
index_key year week product_code customer_code order_qty
110 2010 45 AA001 B0004854 50

the question is :
when i update the data using to match .
the value of order_qty has been updated for 50.but the index_key is also 100.
why this happend?

much thanks?


webfocus 7.65
windows xp sp3
excel,html ,pdf,and graph
November 15, 2010, 05:28 PM
Clif
If INDEX_KEY is indeed the key and you were just doing normal Insert/Update processing then 110 isn't found and a new row would be inserted with that as the key. If you want the key values from the source to be used in the target then that's what you should do.

Does "using sequence" imply that the target is ORACLE and does that mean there is a trigger on INSERT to the target table so that INDEX_KEY is assinged the next value from the sequence?

In this case if you were matching on INDEX_KEY then 110 would not be found, and a new row would be inserted with the next value as a key, 103. But that's nto what happens.

Or are you matching on the *other* columns? YEAR, WEEK, PRODUCT_CODE, CUSTOMER_CODE? That's what it looks like, and there since you've got a match on those four fields so ORDER_QTY gets updated.

This could have been accomplished in DM by either editing the automarically generated synonym so that instead of having INDEX_KEY as the 1 key, the four other columns appear first and the table is identified as having 4 keys.

Or SCD (Slowly Changing Dimension) procesing could be used (in 7.7 with support for SEQUENCE) to to this.

Please advise what columns comprise the logical key of your target table.
November 15, 2010, 10:18 PM
swell.wang
hi,Cilf,
thanks for replying.yes,I am using Oracle.
and the key is index_key,but i dont want to update the data with the filed index_key,i'd like to using the other four fileds to update.
I have worked it out,the order_qty is changed to 50,but the index_key isnot changed,i want to know why?
the main code about normal Insert/Update coding is like this.

CASE MATCHIT1
COMPUTE
INDEX_KEY=E01;
YEAR/P5 MISSING ON=E02;
WEEK/P3 MISSING ON=E03;
PRODUCT_CODE/A10V MISSING ON=E04;
CUSTOMER_CODE/A20V MISSING ON=E05;
ORDER_QTY/D20 MISSING ON = E06;
-* MATCH INDEX_KEY --the default code,using index_key to match.
MATCH YEAR --I have changed to use these four fields.
MATCH WEEK
MATCH PRODUCT_CODE
MATCH CUSTOMER_CODE
ON MATCH UPDATE * SEG TABLE_TEST
ON NOMATCH INCLUDE
GOTO TOP
ENDCASE


webfocus 7.65
windows xp sp3
excel,html ,pdf,and graph
November 16, 2010, 10:28 AM
Clif
You know if you edit the code that DataMigrator generates you void the warranty. Wink

A KEY column, by defintion, is never updated.

INDEX_KEY is identified to ORACLE and FOCUS as a key, so that's why.
November 16, 2010, 08:13 PM
swell.wang
hi,Clif.
thanks for answerring me..


webfocus 7.65
windows xp sp3
excel,html ,pdf,and graph