Sudoku in seconds (plain DB2)

Lenny77 · #1 (**permalink**) 08-28-09, 17:03

In the first step I had replaced all unknown sudoku-numbers market as "X" by "0"s. That's easy.

Very first table:

Quote:

X9X1XXXXX
X37X2XXXX
X6X9X8XX4
XX9X7XXXX
2X6XXX1X7
XXXX5X3XX
7XX6X3X5X
XXXX8X63X
XXXXX2X4X

All "X" replaced by "0".
Now we have another table, which I suppose to use in the query:

Table after replace:

Quote:

090100000
037020000
060908004
009070000
206000107
000050300
700603050
000080630
000002040

Then using the main rules of SUDOKU, I created the query.
I will not explain the details how it works, but it works.

And after few seconds I get the

Final table:

Quote:

| 5 | 9 | 4 | 1 | 6 | 7 | 8 | 2 | 3 |
| 8 | 3 | 7 | 4 | 2 | 5 | 9 | 1 | 6 |
| 1 | 6 | 2 | 9 | 3 | 8 | 5 | 7 | 4 |
| 3 | 8 | 9 | 2 | 7 | 1 | 4 | 6 | 5 |
| 2 | 5 | 6 | 3 | 4 | 9 | 1 | 8 | 7 |
| 4 | 7 | 1 | 8 | 5 | 6 | 3 | 9 | 2 |
| 7 | 4 | 8 | 6 | 1 | 3 | 2 | 5 | 9 |
| 9 | 2 | 5 | 7 | 8 | 4 | 6 | 3 | 1 |
| 6 | 1 | 3 | 5 | 9 | 2 | 7 | 4 | 8 |

If you suppose to use this query somewhere you have to reference on
Leonid Khiger (author).

So, you can check how it's working, using the SUDOKU from newspaper, or from books, or any.

Thank you, Lenny.

The query:

Sorry for big size.

Lenny

Lenny77 · #2 (**permalink**) 08-28-09, 17:07

The query (part 1)

Quote:

with
all_nbrs (k, search_no) as
(
select 1, varchar('1', 1)
from sysibm.sysdummy1
union all
select k + 1, varchar(k + 1)
from all_nbrs
where k + 1 <= 9 )
,
SudokuIn (line, sudoku_str) as
(
select 1, '090100000'
from sysibm.sysdummy1
union all
select 2, '037020000'
from sysibm.sysdummy1
union all
select 3, '060908004'
from sysibm.sysdummy1
union all
select 4, '009070000'
from sysibm.sysdummy1
union all
select 5, '206000107'
from sysibm.sysdummy1
union all
select 6, '000050300'
from sysibm.sysdummy1
union all
select 7, '700603050'
from sysibm.sysdummy1
union all
select 8, '000080630'
from sysibm.sysdummy1
union all
select 9, '000002040'
from sysibm.sysdummy1
)
,
SudokuSt1 (i, j, region, elem) as
(select 1, 1, 1, substr(sudoku_str, 1, 1)
from SudokuIn
where line = 1
union all
select
i,
j + 1,
case
when i between 1 and 3
and j + 1 between 1 and 3
then 1
when i between 1 and 3
and j + 1 between 4 and 6
then 2
when i between 1 and 3
and j + 1 between 7 and 9
then 3

when i between 4 and 6
and j + 1 between 1 and 3
then 4
when i between 4 and 6
and j + 1 between 4 and 6
then 5
when i between 4 and 6
and j + 1 between 7 and 9
then 6

when i between 7 and 9
and j + 1 between 1 and 3
then 7
when i between 7 and 9
and j + 1 between 4 and 6
then 8
when i between 7 and 9
and j + 1 between 7 and 9
then 9
end,
substr(sudoku_str, j + 1, 1)
from SudokuIn, SudokuSt1
where line = i
and j + 1 <= 9
and i <= 9

union all
select i + 1, 1,
case when i + 1 between 1 and 3
then 1
when i + 1 between 4 and 6
then 4
when i + 1 between 7 and 9
then 7
end,
substr(sudoku_str, 1, 1)
from SudokuIn, SudokuSt1
where line = i + 1
and j + 1 > 9
and i + 1 <= 9
)
,
SudokuSt2 (i, j, region, elem, cand, cnt, Sump2) as
(
select i, j, region, elem, int(elem), 1, power(2, int(elem))

from SudokuSt1
where elem > '0'

union all
select s1.i, s1.j, s1.region, s1.elem, int(t1.cand), t2.cnt2, t2.Sump2
from
SudokuSt1 s1
,
table
(select search_no cand from all_nbrs
where search_no not in (select elem from SudokuSt1 s11
where s11.i = s1.i)
and search_no not in (select elem from SudokuSt1 s11
where s11.j = s1.j)
and search_no not in (select elem from SudokuSt1 s11
where s11.region = s1.region)) t1
,
table
(select count(*) cnt2, sum(power(2, k)) Sump2 from all_nbrs
where search_no not in (select elem from SudokuSt1 s11
where s11.i = s1.i)
and search_no not in (select elem from SudokuSt1 s11
where s11.j = s1.j)
and search_no not in (select elem from SudokuSt1 s11
where s11.region = s1.region)) t2
where elem = '0'
)

