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
pravin1581
Premium Member
Posts: 497 Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:
Post
by pravin1581 » Sun Apr 22, 2007 9:49 pm
Hi All,
I have a requirement where i need to create row numbers for different data sets as the following :
Code: Select all
R1 R2 R3 Rownum
-------- ------ ----- ----------
1 A 10 1
1 A 20 2
1 A 30 3
2 B 10 1
2 B 20 2
2 B 30 3
using @INROWNUM creates the rownumber sequentially for the entire data set where as i need it for different partitions. Please help me out in this.
georgesebastian
Participant
Posts: 83 Joined: Tue Dec 19, 2006 8:38 am
Post
by georgesebastian » Sun Apr 22, 2007 9:59 pm
Hi Pravin,
I think you need to first sort the data and then use stage variables here as well as the routine rowproc comparewithprevious value.
Thanks
George
nick.bond
Charter Member
Posts: 230 Joined: Thu Jan 15, 2004 12:00 pm
Location: London
Post
by nick.bond » Sun Apr 22, 2007 10:10 pm
George is right, first sort the data by key fields. I wouldn't use the row compare routine though as it will be quicker to use stage variables to compare the current value with the previous value.
svPreviousValue = svCurrentValue
svCurrentValue = input_rec.value
have the stage variables in that order and you can compare the two in another stage varible
svIsNewValue = If svPreviousValue = svCurrentValue Then 0 Else 1
As you are using EE make sure the partitioning is correct, i.e. partitioned by the key values. If you have used sort stage before the transformer DS should partition the data correctly for you without you having to manually specify it but if you get strange results check this in the SCORE DUMP.
Regards,
Nick.
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Mon Apr 23, 2007 6:58 am
RowProcCompareWithPreviousValue() is only present in server jobs and not px jobs. So your only option is to use stage variables.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
pravin1581
Premium Member
Posts: 497 Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:
Post
by pravin1581 » Mon Apr 23, 2007 7:12 am
DSguru2B wrote: RowProcCompareWithPreviousValue() is only present in server jobs and not px jobs. So your only option is to use stage variables.
How can we create row number for the different data sets ?
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Mon Apr 23, 2007 7:22 am
Did'nt Nick already answer your post?
What have you tried so far?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
nick.bond
Charter Member
Posts: 230 Joined: Thu Jan 15, 2004 12:00 pm
Location: London
Post
by nick.bond » Mon Apr 23, 2007 4:11 pm
Ok you need one more stage varible to produce the row numbers
svRowNumber = If svIsNewValue Then 1 Else svRowNumber + 1
Regards,
Nick.