# Recursive With Clause

Recursive With Clause
By | 2009-11-03T10:37:32+01:00 November 3rd, 2009|Categories: SQL Rants|

``` Python with <b>x</b>( s, ind ) as ( select sud, instr( sud, ' ' ) from ( select '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79' sud from dual ) union all select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 ) , instr( s, ' ', ind + 1 ) <b>from x</b> , ( select to_char( rownum ) z from dual connect by rownum <= 9 ) z where ind > 0 and not exists ( select null from ( select rownum lp from dual connect by rownum <= 9 ) where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 ) or z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 ) or z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3 + trunc( ( ind - 1 ) / 27 ) * 27 + lp + trunc( ( lp - 1 ) / 3 ) * 6 , 1 ) ) ) select s from x where ind = 0 / 12345678910111213141516171819202122232425262728293031 with <b>x</b>( s, ind ) as( select sud, instr( sud, ' ' )  from ( select   '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79'  sud from dual )  union all  select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )       , instr( s, ' ', ind + 1 )  <b>from x</b>     , ( select to_char( rownum ) z         from dual         connect by rownum <= 9       ) z  where ind > 0  and not exists ( select null                   from ( select rownum lp                          from dual                          connect by rownum <= 9                        )                   where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )                   or    z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )                   or    z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3                                      + trunc( ( ind - 1 ) / 27 ) * 27 + lp                                      + trunc( ( lp - 1 ) / 3 ) * 6                                   , 1 )                 ))select sfrom xwhere ind = 0/ ```

(Anton Scheffer) [Solving a Sudoku using Recursive Subquery Factoring]

Impressive code from our amici over at Amis. But also an impressive example of how far one can go outside the original domain of a language. I mean recursive queries? On the other hand this algorithm does not look too good in Scala or in Perl either. The mathematics of Sudoku are studied in depth. Sudoku has been shown to be NP-complete and of the many ways of solving Sudoku, dancing links and constraint programming seem to be very popular. (Typically, it takes milli seconds for a computer to solve a Sudoku.)