Lenny77 · #3 (**permalink**) 08-28-09, 17:12

The query (part 2):

Quote:

,
SudokuRg1 (i, j, region, cand, seq, Strrg, Strrgcoord ) as
(select 1, 0, 1, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg1 rg
where
rg.j + 1 between 1 and 3
and rg.i <= 3
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 1
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 0, 1, 0, 0, Strrg, Strrgcoord
from
SudokuRg1 rg
where
rg.j + 1 > 3
and rg.i + 1 <= 3
)
,
SudokuRg2 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 1, 3, 2, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg2 rg
where
rg.j + 1 between 4 and 6
and rg.i <= 3
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 2
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 3, 2, 0, 0, Strrg, Strrgcoord
from
SudokuRg2 rg
where
rg.j + 1 > 6
and rg.i + 1 <= 3
)
,
SudokuRg3 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 1, 6, 3, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg3 rg
where
rg.j + 1 between 7 and 9
and rg.i <= 3
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 3
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 6, 3, 0, 0, Strrg, Strrgcoord
from
SudokuRg3 rg
where
rg.j + 1 > 9
and rg.i + 1 <= 3
)
,
SudokuRg4 (i, j, region, cand, seq, Strrg, Strrgcoord ) as
(select 4, 0, 4, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg4 rg
where
rg.j + 1 between 1 and 3
and rg.i <= 6
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 4
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 0, 4, 0, 0, Strrg, Strrgcoord
from
SudokuRg4 rg
where
rg.j + 1 > 3
and rg.i + 1 <= 6
)
,
SudokuRg5 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 4, 3, 5, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg5 rg
where
rg.j + 1 between 4 and 6
and rg.i <= 6
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 5
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 3, 5, 0, 0, Strrg, Strrgcoord
from
SudokuRg5 rg
where
rg.j + 1 > 6
and rg.i + 1 <= 6
)
,
SudokuRg6 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 4, 6, 6, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg6 rg
where
rg.j + 1 between 7 and 9
and rg.i <= 6
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 6
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 6, 6, 0, 0, Strrg, Strrgcoord
from
SudokuRg6 rg
where
rg.j + 1 > 9
and rg.i + 1 <= 6
)
,
SudokuRg7 (i, j, region, cand, seq, Strrg, Strrgcoord ) as
(select 7, 0, 7, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg7 rg
where
rg.j + 1 between 1 and 3
and rg.i <= 9
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 7
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 0, 7, 0, 0, Strrg, Strrgcoord
from
SudokuRg7 rg
where
rg.j + 1 > 3
and rg.i + 1 <= 9
)
,
SudokuRg8 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 7, 3, 8, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg8 rg
where
rg.j + 1 between 4 and 6
and rg.i <= 9
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 8
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 3, 8, 0, 0, Strrg, Strrgcoord
from
SudokuRg8 rg
where
rg.j + 1 > 6
and rg.i + 1 <= 9
)
,
SudokuRg9 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 7, 6, 9, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt2 s2, SudokuRg9 rg
where
rg.j + 1 between 7 and 9
and rg.i <= 9
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 9
and locate(varchar(s2.cand), Strrg) = 0
and not exists
(select 1 from SudokuSt2 s3
where
((s3.j = s2.j and s2.i <> s3.i)
or (s3.j <> s2.j and s2.i = s3.i))
and s3.cnt = 1
and s3.cand = s2.cand )

