How to implement PARTITION BY in IOB

235383

HI all,

I am facing the following scenario.

My IOB looks looks this.

NUMBER-ROWID-COLID-SEQNUM
1-123-1-100
1-123-2-200
1-123-3-300
1-123-4-400
1-123-5-500
1-123-6-600
1-124-1-100
1-124-2-200
1-124-3-300
1-124-4-400
1-124-5-500
1-124-6-600

In the above IOB, how can i implement the following query

ROWNUM() OVER() (PARTITIONBY SEQNUM orderby COLID DESC) NUMBER

After executing the above partition by, the output has to be

NUMBER-ROWID-COLID-SEQNUM-NUMBER
1-123-1-100-1
1-123-1-100-2
1-123-2-200-1
1-123-2-200-2
1-123-3-300-1
1-123-3-300-2
1-123-4-400-1
1-123-4-400-2
1-123-5-500-1
1-123-5-500-2
1-123-6-600-1
1-123-6-600-2


After doing this, i will groupby NUMBER while reporting.

To make it short , how to implement PARTITION by in IOB.

If not in IOB, tell me hoe to implement this in RPT design.

I am using 3.2.17 version(version10)

Awaiting for the response.This is very critical issue and needs to be completed in short time.

Thanks

prasad

Hans_vd

Hi,

First of all, the syntax of the analytical function should be:
ROW_NUMBER() OVER (PARTITION BY SEQNUM orderby COLID DESC) NUMBER

Also, I would never use the word NUMBER as a column alias

Now, how to implement this: is adding it to the select clause in the query not doing the trick?

Regards
Hans