Generating an Oracle sequence based on columns present in the same table
I am trying to create a sequence using an analytic function in oracle. However, my sequence depends on two columns in the PROD_INFO table. Column names: PROD_ID and BILLING_NO. There can be many BILLING_NOs for the same PROD_ID. BILLING_NO can also be NULL. I need to create a SEQUENCE based on the following logic.
- For the first set of PROD_ID, BILLING_NO, I need to increase SEQUENCE like 2000, 2010, 2020, etc. until another BILLING_NO with the same PROD_ID is found.
- If it is a different BILLING_NO, I need to enter the sequence as 3000, 3010, 3020, etc.
- When PROD_ID changes, I need to change the sequence to 2000, 2010, 2020, etc. for this BILLING_NO. The output is displayed in the following table.
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
PS: I have displayed NULL values with "NULL" in the above output.
I am currently using the following analytic function. However, it doesn't change to 3000, 3010, and so on, 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!
source to share
--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 with the 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 NULLs together.
source to share