OraQA

The following SQL pattern can be used to solve the blank SUDOKU grid in a single SQL statement.

SELECT
CASE WHEN ROWNUM IN (3, 6)
     THEN substr(fin_str,1,3)||' | '||substr(fin_str,4,3)
          ||' | '||substr(fin_str,7,3) ||chr(10)||'----------------'
     ELSE substr(fin_str,1,3)||' | '||substr(fin_str,4,3)
          ||' | '||substr(fin_str,7,3)
END sudoku
FROM
(SELECT substr(fin_str,(LEVEL - 1) * 9 + 1, 9) as fin_str
  FROM
 (SELECT fin_str
  FROM
 (SELECT fin_str FROM dual
  MODEL
  DIMENSION BY (0 i )
  MEASURES (CAST(NULL AS NUMBER) num,  CAST(NULL AS NUMBER) tmp,
            CAST(NULL AS NUMBER) fin , CAST(NULL AS NUMBER) tmp_fin,
     CAST(NULL AS varchar2(4000)) str,
            CAST(NULL AS varchar2(4000)) fin_str)
  RULES ITERATE (81)
  (num[ITERATION_NUMBER] = 1,
   tmp_fin[ANY] = mod(nvl(fin[cv()-1], 0), 9) + 1,
   str[ANY] = str[cv()-1] || to_char(tmp_fin[cv()]),
   fin[ANY] = nvl(fin [cv()-1], 0) + num[cv()] + nvl(tmp[cv()], 0),
   tmp[ANY] = CASE WHEN cv(i)+1 IN (9,18,36,45,63,72)
     THEN  3
     WHEN cv(i)+1 IN (27,54,81)
            THEN  4 END,
   fin_str[ANY] = CASE WHEN str[cv() + 1] IS NULL
                       THEN str[cv()] END
  )
 )
WHERE fin_str IS NOT NULL
)
CONNECT BY LEVEL <10
);

SUDOKU
----------------
123 | 456 | 789
456 | 789 | 123
789 | 123 | 456
----------------
234 | 567 | 891
567 | 891 | 234
891 | 234 | 567
----------------
345 | 678 | 912
678 | 912 | 345
912 | 345 | 678                                                                

9 rows selected.

Elapsed: 00:00:00.09

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.