How to populate leading value in to current row

[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!

[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).

[chulett]

I'm going to take a wild-assed guess that this isn't a TX question. Moved.

[kottinaresh]

Thanks for solution Melzer, it worked.

[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

[ssnegi]

join only by rownum...

Code: Select all

3000,30,3 <-> 4000,40,3
++++++++^-------------^
3000,30,3,4000

[vamsi.4a6]

@ssnegi.Thanks for input.Just want to know am i missing something
because Roland mentioned to join based on empno and rownum?

[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.