## Forum Stats

• 3,817,233 Users
• 2,259,294 Discussions

Discussions

# create random no id

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

• 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.
• 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..                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ```
• 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;
• 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
• 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.```