Oracle sequence generation based on the columns present in the same table

Question

I am trying to generate a sequence using the analytical function in oracle. However my sequence is dependent on two columns in the table PROD_INFO. The column names are PROD_ID and BILLING_NO. For the same PROD_ID, there can be many BILLING_NO. The BILLING_NO can also have NULL values. I need to generate SEQUENCE based on the following logic.

1. For the first set of PROD_ID, BILLING_NO combination, I need to increment the SEQUENCE like 2000, 2010, 2020 etc until a different BILLING_NO with the same PROD_ID is found.
2. If it is a different BILLING_NO, the I need to input the sequence as 3000, 3010, 3020 etc
3. When the PROD_ID changes, I need to change the sequence to 2000, 2010, 2020 etc for that BILLING_NO. The output is displayed in the table below.

PROD_ID BILLING_NO  SEQUENCE    QUANTITY

1-7OR   AB1     2000        80

1-7OR   AB1     2010        2

1-7OR   AB1     2020        30

1-7OR   NULL    2030        10

1-7OR   AB2     3000        15

1-7OR   AB2     3010        15

1-7OR   AB2     3020        15

1-7OR   AB2     3030        15

1-7OR   NULL    3040        15

1-7OR   NULL    3050        15

1-7OR   AB3     4000        15

1-7OR   AB3     4010        15

1-7OR   AB3     4020        15

1-9ER   UC1     2000        50

1-9ER   UC1     2010        90

1-9ER   UC1     2020        35

1-9ER   UC1     2030        63

1-9ER   NULL    2040        41

1-9ER   UC2     3000        75

1-9ER   UC2     3010        75

1-9ER   UC2     3020        90

1-9ER   UC2     3030        90

P.S: I depicted NULL values with 'NULL' in the above output.

I am currently using the below analytic function. However it does not change to 3000, 3010 etc, 4000, 4010 etc instead it repeats 2000, 2010, 2020 etc.

SELECT   PROD_ID,
    BILLING_NO,
    2000 + ROW_NUMBER() OVER (PARTITION BY PROD_ID, BILLING_NO ORDER BY BILLING_NO) * 10 AS SEQUENCE,
    QUANTITY
FROM    PROD_INFO;

So, could you please help me to achieve the above result.

Thank you !

Answer 1

--The query you need
SELECT PROD_ID,
  BILLING_NO,
  (DENSE_RANK() OVER (PARTITION BY prod_id ORDER BY PROD_ID, BILLING_NO) + 1) * 1000 
  + 
  ROW_NUMBER() OVER (PARTITION BY PROD_ID, BILLING_NO ORDER BY BILLING_NO) * 10 AS sequence,
  QUANTITY
FROM PROD_INFO
/

You need to play around with ANALYTIC functions DENSE_RANK and ROW_NUMBER.

Let's check this with a test case,

SQL> WITH DATA AS(
  2  SELECT '1-7OR'  PROD_ID,   'AB1'    BILLING_NO FROM DUAL UNION ALL
  3  SELECT '1-7OR'  PROD_ID,   'AB1'        BILLING_NO FROM DUAL UNION ALL
  4  SELECT '1-7OR'  PROD_ID,   'AB1'      BILLING_NO FROM DUAL UNION ALL
  5  SELECT '1-7OR'  PROD_ID,   NULL       BILLING_NO FROM DUAL UNION ALL
  6  SELECT '1-7OR'  PROD_ID,   'AB2'       BILLING_NO FROM DUAL UNION ALL
  7  SELECT '1-7OR'  PROD_ID,   'AB2'        BILLING_NO FROM DUAL UNION ALL
  8  SELECT '1-7OR'  PROD_ID,   'AB2'         BILLING_NO FROM DUAL UNION ALL
  9  SELECT '1-7OR'  PROD_ID,   'AB2'         BILLING_NO FROM DUAL UNION ALL
 10  SELECT '1-7OR'  PROD_ID,   NULL        BILLING_NO FROM DUAL UNION ALL
 11  SELECT '1-7OR'  PROD_ID,   NULL       BILLING_NO FROM DUAL UNION ALL
 12  SELECT '1-7OR'  PROD_ID,   'AB3'         BILLING_NO FROM DUAL UNION ALL
 13  SELECT '1-7OR'  PROD_ID,   'AB3'        BILLING_NO FROM DUAL UNION ALL
 14  SELECT '1-7OR'  PROD_ID,   'AB3'         BILLING_NO FROM DUAL UNION ALL
 15  SELECT '1-9ER'  PROD_ID,   'UC1'         BILLING_NO FROM DUAL UNION ALL
 16  SELECT '1-9ER'  PROD_ID,   'UC1'        BILLING_NO FROM DUAL UNION ALL
 17  SELECT '1-9ER'  PROD_ID,   'UC1'        BILLING_NO FROM DUAL UNION ALL
 18  SELECT '1-9ER'  PROD_ID,   'UC1'        BILLING_NO FROM DUAL UNION ALL
 19  SELECT '1-9ER'  PROD_ID,   NULL       BILLING_NO FROM DUAL UNION ALL
 20  SELECT '1-9ER'  PROD_ID,   'UC2'      BILLING_NO FROM DUAL UNION ALL
 21  SELECT '1-9ER'  PROD_ID,   'UC2'      BILLING_NO FROM DUAL UNION ALL
 22  SELECT '1-9ER'  PROD_ID,   'UC2'        BILLING_NO FROM DUAL UNION ALL
 23  SELECT '1-9ER'  PROD_ID,   'UC2'         BILLING_NO FROM DUAL
 24  )
 25  --The query you need
 26  SELECT PROD_ID,
 27    BILLING_NO,
 28    (DENSE_RANK() OVER (PARTITION BY prod_id ORDER BY PROD_ID, BILLING_NO) + 1) * 1000
 29    +
 30    ROW_NUMBER() OVER (PARTITION BY PROD_ID, BILLING_NO ORDER BY BILLING_NO) * 10 AS sequence
 31  FROM data
 32  /

PROD_ BIL             SEQUENCE
----- --- --------------------
1-7OR AB1                 2010
1-7OR AB1                 2020
1-7OR AB1                 2030
1-7OR AB2                 3010
1-7OR AB2                 3020
1-7OR AB2                 3030
1-7OR AB2                 3040
1-7OR AB3                 4010
1-7OR AB3                 4020
1-7OR AB3                 4030
1-7OR                     5010
1-7OR                     5020
1-7OR                     5030
1-9ER UC1                 2010
1-9ER UC1                 2020
1-9ER UC1                 2030
1-9ER UC1                 2040
1-9ER UC2                 3010
1-9ER UC2                 3020
1-9ER UC2                 3030
1-9ER UC2                 3040
1-9ER                     4010

22 rows selected.

SQL>

NOTE The windowing function keeps all the NULLs together.