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

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

i trying generate sequence using analytical function in oracle. sequence dependent on 2 columns in table prod_info. column names prod_id , billing_no. same prod_id, there can many billing_no. billing_no can have null values. need generate sequence based on next logic.

for first set of prod_id, billing_no combination, need increment sequence 2000, 2010, 2020 etc until different billing_no same prod_id found. if different billing_no, need input sequence 3000, 3010, 3020 etc when prod_id changes, need alter sequence 2000, 2010, 2020 etc billing_no. output displayed in 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: depicted null values 'null' in above output.

i using below analytic function. not alter 3000, 3010 etc, 4000, 4010 etc instead repeats 2000, 2010, 2020 etc.

select prod_id, billing_no, 2000 + row_number() on (partition prod_id, billing_no order billing_no) * 10 sequence, quantity prod_info;

so, please help me accomplish above result.

thank !

--the query need select prod_id, billing_no, (dense_rank() on (partition prod_id order prod_id, billing_no) + 1) * 1000 + row_number() on (partition prod_id, billing_no order billing_no) * 10 sequence, quantity prod_info /

you need play around analytic functions dense_rank , row_number.

let's check test case,

sql> info as( 2 select '1-7or' prod_id, 'ab1' billing_no dual union 3 select '1-7or' prod_id, 'ab1' billing_no dual union 4 select '1-7or' prod_id, 'ab1' billing_no dual union 5 select '1-7or' prod_id, null billing_no dual union 6 select '1-7or' prod_id, 'ab2' billing_no dual union 7 select '1-7or' prod_id, 'ab2' billing_no dual union 8 select '1-7or' prod_id, 'ab2' billing_no dual union 9 select '1-7or' prod_id, 'ab2' billing_no dual union 10 select '1-7or' prod_id, null billing_no dual union 11 select '1-7or' prod_id, null billing_no dual union 12 select '1-7or' prod_id, 'ab3' billing_no dual union 13 select '1-7or' prod_id, 'ab3' billing_no dual union 14 select '1-7or' prod_id, 'ab3' billing_no dual union 15 select '1-9er' prod_id, 'uc1' billing_no dual union 16 select '1-9er' prod_id, 'uc1' billing_no dual union 17 select '1-9er' prod_id, 'uc1' billing_no dual union 18 select '1-9er' prod_id, 'uc1' billing_no dual union 19 select '1-9er' prod_id, null billing_no dual union 20 select '1-9er' prod_id, 'uc2' billing_no dual union 21 select '1-9er' prod_id, 'uc2' billing_no dual union 22 select '1-9er' prod_id, 'uc2' billing_no dual union 23 select '1-9er' prod_id, 'uc2' billing_no dual 24 ) 25 --the query need 26 select prod_id, 27 billing_no, 28 (dense_rank() on (partition prod_id order prod_id, billing_no) + 1) * 1000 29 + 30 row_number() on (partition prod_id, billing_no order billing_no) * 10 sequence 31 info 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 windowing function keeps nulls together.

sql oracle sequence