# Monthly Archives: June 2011

## Project Euler – Problem 7

Problem 7 from Project Euler:

By listing the first six prime numbers: 2, 3, 5, 7, 11, and 13, we can see that the 6th prime is 13.

What is the 10001st prime number?

My solution:

```DECLARE
v_cnt PLS_INTEGER := 1;
v_curr PLS_INTEGER := 2;
c_num_prime CONSTANT PLS_INTEGER := 10001;

FUNCTION is_prime
(   num_in IN NUMBER
)
RETURN BOOLEAN
IS
v_is_prime BOOLEAN := TRUE;
BEGIN

FOR i in 2..ceil(sqrt(abs(num_in)))
LOOP
IF mod(num_in,i) = 0
THEN
v_is_prime := FALSE;
exit;
END IF;
END LOOP;

RETURN v_is_prime;

END is_prime;
BEGIN
WHILE v_cnt < c_num_prime
LOOP
v_curr := v_curr + 1;
IF is_prime(v_curr)
THEN
v_cnt := v_cnt + 1;
END IF;
END LOOP;
dbms_output.put_line('prime #'||c_num_prime||': '||v_curr);
END;
```

As always.. I am no mathmatician, so If you know of a better solution please let me know.

## NULLIF function

I just learned about NULLIF not too long ago, and again today found a good use case for it. I took me a while to find the function again though, so I figure if I put it here it will be easier to find next time.

Basically the function NULLIF compares 2 values. If they are equal, it returns null, otherwise it returns the first value.

In the example below, new_val1 and new_val2 are equivalent:

```with data as
(
select rownum rn
from dual
connect by level <= 5
)
select
rn,
nullif(rn,3) new_val1,
case when rn = 3 then null else rn end new_val2
from data;
```
```        RN   NEW_VAL1   NEW_VAL2
---------- ---------- ----------
1          1          1
2          2          2
3
4          4          4
5          5          5
```