create random no id

612984 · Aug 6, 2008 7:50AM

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

Keith Jamieson · Aug 6, 2008 7:58AM

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 · Aug 6, 2008 8:16AM

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 · Aug 6, 2008 8:39AM

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 · Aug 6, 2008 9:12AM

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 · Aug 6, 2008 9:20AM

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 · Aug 7, 2008 1:23AM

Thanks For the reply
it works

Forum Stats

Quick Links

Categories

create random no id

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

create random no id

Comments