|
|
|
|
|
|
Re: Query to display prime numbers [message #144197 is a reply to message #144194] |
Tue, 25 October 2005 08:00   |
sreedevi_83
Messages: 43 Registered: October 2005 Location: India
|
Member |
|
|
Hi Sanka,
Thank you very much. I wanted to get list of prime numbers through sql query and not thru procedure. Any how thanx for responding. Maaher's function seems to be more optimised than yours. I suggest you to see it.
Thanks,
Sreedevi
|
|
|
|
|
Re: Query to display prime numbers [message #147145 is a reply to message #146990] |
Tue, 15 November 2005 22:49   |
sreedevi_83
Messages: 43 Registered: October 2005 Location: India
|
Member |
|
|
Hi Sharan,
It was one of the interview question for me. So I asked for it. Thanks to Metzer for giving me more information that has been covered by Tom.
Regards,
Sreedevi.
sharan_it wrote on Tue, 15 November 2005 14:09 | Hi Sree,
What is the need of getting prime numbers through query?
Regards,
Sharan
|
|
|
|
|
Re: Query to display prime numbers [message #147269 is a reply to message #144197] |
Wed, 16 November 2005 22:33   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
sreedevi_83 wrote on Tue, 25 October 2005 23:00 | Hi I wanted to get list of prime numbers through sql query and not thru procedure.
|
Prime numbers up to n - no PL/SQL:
select a.prime
from (
select level AS prime
from dual
connect by level < &n
) a
where not exists (
select 1
from dual b
where mod(a.prime, level) = 0
and level > 1
connect by level <= a.prime / 2
)
/
_____________
Ross Leishman
|
|
|
|
|
Re: Query to display prime numbers [message #149708 is a reply to message #147311] |
Sat, 03 December 2005 01:33   |
chinni_00
Messages: 30 Registered: October 2005
|
Member |
|
|
hi Ross,
when i was go throughing the previous topics in the forum i came across this topic
and when i run the query which you gave is showing some errors
select a.prime
from (
select level AS prime
from dual
connect by level < &n
) a
where not exists (
select 1
from dual b
where mod(a.prime, level) = 0
and level > 1
connect by level <= a.prime / 2
)
The errors are
Enter value for n: 50
old 5: connect by level < &n
new 5: connect by level < 50
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Please correct me if i went wrong in understanding the query.
Thanks
Regards
|
|
|
Re: Query to display prime numbers [message #149709 is a reply to message #149708] |
Sat, 03 December 2005 02:58   |
bsubbu
Messages: 42 Registered: November 2005 Location: Hyderabad
|
Member |
|
|
hi chinni..
no it is working chinni...no errors
see my outuput
SQL> r
1 select a.prime
2 from (
3 select level AS prime
4 from dual
5 connect by level < &n
6 ) a
7 where not exists (
8 select 1
9 from dual b
10 where mod(a.prime, level) = 0
11 and level > 1
12 connect by level <= a.prime / 2
13* )
Enter value for n: 10
old 5: connect by level < &n
new 5: connect by level < 10
PRIME
----------
1
2
3
5
7
Regards
Subbu
|
|
|
Re: Query to display prime numbers [message #149716 is a reply to message #149709] |
Sat, 03 December 2005 04:41   |
chinni_00
Messages: 30 Registered: October 2005
|
Member |
|
|
hi Subbu,
There is no change in the code which i wrote and which you gave me even though i tried your code even then iam getting that error what could be wrong
can anyone explain regarding this
Thanks In Advance
Regards
chinni
|
|
|
Re: Query to display prime numbers [message #149717 is a reply to message #149716] |
Sat, 03 December 2005 04:44   |
chinni_00
Messages: 30 Registered: October 2005
|
Member |
|
|
sorry for replying again the error msg is
Quote: | Enter value for n: 10
old 5: connect by level < &n
new 5: connect by level < 10
ERROR:
ORA-01436: CONNECT BY loop in user data
|
can anyone explain regarding this error
Thanks In Advance
Regards
chinni
|
|
|
|
|
Re: Query to display prime numbers [message #149723 is a reply to message #149721] |
Sat, 03 December 2005 05:38   |
sudhir.sukumar
Messages: 52 Registered: August 2005 Location: India
|
Member |
|
|
Does not work on 8.1.7
SQL*Plus: Release 8.1.7.0.0 - Production on Sat Dec 3 14:42:36 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
sql> select a.prime
2 from (
3 select level AS prime
4 from dual
5 connect by level < &n
6 ) a
7 where not exists (
8 select 1
9 from dual b
10 where mod(a.prime, level) = 0
11 and level > 1
12 connect by level <= a.prime / 2
13 )
14 /
Enter value for n: 15
old 5: connect by level < &n
new 5: connect by level < 15
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
|
|
|
|
Re: Query to display prime numbers [message #149725 is a reply to message #149717] |
Sat, 03 December 2005 05:47   |
bsubbu
Messages: 42 Registered: November 2005 Location: Hyderabad
|
Member |
|
|
chinni .
what is ur database verision..my database version is 9.2.0.1.0
for me its working....I am not sure that wether it will work with 8i or not...
ORA-01436 CONNECT BY loop in user data
Cause: The condition specified in a CONNECT BY clause caused a loop in the query, where the next record to be selected is a descendent of itself. When this happens, there can be no end to the query.
Action: Check the CONNECT BY clause and remove the circular reference.
check out this link saying that it will not work with 7,8,8i
http://laurentschneider.blogspot.com/2005/11/bible-of-oracle.html
Regards
Subbu
[Updated on: Sat, 03 December 2005 05:56] Report message to a moderator
|
|
|
|
|
|
|
Re: Query to display prime numbers [message #327066 is a reply to message #327023] |
Fri, 13 June 2008 10:50   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
very quick should be:
with a as
(SELECT rownum zeile FROM dual connect BY level <= 1000)
select n1 from (select a1.zeile n1, a2.zeile n2 from a a1, a a2)
where n2>0
and n1>n2
group by n1
having sum(decode(Mod(n1,n2),0,1,0))=1
order by n1
|
|
|
|