# Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring

Oracle Database 11g Release 2 introduces a new feature called Recursive Subquery Factoring. My collegue Lucas sees it as a substitute for Connect By based hierarchical querying, Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring. When I first was thinking about a pratical use for this feature I couldn’t come up with anything, but on second thought:: solving a Sudoku!

Say you have a sudoku like:

To solve this sudoku you first have to transforms this to a single string by appending all rows together:

"53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79"

Past this string into a Recursive Subquery, run it and you get a new string with your solved sudoku:

```with x( 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 )
from x
, ( 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
/
```

This string can be transformed back to a nice display of the solution

So with Recursive Subquery Factoring you can solve your sudokus in 1 statement wich does fit on your screen, not something like in Solving a Sudoku with 1 SQL-statement: the Model-clause

Anton

Tags:,

1. Mirek Burnejko January 28, 2012
2. Dora December 16, 2011
3. jareeq October 7, 2011
4. Tonny August 11, 2011
5. Ramesh G July 6, 2010
6. emil March 2, 2010
7. Anton Scheffer January 4, 2010
8. C December 31, 2009
9. Anton Scheffer December 22, 2009
10. Bijesh K December 22, 2009
11. W. G. December 13, 2009
12. Anton Scheffer December 11, 2009
14. Arnaud LadriÃ¨re December 2, 2009
15. Cafer November 12, 2009
16. Frank November 9, 2009
17. Raja November 7, 2009
18. Anton Scheffer November 6, 2009
19. Mike November 6, 2009
20. Frank November 5, 2009
21. Anton Scheffer November 3, 2009
22. Dan November 3, 2009
23. Anton Scheffer November 2, 2009
24. SRINI November 2, 2009
25. daily sudoku November 2, 2009
26. club penguin toys November 1, 2009
27. monty November 1, 2009
28. sal November 1, 2009
29. covalic November 1, 2009
30. Helpers November 1, 2009
31. aleph November 1, 2009
32. fooledbyprimes October 31, 2009
33. chithanh October 31, 2009
34. chithanh October 31, 2009
35. David October 31, 2009
36. Prakash Murthy October 31, 2009
37. Jack Hewitt October 31, 2009
38. Ittichai Chammavanijakul October 22, 2009
39. Jean-Marc Desvaux October 22, 2009
40. Anton Scheffer October 14, 2009
41. Laurent Schneider October 14, 2009
42. Laurent Schneider October 14, 2009
43. Marco Gralike October 13, 2009
44. Rob van Wijk October 13, 2009
45. Lucas Jellema October 13, 2009