Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    16

    Unanswered: Prime No Generation

    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???
    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.
    "TALK SENSE TO FOOL. HE CALLS U FOOLISH"

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2008
    Posts
    16
    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
    "TALK SENSE TO FOOL. HE CALLS U FOOLISH"

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 10-26-08 at 15:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •