Hi,
From source i am splitting the records based on certain conditions and i need to update different columns based on the conditions.
What i am doing is :Take all the columns from source,send to different links according to conditions, change the required columns and send to funnel and combine it. So, i have all the columns from input that needs to be updated. But when there is no update for any column, i am just pulling it from source and using the same value.
Now, if i want to update only the required columns, i think the only way is to use multiple oracle stages. which i dont want to do..
Can we use the same oracle stages with multiple update statements?.
If i pass another column with a flag, say A to update one column, B to update another column and it goes through funnel and in the oracle stage, can i use this flag to differentiate different update statements?
ot is there any other way?
multiple update statements
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Mon Oct 29, 2007 9:55 pm
- Location: Mumbai
Re: multiple update statements
As per what I understood...
suppose A, B are the key columns C, D, E are to be updated and F, G, H not to be updated
then pass F, G, H as NULL values...
and use only one statement like
update TABLE
SET C = NVL(:3, C),
D = NVL(:4, D),
E = NVL(:5, E),
F = NVL(:6, F),
G = NVL(:7, G),
H = NVL(:8, H)
WHERE A= :1 AND B = :2
suppose A, B are the key columns C, D, E are to be updated and F, G, H not to be updated
then pass F, G, H as NULL values...
and use only one statement like
update TABLE
SET C = NVL(:3, C),
D = NVL(:4, D),
E = NVL(:5, E),
F = NVL(:6, F),
G = NVL(:7, G),
H = NVL(:8, H)
WHERE A= :1 AND B = :2
Re: multiple update statements
Want or don't want, doesn't matter. You'll need multiple stages. A Server job would support multiple links into a single stage but PX doesn't. Or as you've noted, you can do a lookup and pass the current value on the fields you don't want to change on that particular record.dnat wrote:Now, if i want to update only the required columns, i think the only way is to use multiple oracle stages. which i dont want to do..
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia