Prime No Generation

skarri · #1 (**permalink**) 10-24-08, 02:04

THIS IS CODE FOR PRIME NOGENERATION IN ORACLE:
THE THING IS THAT I FIND IT DIFFCULT TO CONVERT IT TO DB2 AS THE FOR LOOP BEHAVIOUR IS DIFFERENT ANY SUGGESTIONS???

Quote:

SQL> create or replace procedure prime
2 as
3 prime number:=1;
4 begin
5 for x in 1..100
6 loop
7 prime :=1;
8 for y in 2..x-1
9 loop
10 if mod(x,y)=0
11 then
12 prime:=0;
13 end if;
14 exit when prime=0;
15 end loop;
16 if prime=1
17 then
18 dbms_output.put_line(x);
19 end if;
20 end loop;
21 end;
22 /

Procedure created.

SQL> exec prime;
1
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97

PL/SQL procedure successfully completed.

stolze · #2 (**permalink**) 10-24-08, 04:28

Writing loops in DB2 is straight-forward standard SQL. So could you be a bit more specific on what exactly is difficult for you and what does mentioned differences in the behavior are?

Btw, you know that writing in all-upper-case is seen as shouting and rather rude?

skarri · #3 (**permalink**) 10-24-08, 04:59

sorry, i dnt mean to shout
any way my problem is
how do you specify range like this
" for x in 1..100"
in db2

tonkuma · #4 (**permalink**) 10-24-08, 08:11

Althou there are some statements which execute loop,
there is no direct correspondance to " for x in 1..100" in DB2.
So, it is neccesary some considerations.
Example 1:

Code:

DECLARE x INTEGER;
...
...
SET x = 1;
WHILE x <= 100 DO
   ...
   SET x = x+1;
END WHILE;

Example 2:

Code:

/*
VALUES 1: start from 1
SELECT x+1: increment by 1
WHERE x<100: until 100
*/
FOR loop1 AS
 WITH $(x)AS(VALUES 1 UNION ALL SELECT x+1 FROM $ WHERE x<100)SELECT x FROM $ DO
   ...
END FOR;

tonkuma · #5 (**permalink**) 10-26-08, 14:51

Quote:

THIS IS CODE FOR PRIME NO GENERATION IN ORACLE:

I thoght the code was too naive.
We know some more knowledge about prime numbers.
For example:
1) All prime numbers except two are odd numbers.
2) Prime numbers less than 10 are 2, 3, 5, 7 except 1.
3) To check wheather a number x is a prime number, it is enough to see MOD(x, y) <> 0 for all prime numbers y less than or equal to SQRT(x).

By considering those facts, number of iterations can be greatly reduced.
Here are some examples.

Example 1 ( Considering 2) and 3) ):

Code:

------------------------------ Commands Entered ------------------------------
WITH
 prime_numbers(x, prime) AS(
VALUES (1, 'Y')
/**/
UNION ALL
/**/
SELECT x + 1
     , CASE 
       WHEN EXISTS
            (SELECT *
               FROM (VALUES 2,3,5,7) Y(y)
              WHERE y <= SQRT(x + 1)
                AND MOD(x + 1, y) = 0
            ) THEN
            'N'
       ELSE 'Y'
       END
  FROM prime_numbers
 WHERE x < 100
)
SELECT x
  FROM prime_numbers
 WHERE prime = 'Y'
;

Example 2 ( Considering 1), 2), and 3) ):

Code:

------------------------------ Commands Entered ------------------------------
WITH
 pn_10(n) AS (
VALUES 2,3,5,7
)
,pn_100(n, prime) AS (
VALUES (9, 'N')
UNION ALL
SELECT n + 2
     , CASE
       WHEN EXISTS
            (SELECT *
               FROM pn_10 P(p)
              WHERE p <= SQRT(n + 2)
                AND MOD(n + 2, p) = 0
            ) THEN
            'N'
       ELSE 'Y'
       END
  FROM pn_100
 WHERE n < 97
)
SELECT n AS "prime numbers less than 100"
  FROM (VALUES 1
        UNION ALL
        SELECT n FROM pn_10
        UNION ALL
        SELECT n FROM pn_100
         WHERE prime = 'Y'
       ) PN(n)
 ORDER BY
       n
;

Example 3(Extended Example 2 to prime numbers less than 10000:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 pn_10(n) AS (
VALUES 2,3,5,7
)
,pn_100(n, prime) AS (
VALUES (9, 'N')
UNION ALL
SELECT n + 2
     , CASE
       WHEN EXISTS
            (SELECT *
               FROM pn_10 P(p)
              WHERE p <= SQRT(n + 2)
                AND MOD(n + 2, p) = 0
            ) THEN
            'N'
       ELSE 'Y'
       END
  FROM pn_100
 WHERE n < 97
)
,pn_10_100(n) AS (
SELECT n FROM pn_10
UNION ALL
SELECT n FROM pn_100
 WHERE prime = 'Y'
)
,pn_10000(n, prime) AS (
VALUES (99, 'N')
UNION ALL
SELECT n + 2
     , CASE
       WHEN EXISTS
            (SELECT *
               FROM pn_10_100 P(p)
              WHERE p <= SQRT(n + 2)
                AND MOD(n + 2, p) = 0
            ) THEN
            'N'
       ELSE 'Y'
       END
  FROM pn_10000
 WHERE n < 9997
)
-- SELECT n AS "prime numbers less than 10000"
SELECT COUNT(*) AS "prime numbers less than 10000"
  FROM (VALUES 1
        UNION ALL
        SELECT n FROM pn_10_100
        UNION ALL
        SELECT n FROM pn_10000
         WHERE prime = 'Y'
       ) PN(n)
 ORDER BY
       "prime numbers less than 10000"
;
------------------------------------------------------------------------------

prime numbers less than 10000
-----------------------------
                         1230

  1 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech