Forum Stats

  • 3,817,233 Users
  • 2,259,294 Discussions
  • 7,893,707 Comments

Discussions

create random no id

612984
612984 Member Posts: 13
edited Aug 7, 2008 1:23AM in SQL & PL/SQL
hiii,

I have to create a sequence id randomly generated depand on my conditions..
means take an example like first id is 43 then second one should be square of 4 + square of 3 that is 25.
then third id should be square of 2 + square of 5 means 29. and if 100 comes then start again.
so the output should be like this
43
25
29
85
100

Thanks
user609981

Comments

  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815
    You'll have to write a pl/sql function for this.
    Pass in the value to the function and let it perform the calculation and return the result.
  • jeneesh
    jeneesh Member Posts: 7,168
    edited Aug 6, 2008 8:17AM
    Like..
    SQL> create or replace function f1(p_start number,p_rn number) return number is
    2 n number;
    3 begin
    4 n := p_start;
    5 if n > 0 then
    6 for i in 1..p_rn loop
    7 if n > 99 then
    8 n := p_start;
    9 else
    10 n := power(mod(n,10),2) + power(floor(n/10),2);
    11 end if;
    12 end loop;
    13 end if;
    14 return n;
    15 end;
    16 /

    Function created.

    SQL> select f1(&p_start,rownum-1) rn
    2 from dual
    3 connect by level <= 10;
    Enter value for p_start: 43
    old 1: select f1(&p_start,rownum-1) rn
    new 1: select f1(43,rownum-1) rn

    RN
    ----------
    43
    25
    29
    85
    89
    145
    43
    25
    29
    85

    10 rows selected.

    Message was edited by:
    jeneesh

    Not tested thoroughly..
  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815
    Very nice solution.
    And if you want to start with a random value , just replace &pstart
    with trunc(dbms_random.value(10,100))


    select f1( trunc(dbms_random.value(10,100)),rownum-1) rn from dual connect by level <= 10;
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Aug 6, 2008 9:25AM
    The sequence could be longer if you just subtract 100 when you obtain a result over 100 and continue with that number. The only problem left remains 100 = 64 + 36 that can occurr when you get to 68 or 86.

    Regards

    Etbin

    This is similar to an old hashing technique where you started with an eight digit number taking the middle four digits as the hash value. Then you squared the hash value to take the middle four digits of the square as a new hash value, repeating the process and keeping the fingers crossed ...

    Message was edited by:
    Etbin
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    A bit unconventional but why not:
    SQL>  var n number
    
    SQL>  exec :n := 43
    PL/SQL procedure successfully completed.
    
    SQL>  select * from xmltable('declare function local:f($i)
                            {
                              if(string-length($i) = 2) then 
                               ($i, local:f(string(xs:int(substring($i,1,1)) * xs:int(substring($i,1,1)) + xs:int(substring($i,2,1)) * xs:int(substring($i,2,1)))))
                              else (100)
                             };(::)
                             local:f(P)' passing xmlelement(p, :n)
                                         columns n integer path '.') x
    
             N
    ----------
            43
            25
            29
            85
            89
           100
    
    6 rows selected.
    
    SQL>  exec :n := 16
    PL/SQL procedure successfully completed.
    
    SQL>  select * from xmltable('declare function local:f($i)
                            {
                              if(string-length($i) = 2) then 
                               ($i, local:f(string(xs:int(substring($i,1,1)) * xs:int(substring($i,1,1)) + xs:int(substring($i,2,1)) * xs:int(substring($i,2,1)))))
                              else (100)
                             };(::)
                             local:f(P)' passing xmlelement(p, :n)
                                         columns n integer path '.') x
    
             N
    ----------
            16
            37
            58
            89
           100
    
    5 rows selected.
  • 612984
    612984 Member Posts: 13
    Thanks For the reply
    it works
This discussion has been closed.