Unanswered: How to frame a query on the primary key coumn for this scenario...
I have got an 11 digit no to be generated in a table..
11 digits are distributed as
PPxxxxxxxxM --(Here PP are 10,11,12; xxxxxxxx ranges from 00000000 to 99999999 and the complicated part is the 11th digit which is the MOD 10 of all the 10 digits. The values can adjust based on the no. of prefixes..)
One way is to go ahead and create a sequence from 1000000000 to 1299999999 but how the MOD10 can be calculated for each number and to be concatenated as 11th digit for each number..
what needs to be done if the intial digits(PPP) required is 100 apart from 10,11,12.
One way might be to create a function which would take the first 10 digits (nevetheless first two are PP or first three are PPP) as an IN parameter and calculate the 11th digit. Something like this:
FUNCTION fun_11 (par_10 IN NUMBER)
l_eleventh := calculate_11th_digit_here_as_MOD_of_the_par_10;
-- OR, alternatively, return the whole 11-digit value as
-- RETURN TO_NUMBER(TO_CHAR(par_10) || TO_CHAR(l_eleventh));
I have generated a sequence and referred to dual table to pass the next value in the function.
Like select x_seq.nextval into v_seqvalue from dual (v_seqvalue varchar2(10)).
I want to implement MOD10 function on v_seqvalue.
MOD10 Logic says that every 2nd digit from right to left should get doubled(or multiply by 2) and the digit doubled if greater than 10, then it should be subtracted by 9 .Finally the sum of all the digits will be divided by 10. this mod I will be concatenating with theinput v_seqvalue and will return it.
1234567890 becomes 2264165890 (9+9=18>10 so 18-9=9and so on..)