SQL Logic in Datastage
Moderators: chulett, rschirm, roy
SQL Logic in Datastage
Hi,
How can I implement this following logic in datastage.
select
B.AMT,
rank ()
OVER (PARTITION BY B.FIELD_ID
ORDER BY B.AMT DESC)
ROW_RANK
FROM TABLE B
I wanted to implement this in datastage and here my table represents a dataset.
Appriciate your help.
Thanks
How can I implement this following logic in datastage.
select
B.AMT,
rank ()
OVER (PARTITION BY B.FIELD_ID
ORDER BY B.AMT DESC)
ROW_RANK
FROM TABLE B
I wanted to implement this in datastage and here my table represents a dataset.
Appriciate your help.
Thanks
KPSITA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
RANK is a display function, not a selection function. As a general rule it's not something I'd ask an ETL tool to do - it's something I'd ask a reporting (or business intelligence tool) to do.
While it can be done in ETL, you need to decide in advance how you're going to handle tied values.
While it can be done in ETL, you need to decide in advance how you're going to handle tied values.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 53
- Joined: Fri Apr 18, 2008 8:13 am
- Location: United States
SQL Logic in Datastage
How can you write an SQL Query on a DataSet in the first place? You either need to load the DataSet into a tabel and then try out whatever query you were planning to implement.
Regards
Ajay
Ajay
-
- Participant
- Posts: 34
- Joined: Mon May 05, 2008 1:31 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ray you are correct, RANK() is used to display records when one or more rows have same value in reporting.e.g when filter applied to display top 10 ranks(rows) and when rank 10 shared among 3 in this case 13 rows will be displayed even though filter is applied to display top 10 rows. Hope aggregate and filter can be applied to achive this in datastage.
-
- Premium Member
- Posts: 72
- Joined: Mon Jul 06, 2009 9:34 pm
- Location: Sydney
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Try it. It doesn't handle tied ranks.
To handle rank properly in ETL - and I continue to maintain that it's not an ETL task but a display task - you can use stage variables that detect change (perhaps from a Sort stage key change column) and decide whether to preserve or increment the rank. At the same time another stage variable, always incrementing, is needed to determine what the next value needs to be after tied values are encountered.
To handle rank properly in ETL - and I continue to maintain that it's not an ETL task but a display task - you can use stage variables that detect change (perhaps from a Sort stage key change column) and decide whether to preserve or increment the rank. At the same time another stage variable, always incrementing, is needed to determine what the next value needs to be after tied values are encountered.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.