How to set serial number in OAS (Oracle Analytics Publisher)

Question

My current result:

No.	Column A	Column B	Column C
1	A	AB	col_amount
2			col_amount
3			col_amount
4	B	AS	col_amount
5			col_amount
6	T	TV	col_amount

I want to get:

No.	Column A	Column B	Column C
1	A	AB	col_amount
			col_amount
			col_amount
2	B	AS	col_amount
			col_amount
3	T	TV	col_amount

Answer 1

Following your previous question, you can use the DENSE_RANK analytic function in a CASE expression.

Given the sample data (slightly different than the question to show changes in column_b when column_a remains the same):

CREATE TABLE table_name (Column_A, Column_B, Column_C) AS
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '01', 'AC', 'col_amount' FROM DUAL UNION ALL
SELECT '02', 'OA', 'col_amount' FROM DUAL UNION ALL
SELECT '03', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '03', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '04', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '04', 'XX', 'col_amount' FROM DUAL;

Then, if you want to number based on changes in column_a and column_b then:

SELECT CASE b_rn WHEN 1 THEN DENSE_RANK() OVER (ORDER BY column_a, column_b) END
         AS no,
       CASE a_rn WHEN 1 THEN column_a END AS column_a,
       CASE b_rn WHEN 1 THEN column_b END AS column_b,
       column_c
FROM   (
  SELECT column_a,
         column_b,
         column_c,
         ROW_NUMBER() OVER (PARTITION BY column_a ORDER BY column_b, column_c)
           AS a_rn,
         ROW_NUMBER() OVER (PARTITION BY column_a, column_b ORDER BY column_c)
           AS b_rn
  FROM   table_name
)

Which outputs:

NO	COLUMN_A	COLUMN_B	COLUMN_C
1	01	AB	col_amount
null	null	null	col_amount
2	null	AC	col_amount
3	02	OA	col_amount
4	03	OE	col_amount
null	null	null	col_amount
5	04	OE	col_amount
6	null	XX	col_amount

Or, if you only want to number based on changes in column_a then you can use:

SELECT CASE a_rn WHEN 1 THEN DENSE_RANK() OVER (ORDER BY column_a) END
         AS no,
       CASE a_rn WHEN 1 THEN column_a END AS column_a,
       CASE b_rn WHEN 1 THEN column_b END AS column_b,
       column_c
FROM   (
  SELECT column_a,
         column_b,
         column_c,
         ROW_NUMBER() OVER (PARTITION BY column_a ORDER BY column_b, column_c)
           AS a_rn,
         ROW_NUMBER() OVER (PARTITION BY column_a, column_b ORDER BY column_c)
           AS b_rn
  FROM   table_name
)

Which outputs:

NO	COLUMN_A	COLUMN_B	COLUMN_C
1	01	AB	col_amount
null	null	null	col_amount
null	null	AC	col_amount
2	02	OA	col_amount
3	03	OE	col_amount
null	null	null	col_amount
4	04	OE	col_amount
null	null	XX	col_amount

fiddle