Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
gssr
Participant
Posts: 243 Joined: Fri Jan 09, 2009 12:51 am
Location: India
Post
by gssr » Wed May 26, 2010 12:13 am
HI,
My requirement is to update a COLUMN in oracle table from the value from netezza table. The job is running fine and updates the value with respective id.
If an id is not in my netezza table , the column was left as null value.
Now i need to update values if the id is present in source else need to set as zero..
Thanks in advance
RAJ
truenorth
Participant
Posts: 139 Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio
Post
by truenorth » Wed May 26, 2010 12:35 am
I'm not clear on the mapping, but consider using Oracle left outer join to netezza and NullToZero(value from netezza).
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
gssr
Participant
Posts: 243 Joined: Fri Jan 09, 2009 12:51 am
Location: India
Post
by gssr » Wed May 26, 2010 1:09 am
truenorth wrote: I'm not clear on the mapping, but consider using Oracle left outer join to netezza and NullToZero(value from netezza).
lets consider Table Tab_A,
ID COL_A
1 199
2 299
3 399
Table Tab_B
ID COL_B
1 NULL
2 NULL
3 NULL
4 NULL
...
Need to update the table Tab_B from Tab_A,
Tab_B should be,
ID COL_B
1 199
2 299
3 399
4 000
Need to Update "0" to COL_B for the ID that is not present in the TAb_A...
RAJ
truenorth
Participant
Posts: 139 Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio
Post
by truenorth » Wed May 26, 2010 1:44 am
Just as I thought.
Tab_B left outer join Tab_A on ID
followed by
Transformer, where Tab_B.COL_B is derived from NullToZero(Tab_A.COL_A)
Where the ID matches, Tab_B.COL_B gets the value from Tab_A. Otherwise, it gets zero.
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Wed May 26, 2010 4:05 am
If you're updating while data are in transit, use NullToZero() function.
If you need to update in the Oracle table, don't bother using DataStage - execute a simple UPDATE statement against the table.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Wed May 26, 2010 6:38 am
How is this any kind of
WORKAROUND? You are (or certainly should be)
Resolved now.
-craig
"You can never have too many knives" -- Logan Nine Fingers
gssr
Participant
Posts: 243 Joined: Fri Jan 09, 2009 12:51 am
Location: India
Post
by gssr » Thu May 27, 2010 12:20 am
I deployed two jobs,
One to update the Oracle table from Netezza Table
second to update null value to zero in Oracle table
I am working around with some more option..... to do it in one job
RAJ
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Thu May 27, 2010 7:10 am
OK, your "two jobs" is a workaround of sorts but there is absolutely no need for two jobs to do something so simple.
-craig
"You can never have too many knives" -- Logan Nine Fingers