Link Order in transform stage
Posted: Thu Sep 11, 2003 3:55 pm
I need to do an update (conditional) and insert (unconditional) in order.
I have 2 output links from a transformation stage, and specified the link order in the tranformation stage with update link before the insert link.
However, it seems the ordering is not strictly enforced in PE. Sometimes the update would occur after the insert, so the new rows would be updated as well.
I searched the other forum, and got an idea to add an extra transformation stage to artificially delay the insert (so there are 2 transformations before the insert vs. 1 transformation before update).
Now my question: will that solution/hack always guarantee an update before insert?
Other unrelated questions.
1. In terms of performance, if the transformation is simple enough that can be done using SQL case/decode construct, should it be done in SQL select, or in a transformation stage? What other considerations are there?
2. In terms of performance, if a join/lookup can be done using SQL sub-select, or something like this:
select * from A,
(select * from B where...) as tmp_table_B,
where...
Should it be done in the SQL select, or a join/lookup stage? What other considerations are there?
Thanks
LM
I have 2 output links from a transformation stage, and specified the link order in the tranformation stage with update link before the insert link.
However, it seems the ordering is not strictly enforced in PE. Sometimes the update would occur after the insert, so the new rows would be updated as well.
I searched the other forum, and got an idea to add an extra transformation stage to artificially delay the insert (so there are 2 transformations before the insert vs. 1 transformation before update).
Now my question: will that solution/hack always guarantee an update before insert?
Other unrelated questions.
1. In terms of performance, if the transformation is simple enough that can be done using SQL case/decode construct, should it be done in SQL select, or in a transformation stage? What other considerations are there?
2. In terms of performance, if a join/lookup can be done using SQL sub-select, or something like this:
select * from A,
(select * from B where...) as tmp_table_B,
where...
Should it be done in the SQL select, or a join/lookup stage? What other considerations are there?
Thanks
LM