Page 1 of 1
How to populate leading value in to current row
Posted: Wed Sep 07, 2011 6:00 am
by kottinaresh
Hi,
I have fields Salary & EmpNo with values as
salary EmpNo
1000 10
2000 20
3000 30
4000 40
Now i need to populate records into next_sal as
salary EmpNo next_sal
1000 10 2000
2000 20 3000
3000 30 4000
4000 40 Null
can somebody help me on this.
For the similar requirement in Oracle we can use the LEAD function, But i doubt whether such type of functions available in datastage?
Thanks in advance!
Re: How to populate leading value in to current row
Posted: Wed Sep 07, 2011 6:31 am
by BI-RMA
Hi,
read your original table in two streams. Sort identically and add rownumbers (using systemvariable outrownum in a transformer). Name the salary-column from stream 2 next_sal. Decrease the rownumber of Stream 2 by one and join both streams by EmpNo and rownumber. The result is your target-table.
DataStage hasn't got an equivalent to the analytical SQL-functions in Oracle, but it supports the use of analytical SQL within a number of Database stages (for example Oracle, DB2).
Posted: Wed Sep 07, 2011 7:18 am
by chulett
I'm going to take a wild-assed guess that this isn't a TX question. Moved.
Re: How to populate leading value in to current row
Posted: Wed Sep 07, 2011 7:47 am
by kottinaresh
Thanks for solution Melzer, it worked.
Posted: Sun Jun 15, 2014 12:34 am
by vamsi.4a6
I am not able to get required o/p after joining based on EmpNo and rownumber.Not sure what i am missing
stream1 o/p:
salary,EmpNo,rownum
1000,10,1
2000,20,2
3000,30,3
4000,40,4
stream2 o/p:
salary,EmpNo,rownum
1000,10,0
2000,20,1
3000,30,2
4000,40,3
Posted: Sun Jun 15, 2014 3:48 am
by ssnegi
join only by rownum...
Code: Select all
3000,30,3 <-> 4000,40,3
++++++++^-------------^
3000,30,3,4000
Posted: Sun Jun 15, 2014 5:09 am
by vamsi.4a6
@ssnegi.Thanks for input.Just want to know am i missing something
because Roland mentioned to join based on empno and rownum?
Posted: Sun Jun 15, 2014 5:19 am
by ssnegi
read the file twice to sequential transformers...there put @INROWNUM and @INROWNUM-1. This will generate 1,2,3,4 and 0,1,2,3 rownums. Then join on rownum.