union all
select rg.i + 1, 6, 9, 0, 0, Strrg, Strrgcoord
from
SudokuRg9 rg
where
rg.j + 1 > 9
and rg.i + 1 <= 9
)

Lenny77 · #4 (**permalink**) 08-28-09, 17:14

The query (part 3):

Quote:

,
SudokuRg (region, Strrgcoord) as
(
select region, Strrgcoord
from SudokuRg1
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg2
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg3
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg4
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg5
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg6
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg7
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg8
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg9
where length(Strrg) = 9
)
,
SudokuSt3A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt2 s2
where exists
(select 1 from SudokuRg rg
where s2.region = rg.region
and locate(varchar(s2.i) || varchar(s2.j) || varchar(s2.cand), rg.Strrgcoord) > 0 )
)
,
SudokuSt3B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt3A s3a
, table
(select count(*) cnt
from SudokuSt3A s3b
where s3a.i = s3b.i
and s3a.j = s3b.j ) cc
)
,
SudokuSt3C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt3B
where cnt = 1
union
select i, j, region, cand
from SudokuSt3B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt3B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt3 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt3C s3
, table
(select count(*) cnt
from SudokuSt3B s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuClm1 (i, j, region, cand, seq, Sumc, Strcm, Strrgcoord) as
(select 0, 1, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select cm.i + 1, cm.j, s2.region, s2.cand, cm.seq + 1, cm.Sumc + s2.cand, Strcm || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'

from
SudokuSt3 s2, SudokuClm1 cm
where
cm.i + 1 <= 9
and cm.j between 1 and 4
and s2.i = cm.i + 1
and s2.j = cm.j
and locate(varchar(s2.cand), Strcm) = 0

union all
select 0, cm.j + 1, 0, 0, 0, 0, '', ''
from
SudokuClm1 cm
where
cm.i + 1 > 9
and cm.j + 1 between 1 and 4
)
,
SudokuClm2 (i, j, region, cand, seq, Sumc, Strcm, Strrgcoord) as
(select 0, 5, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select cm.i + 1, cm.j, s2.region, s2.cand, cm.seq + 1, cm.Sumc + s2.cand, Strcm || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'

from
SudokuSt3 s2, SudokuClm2 cm
where
cm.i + 1 <= 9
and cm.j between 5 and 9
and s2.i = cm.i + 1
and s2.j = cm.j
and locate(varchar(s2.cand), Strcm) = 0

union all
select 0, cm.j + 1, 0, 0, 0, 0, '', ''
from
SudokuClm2 cm
where
cm.i + 1 > 9
and cm.j + 1 between 5 and 9
)
,
SudokuClm (j, Strrgcoord) as
(
select j, Strrgcoord
from SudokuClm1 c1
where seq = 9
union
select j, Strrgcoord
from SudokuClm2 c2
where seq = 9
)
,
SudokuSt4A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt3 s3
where exists
(select 1 from SudokuClm cm
where s3.j = cm.j
and locate(varchar(s3.i) || varchar(s3.j) || varchar(s3.cand), cm.Strrgcoord) > 0 )
)
,
SudokuSt4B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt4A s4a
, table
(select count(*) cnt
from SudokuSt4A s4b
where s4a.i = s4b.i
and s4a.j = s4b.j ) cc
)
,
SudokuSt4C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt4B
where cnt = 1
union
select i, j, region, cand
from SudokuSt4B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt4B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt4 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt4C s3
, table
(select count(*) cnt
from SudokuSt4C s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuSln1 (i, j, region, cand, seq, Suml, Strln, Strrgcoord) as
(select 1, 0, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select ln.i, ln.j + 1, s2.region, s2.cand, ln.seq + 1, ln.Suml + s2.cand, Strln || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt4 s2, SudokuSln1 ln
where
ln.j + 1 <= 9
and ln.i between 1 and 3
and s2.j = ln.j + 1
and s2.i = ln.i
and locate(varchar(s2.cand), Strln) = 0

union all
select ln.i + 1, 0, 0, 0, 0, 0, '', ''
from
SudokuSln1 ln
where
ln.j + 1 > 9
and ln.i + 1 between 1 and 3

)
,
SudokuSln2 (i, j, region, cand, seq, Suml, Strln, Strrgcoord) as
(select 4, 0, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select ln.i, ln.j + 1, s2.region, s2.cand, ln.seq + 1, ln.Suml + s2.cand, Strln || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt4 s2, SudokuSln2 ln
where
ln.j + 1 <= 9
and ln.i between 4 and 6
and s2.j = ln.j + 1
and s2.i = ln.i
and locate(varchar(s2.cand), Strln) = 0

union all
select ln.i + 1, 0, 0, 0, 0, 0, '', ''
from
SudokuSln2 ln
where
ln.j + 1 > 9
and ln.i + 1 between 4 and 6
)
,
SudokuSln3 (i, j, region, cand, seq, Suml, Strln, Strrgcoord) as
(select 7, 0, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select ln.i, ln.j + 1, s2.region, s2.cand, ln.seq + 1, ln.Suml + s2.cand, Strln || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt4 s2, SudokuSln3 ln
where
ln.j + 1 <= 9
and ln.i between 7 and 9
and s2.j = ln.j + 1
and s2.i = ln.i
and locate(varchar(s2.cand), Strln) = 0

union all
select ln.i + 1, 0, 0, 0, 0, 0, '', ''
from
SudokuSln3 ln
where
ln.j + 1 > 9
and ln.i + 1 between 7 and 9

)
,
SudokuLn (i, Strrgcoord) as
(
select i, Strrgcoord
from SudokuSln1
where seq = 9
union
select i, Strrgcoord
from SudokuSln2
where seq = 9
union
select i, Strrgcoord
from SudokuSln3
where seq = 9
)
,
SudokuSt5A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt4 s4
where exists
(select 1 from SudokuLn l2
where s4.i = l2.i
and locate(varchar(s4.i) || varchar(s4.j) || varchar(s4.cand), l2.Strrgcoord) > 0 )
)
,
SudokuSt5B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt5A s5a
, table
(select count(*) cnt
from SudokuSt5A s5b
where s5a.i = s5b.i
and s5a.j = s5b.j ) cc
)
,
SudokuSt5C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt5B
where cnt = 1
union
select i, j, region, cand
from SudokuSt5B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt5B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt5 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt5C s3
, table
(select count(*) cnt
from SudokuSt5C s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)

