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!
How to populate leading value in to current row
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 9
- Joined: Fri Feb 18, 2011 1:12 am
- Location: Hyderabad
How to populate leading value in to current row
Naresh
ETL Developer
ETL Developer
Re: How to populate leading value in to current row
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).
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).
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 9
- Joined: Fri Feb 18, 2011 1:12 am
- Location: Hyderabad
Re: How to populate leading value in to current row
Thanks for solution Melzer, it worked.
Naresh
ETL Developer
ETL Developer
join only by rownum...
Code: Select all
3000,30,3 <-> 4000,40,3
++++++++^-------------^
3000,30,3,4000