## Who's online

There are currently 0 users and 26 guests online.

# Recursive Subqueries for FizzBuzz

So in yesterday’s post I mentioned that I wasn’t happy with my solution. Among several reasons were the limit on the first 100 numbers, and the lack of using what I think to be a more elegant solution with recursive sub-queries.

Also, I received a comment about problems with the MarkDown plugin — which was also affecting my code posting, so I disabled MarkDown and can now handle code posting much, much better.

I couldn’t let it rest, so I dug into the recursive sub-queries with a vengeance and wrote up something more elegant.

```with
fb0 (input) as (
select      'buzz,fizz,fizzbuzz,fizz,buzz,fizz,fizz' input
from        dual
),
fb2 (n,pos,n_start,n_str) as
(
select      /* Anchor query of root nodes for recursion */
fb1.column_value n,
1 pos,
fb1.column_value n_start,
to_char(fb1.column_value) n_str
from        fb0,
table(sys.odcinumberlist(3,5,6,9,10,12,15)) fb1
where       /* Limit root nodes to ones that match the first word of input */
case regexp_substr(fb0.input,'\w+')
when 'fizz' then mod(fb1.column_value,3)
when 'buzz' then mod(fb1.column_value,5)
when 'fizzbuzz' them mod(fb1.column_value,15)
end = 0
union all
select      /* Build "child" nodes by walking fizzbuzz matching to input string */
case
when mod(fb2.n,15) in (5,9) then n+1
when mod(fb2.n,15) in (3,10) then n+2
when mod(fb2.n,15) in (0,6,12) then n+3
end,
fb2.pos + 1,
fb2.n_start,
fb2.n_str || ',' ||
case
when mod(fb2.n,15) in (5,9) then n+1
when mod(fb2.n,15) in (3,10) then n+2
when mod(fb2.n,15) in (0,6,12) then n+3
end
from        fb2,
fb0
where       case
when mod(fb2.n,15) in (0,5,6,10) then 'fizz'
when mod(fb2.n,15) in (3,9) then 'buzz'
when mod(fb2.n,15) in (12) then 'fizzbuzz'
end =
regexp_substr(fb0.input,'\w+',1,fb2.pos + 1)
),
fb3 (input, n_str, n_length, n_start, min_length, min_start) as
(
select      /* Measure lengths of resulting matches, discard ones that are not long enough */
fb0.input,
fb2.n_str,
fb2.n - fb2.n_start n_length,
fb2.n_start,
min(fb2.n - fb2.n_start) over () min_length,
min(fb2.n_start) over (partition by fb2.n - fb2.n_start) min_start
from        fb2,
fb0
where       fb2.pos = nvl(length(regexp_replace(fb0.input,'\w')),0)+1
),
fb4 (input, n_str) as
(
select      /* Retain the matches which are shortest and start at the smallest value */
fb3.input,
fb3.n_str
from        fb3
where       fb3.n_length = fb3.min_length
and         fb3.n_start = fb3.min_start
)
select      /* Display output */
input,
n_str
from        fb4;
```