Lenny77 · #5 (**permalink**) 08-28-09, 17:16

The query (part 4):

Quote:

,
SudokuRg12 (i, j, region, cand, seq, Strrg, Strrgcoord ) as
(select 1, 0, 1, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg12 rg
where
rg.j + 1 between 1 and 3
and rg.i <= 3
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 1
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 0, 1, 0, 0, Strrg, Strrgcoord
from
SudokuRg12 rg
where
rg.j + 1 > 3
and rg.i + 1 <= 3
)
,
SudokuRg22 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 1, 3, 2, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg22 rg
where
rg.j + 1 between 4 and 6
and rg.i <= 3
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 2
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 3, 2, 0, 0, Strrg, Strrgcoord
from
SudokuRg22 rg
where
rg.j + 1 > 6
and rg.i + 1 <= 3
)
,
SudokuRg32 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 1, 6, 3, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg32 rg
where
rg.j + 1 between 7 and 9
and rg.i <= 3
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 3
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 6, 3, 0, 0, Strrg, Strrgcoord
from
SudokuRg32 rg
where
rg.j + 1 > 9
and rg.i + 1 <= 3
)
,
SudokuRg42 (i, j, region, cand, seq, Strrg, Strrgcoord ) as
(select 4, 0, 4, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg42 rg
where
rg.j + 1 between 1 and 3
and rg.i <= 6
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 4
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 0, 4, 0, 0, Strrg, Strrgcoord
from
SudokuRg42 rg
where
rg.j + 1 > 3
and rg.i + 1 <= 6
)
,
SudokuRg52 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 4, 3, 5, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg52 rg
where
rg.j + 1 between 4 and 6
and rg.i <= 6
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 5
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 3, 5, 0, 0, Strrg, Strrgcoord
from
SudokuRg52 rg
where
rg.j + 1 > 6
and rg.i + 1 <= 6
)
,
SudokuRg62 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 4, 6, 6, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg62 rg
where
rg.j + 1 between 7 and 9
and rg.i <= 6
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 6
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 6, 6, 0, 0, Strrg, Strrgcoord
from
SudokuRg62 rg
where
rg.j + 1 > 9
and rg.i + 1 <= 6
)
,
SudokuRg72 (i, j, region, cand, seq, Strrg, Strrgcoord ) as
(select 7, 0, 7, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg72 rg
where
rg.j + 1 between 1 and 3
and rg.i <= 9
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 7
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 0, 7, 0, 0, Strrg, Strrgcoord
from
SudokuRg72 rg
where
rg.j + 1 > 3
and rg.i + 1 <= 9
)
,
SudokuRg82 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 7, 3, 8, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg82 rg
where
rg.j + 1 between 4 and 6
and rg.i <= 9
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 8
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 3, 8, 0, 0, Strrg, Strrgcoord
from
SudokuRg82 rg
where
rg.j + 1 > 6
and rg.i + 1 <= 9
)
,
SudokuRg92 (i, j, region, cand, seq, Strrg, Strrgcoord) as
(select 7, 6, 9, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select rg.i, rg.j + 1, rg.region , s2.cand, rg.seq + 1, Strrg || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt5 s2, SudokuRg92 rg
where
rg.j + 1 between 7 and 9
and rg.i <= 9
and s2.j = rg.j + 1
and s2.i = rg.i
and rg.region = s2.region
and rg.region = 9
and locate(varchar(s2.cand), Strrg) = 0

union all
select rg.i + 1, 6, 9, 0, 0, Strrg, Strrgcoord
from
SudokuRg92 rg
where
rg.j + 1 > 9
and rg.i + 1 <= 9
)

