Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    Unanswered: How to frame a query on the primary key coumn for this scenario...

    Dear Gurus,

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    FUNCTION fun_11 (par_10 IN NUMBER)
      RETURN NUMBER
    IS
      l_eleventh number;
    BEGIN
      l_eleventh := calculate_11th_digit_here_as_MOD_of_the_par_10;
    
      RETURN l_eleventh;
    
      -- OR, alternatively, return the whole 11-digit value as
      -- RETURN TO_NUMBER(TO_CHAR(par_10) || TO_CHAR(l_eleventh));
    END;

  3. #3
    Join Date
    Feb 2006
    Posts
    7
    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.
    For Example..
    1234567890 becomes 2264165890 (9+9=18>10 so 18-9=9and so on..)

    How could I achieve this ?
    Thanks in advance...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Using a LOOP is maybe the most straightforward method.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •