Home » SQL & PL/SQL » SQL & PL/SQL » Help in Understanding a complex (complex at least for me) sql to solve sudoku (11.2.04)
Help in Understanding a complex (complex at least for me) sql to solve sudoku [message #671619] Sat, 08 September 2018 04:23 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hello Experts,
I came across the following sql that is said to solve sudoku:

https://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring/

The query is as follows. I want to understand how it works and have no clue on it. Can someone help by explaining how this query is formed, the logic in it.

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
/

Thanks
Re: Help in Understanding a complex (complex at least for me) sql to solve sudoku [message #671621 is a reply to message #671619] Sat, 08 September 2018 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
https://en.wikipedia.org/wiki/Sudoku_solving_algorithms
Re: Help in Understanding a complex (complex at least for me) sql to solve sudoku [message #671622 is a reply to message #671621] Sun, 09 September 2018 08:35 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks BlackSwan. This is useful but in particular I was asking for help in understanding how that specific query works - a sort of code walk through by an expert for that query.
Re: Help in Understanding a complex (complex at least for me) sql to solve sudoku [message #671623 is a reply to message #671622] Sun, 09 September 2018 09:03 Go to previous message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Recursive Subquery Factoring

SY.
Previous Topic: update column on same table using database trigger
Next Topic: String Comparison
Goto Forum:
  


Current Time: Sun Sep 20 07:12:40 CDT 2020