Lenny77 · #6 (**permalink**) 08-28-09, 17:17

The query (part 5):

Quote:

,
SudokuRgX (region, Strrgcoord) as
(
select region, Strrgcoord
from SudokuRg12
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg22
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg32
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg42
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg52
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg62
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg72
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg82
where length(Strrg) = 9
union
select region, Strrgcoord
from SudokuRg92
where length(Strrg) = 9
)
,
SudokuSt6A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt5 s5
where exists
(select 1 from SudokuRgX rx
where s5.region = rx.region
and locate(varchar(s5.i) || varchar(s5.j) || varchar(s5.cand), rx.Strrgcoord) > 0 )
)
,
SudokuSt6B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt6A s6a
, table
(select count(*) cnt
from SudokuSt6A s6b
where s6a.i = s6b.i
and s6a.j = s6b.j ) cc
)
,
SudokuSt6C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt6B
where cnt = 1
union
select i, j, region, cand
from SudokuSt6B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt6B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt6 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt6C s3
, table
(select count(*) cnt
from SudokuSt6C s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuClmX1 (i, j, region, cand, seq, Sumc, Strcm, Strrgcoord) as
(select 0, 1, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select cm.i + 1, cm.j, s2.region, s2.cand, cm.seq + 1, cm.Sumc + s2.cand, Strcm || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'

from
SudokuSt6 s2, SudokuClmX1 cm
where
cm.i + 1 <= 9
and cm.j between 1 and 9
and s2.i = cm.i + 1
and s2.j = cm.j
and locate(varchar(s2.cand), Strcm) = 0

union all
select 0, cm.j + 1, 0, 0, 0, 0, '', ''
from
SudokuClmX1 cm
where
cm.i + 1 > 9
and cm.j + 1 between 1 and 9
)
,
SudokuClmX (j, Strrgcoord) as
(
select distinct j, Strrgcoord
from SudokuClmX1 c1
where seq = 9
)
,
SudokuSt7A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt6 s5
where exists
(select 1 from SudokuClmX cx
where s5.j = cx.j
and locate(varchar(s5.i) || varchar(s5.j) || varchar(s5.cand), cx.Strrgcoord) > 0 )
)
,
SudokuSt7B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt7A s7a
, table
(select count(*) cnt
from SudokuSt7A s7b
where s7a.i = s7b.i
and s7a.j = s7b.j ) cc
)
,
SudokuSt7C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt7B
where cnt = 1
union
select i, j, region, cand
from SudokuSt7B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt7B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt7 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt7C s3
, table
(select count(*) cnt
from SudokuSt7C s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuSln1X (i, j, region, cand, seq, Suml, Strln, Strrgcoord) as
(select 1, 0, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select ln.i, ln.j + 1, s2.region, s2.cand, ln.seq + 1, ln.Suml + s2.cand, Strln || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'
from
SudokuSt7 s2, SudokuSln1X ln
where
ln.j + 1 <= 9
and ln.i between 1 and 9
and s2.j = ln.j + 1
and s2.i = ln.i
and locate(varchar(s2.cand), Strln) = 0

union all
select ln.i + 1, 0, 0, 0, 0, 0, '', ''
from
SudokuSln1X ln
where
ln.j + 1 > 9
and ln.i + 1 between 1 and 9
)
,
SudokuLnX (i, Strrgcoord) as
(
select distinct i, Strrgcoord
from SudokuSln1X
where seq = 9
)
,
SudokuSt8A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt7 s7
where exists
(select 1 from SudokuLnX l2
where s7.i = l2.i
and locate(varchar(s7.i) || varchar(s7.j) || varchar(s7.cand), l2.Strrgcoord) > 0 )
)
,
SudokuSt8B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt8A s8a
, table
(select count(*) cnt
from SudokuSt8A s8b
where s8a.i = s8b.i
and s8a.j = s8b.j ) cc
)
,
SudokuSt8C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt8B
where cnt = 1
union
select i, j, region, cand
from SudokuSt8B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt8B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt8 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt8C s3
, table
(select count(*) cnt
from SudokuSt8C s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuClmZ1 (i, j, region, cand, seq, Sumc, Strcm, Strrgcoord) as
(select 0, 1, 0, 0, 0, 0, varchar('', 1000), varchar('', 1000)
from sysibm.sysdummy1

union all
select cm.i + 1, cm.j, s2.region, s2.cand, cm.seq + 1, cm.Sumc + s2.cand, Strcm || varchar(s2.cand)
, Strrgcoord || varchar(s2.i) || varchar(s2.j) || varchar(s2.cand) || ';'

from
SudokuSt8 s2, SudokuClmZ1 cm
where
cm.i + 1 <= 9
and cm.j between 1 and 9
and s2.i = cm.i + 1
and s2.j = cm.j
and locate(varchar(s2.cand), Strcm) = 0

union all
select 0, cm.j + 1, 0, 0, 0, 0, '', ''
from
SudokuClmZ1 cm
where
cm.i + 1 > 9
and cm.j + 1 between 1 and 9
)
,
SudokuClmZ (j, Strrgcoord) as
(
select distinct j, Strrgcoord
from SudokuClmZ1 c1
where seq = 9
)
,
SudokuSt9A (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt8 s5
where exists
(select 1 from SudokuClmZ cz
where s5.j = cz.j
and locate(varchar(s5.i) || varchar(s5.j) || varchar(s5.cand), cz.Strrgcoord) > 0 )
)
,
SudokuSt9B (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt9A s9a
, table
(select count(*) cnt
from SudokuSt9A s9b
where s9a.i = s9b.i
and s9a.j = s9b.j ) cc
)
,
SudokuSt9C (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt9B
where cnt = 1
union
select i, j, region, cand
from SudokuSt9B s2
where cnt > 1
and
not exists
(select 1 from SudokuSt9B s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt9D (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt9C s3
, table
(select count(*) cnt
from SudokuSt9C s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuSt9E (i, j, region, cand) as
(
select i, j, region, cand
from SudokuSt9D
where cnt = 1
union
select i, j, region, cand
from SudokuSt9D s2
where cnt > 1
and
not exists
(select 1 from SudokuSt9D s3
where (s3.i = s2.i or s3.j = s2.j or s3.region = s2.region)
and s3.cnt = 1
and s3.cand = s2.cand )
)
,
SudokuSt9 (i, j, region, cand, cnt) as
(
select distinct i, j, region, cand, cnt
from SudokuSt9E s3
, table
(select count(*) cnt
from SudokuSt9E s4
where s3.i = s4.i
and s3.j = s4.j ) cc
)
,
SudokuF2 (L2_No, j, F2_String) as
(select 1, 0, varchar('|', 1000)
from sysibm.sysdummy1

union all
select L2_No, f2.j + 1, F2_String || ' ' || varchar(f1.cand) || ' |'
from
SudokuSt9 f1, SudokuF2 f2
where
f2.j + 1 <= 9
and f2.L2_No between 1 and 9
and f2.L2_No = f1.i
and f1.j = f2.j + 1

union all
select f2.L2_No + 1, 0, '|'
from
SudokuF2 f2
where
f2.j + 1 > 9
and f2.L2_No + 1 between 1 and 9
) select L2_No, F2_String from SudokuF2 where j = 9 order by 1;

Lenny77 · #7 (**permalink**) 08-28-09, 17:20

If you want to try how it works, you have to copy all parts in one huge query and execute.

If you'll have the problems give me your email address I'll send you complete query.

Lenny

db2girl · #8 (**permalink**) 08-28-09, 18:39

Do I just build SudokuIn using my numbers and run the query? I'll send you a message with my email, please send me the complete query. Thx

DB2Plus · #9 (**permalink**) 08-28-09, 22:53

This is really good one ! Ossam !

I never saw query like this before.

Thanks.

Lenny77 · #10 (**permalink**) 08-31-09, 10:19

Just COPY and USE. See an attachment.

Lenny

Lenny77 · #11 (**permalink**) 08-31-09, 10:22

You have to replace

Quote:

SudokuIn (line, sudoku_str) as
(
select 1, '090100000'
from sysibm.sysdummy1
union all
select 2, '037020000'
from sysibm.sysdummy1
union all
select 3, '060908004'
from sysibm.sysdummy1
union all
select 4, '009070000'
from sysibm.sysdummy1
union all
select 5, '206000107'
from sysibm.sysdummy1
union all
select 6, '000050300'
from sysibm.sysdummy1
union all
select 7, '700603050'
from sysibm.sysdummy1
union all
select 8, '000080630'
from sysibm.sysdummy1
union all
select 9, '000002040'
from sysibm.sysdummy1
)

by yours values.

Good Luck !

Lenny

db2girl · #12 (**permalink**) 08-31-09, 17:17

Thanks, Lenny

Lenny77 · #13 (**permalink**) 08-31-09, 17:24

Copy an attachment and go ahead !
Lenny

DB2Plus · #14 (**permalink**) 09-01-09, 23:13

Thank you, Lenny.

I have copied your SQL from attachment and RUN, just how you recommended.
Got result in 5 second.

Then I replaced your numbers by numbers from today's newspaper SUDOKU puzzle, and ran again.

I got the result in 2 seconds. That's great.

I could not believe it's possible with a single SQL statement, but it's a possible !

Kara S.

Lenny77 · #15 (**permalink**) 09-02-09, 11:40

Today I found the different way for solution Sudoku Puzzles which works not in seconds, but in minutes, but brought to us the same result:

Quote:

Sudoku Line
Line 1: 5 | 9 | 4 | 1 | 6 | 7 | 8 | 2 | 3 |
Line 2: 8 | 3 | 7 | 4 | 2 | 5 | 9 | 1 | 6 |
Line 3: 1 | 6 | 2 | 9 | 3 | 8 | 5 | 7 | 4 |
Line 4: 3 | 8 | 9 | 2 | 7 | 1 | 4 | 6 | 5 |
Line 5: 2 | 5 | 6 | 3 | 4 | 9 | 1 | 8 | 7 |
Line 6: 4 | 7 | 1 | 8 | 5 | 6 | 3 | 9 | 2 |
Line 7: 7 | 4 | 8 | 6 | 1 | 3 | 2 | 5 | 9 |
Line 8: 9 | 2 | 5 | 7 | 8 | 4 | 6 | 3 | 1 |
Line 9: 6 | 1 | 3 | 5 | 9 | 2 | 7 | 4 | 8 |

It's maybe interesting to understand by yourself how it works.
So, I'll not explain.

Open an attachment and run.

If you'll find the way of improving performance, let me know.
I'll not forget your name on publication.
Thanks everybody.
Sincerely, Leonid Khiger.

Internet Services

Web Development

Digital Marketing

Consumer Tech