I tried to download the query from attachment, for me it looks unreadable.
So, I'll try to get you query in parts. At least you'll see how it looks nice.
Part 1:
Code:
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'
)
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
)
,
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
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
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
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
)
,
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
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
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;
If you want to try how it works, you have to copy all parts and join text in one huge query, then execute.
if you suppose to use this query somewhere you have to reference to
Leonid Khiger (author).
I will not get into a legal dispute about Your request
let' s just say that it is <improper>
You now have three options...
- use the customary legalese and packaging(*) ... which means
proper comments in the source code,
reference to the licensing terms,
copy of the license being used ( the text, not a url pointer )
here is a link to www.opensource.org/licenses
(Open Source Licenses which have successfully gone through the approval process and comply with the Open Source Definition)
- ask a moderator to clean up the quoted sentence
- ask a moderator to delete the topic
Quote:
I will appreciate your respond and your comments (positive, or negatitive) on my SQL.
remember... replying is on voluntary base, time, interest,
soliciting answers is just bad taste
Also You should have noticed that Your posts were edited to <unbold> them
please avoid doing it in the future
(*)for packaging only, usually a zipped or tar gzipped/bzipped archive containing
<README> file with something along ...
Quote:
This file, and the remaining files of the archive containing it
excluding <the licence document> are :
(c) Copyright yyyy-yyyy, <the authr name>
and are released under the <licence name>
if you suppose to use this query somewhere you have to reference to
Leonid Khiger (author).
I will not get into a legal dispute about Your request
let' s just say that it is <improper>
You now have three options...
- use the customary legalese and packaging(*) ... which means
proper comments in the source code,
reference to the licensing terms,
copy of the license being used ( the text, not a url pointer )
here is a link to www.opensource.org/licenses
(Open Source Licenses which have successfully gone through the approval process and comply with the Open Source Definition)
- ask a moderator to clean up the quoted sentence
- ask a moderator to delete the topic
Quote:
I will appreciate your respond and your comments (positive, or negatitive) on my SQL.
remember... replying is on voluntary base, time, interest,
soliciting answers is just bad taste
Also You should have noticed that Your posts were edited to <unbold> them
please avoid doing it in the future
(*)for packaging only, usually a zipped or tar gzipped/bzipped archive containing
<README> file with something along ...
Quote:
This file, and the remaining files of the archive containing it
excluding <the licence document> are :
(c) Copyright yyyy-yyyy, <the authr name>
and are released under the <licence name>
a list of the files contained in the package
You are absolutelly right !
Please, change the phrase "reference to Leonid Khiger (author). " to
"Thanks, Leonid Khiger". :
For everybody who loves the simple ideas and strong algorithms I found another way to solve the sudoku puzzles.
This algorithm based on the simple idea:
If in all lines, columns and regions we have all numbers from 1 to 9 we have also the same summaries of these numbers
Sum(i) = Sum(j) = Sum(region) = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 = 45.
But this is not enough to solve Sudoku.
And I use the Sum of the power(2, Aij) = 2^1 + 2^2 +... + 2^8 + 2^9 = 1022.
We solved Sudoku. But it's running for 3 to 5 minutes and I continue to work on it to improve performance of the query.
If you'll have some ideas how to improve performance I'll appreciate your solution.
Thanks, lenny Khiger
Code:
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, power(2, int(t1.cand))
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'
)
,
Sudoku_Final (Line_1, Line_2, Line_3, Line_4, Line_5, Line_6, Line_7, Line_8, Line_9) as
( select distinct
'Line 1: ' || varchar(s11) || ' | ' || varchar(s12) || ' | ' || varchar(s13) || ' | ' ||
varchar(s14) || ' | ' || varchar(s15) || ' | ' || varchar(s16) || ' | ' ||
varchar(s17) || ' | ' || varchar(s18) || ' | ' || varchar(s19) || ' | ' Line_1,
'Line 9: ' || varchar(s91) || ' | ' || varchar(s92) || ' | ' || varchar(s93) || ' | ' ||
varchar(s94) || ' | ' || varchar(s95) || ' | ' || varchar(s96) || ' | ' ||
varchar(s97) || ' | ' || varchar(s98) || ' | ' || varchar(s99) || ' | ' Line_9
from
(
select s11.s S11, s12.s S12, s13.s S13, s14.s S14, s15.s S15, s16.s S16, s17.s S17, s18.s S18, s19.s S19,
s11.y s11y, s12.y S12y, s13.y S13y, s14.y S14y, s15.y S15y, s16.y S16y, s17.y S17y, s18.y S18y, s19.y S19y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 1) s11, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 2 and cand not in (s11.s) ) s12, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 3 and cand not in (s11.s, s12.s) ) s13, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 4
and cand not in (s11.s, s12.s, s13.s) ) s14, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 5
and cand not in (s11.s, s12.s, s13.s, s14.s) ) s15, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 6
and cand not in (s11.s, s12.s, s13.s, s14.s, s15.s) ) s16, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 7
and cand not in (s11.s, s12.s, s13.s, s14.s, s15.s, s16.s) ) s17, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 8
and cand not in (s11.s, s12.s, s13.s, s14.s, s15.s, s16.s, s17.s) ) s18, table
(select cand S, Sump2 Y from SudokuSt2 where i = 1 and j = 9
and cand not in (s11.s, s12.s, s13.s, s14.s, s15.s, s16.s, s17.s, s18.s)
) s19
where s11.s not in (s12.s, s13.s, s14.s, s15.s, s16.s, s17.s, s18.s, s19.s)
and s12.s not in (s13.s, s14.s, s15.s, s16.s, s17.s, s18.s, s19.s)
and s13.s not in (s14.s, s15.s, s16.s, s17.s, s18.s, s19.s)
and s14.s not in (s15.s, s16.s, s17.s, s18.s, s19.s)
and s15.s not in (s16.s, s17.s, s18.s, s19.s)
and s16.s not in (s17.s, s18.s, s19.s)
and s17.s not in (s18.s, s19.s)
and s18.s not in (s19.s) ) SL1
,
(
select s21.s S21, s22.s S22, s23.s S23, s24.s S24, s25.s S25, s26.s S26, s27.s S27, s28.s S28, s29.s S29,
s21.y s21y, s22.y S22y, s23.y S23y, s24.y S24y, s25.y S25y, s26.y S26y, s27.y S27y, s28.y S28y, s29.y S29y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 1) s21, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 2 and cand not in (s21.s) ) s22, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 3 and cand not in (s21.s, s22.s) ) s23, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 4
and cand not in (s21.s, s22.s, s23.s) ) s24, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 5
and cand not in (s21.s, s22.s, s23.s, s24.s) ) s25, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 6
and cand not in (s21.s, s22.s, s23.s, s24.s, s25.s) ) s26, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 7
and cand not in (s21.s, s22.s, s23.s, s24.s, s25.s, s26.s) ) s27, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 8
and cand not in (s21.s, s22.s, s23.s, s24.s, s25.s, s26.s, s27.s) ) s28, table
(select cand S, Sump2 Y from SudokuSt2 where i = 2 and j = 9
and cand not in (s21.s, s22.s, s23.s, s24.s, s25.s, s26.s, s27.s, s28.s)
) s29
where s21.s not in (s22.s, s23.s, s24.s, s25.s, s26.s, s27.s, s28.s, s29.s)
and s22.s not in (s23.s, s24.s, s25.s, s26.s, s27.s, s28.s, s29.s)
and s23.s not in (s24.s, s25.s, s26.s, s27.s, s28.s, s29.s)
and s24.s not in (s25.s, s26.s, s27.s, s28.s, s29.s)
and s25.s not in (s26.s, s27.s, s28.s, s29.s)
and s26.s not in (s27.s, s28.s, s29.s)
and s27.s not in (s28.s, s29.s)
and s28.s not in (s29.s) ) SL2
,
(
select s31.s S31, s32.s S32, s33.s S33, s34.s S34, s35.s S35, s36.s S36, s37.s S37, s38.s S38, s39.s S39,
s31.y s31y, s32.y S32y, s33.y S33y, s34.y S34y, s35.y S35y, s36.y S36y, s37.y S37y, s38.y S38y, s39.y S39y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 1) s31, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 2 and cand not in (s31.s) ) s32, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 3 and cand not in (s31.s, s32.s) ) s33, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 4
and cand not in (s31.s, s32.s, s33.s) ) s34, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 5
and cand not in (s31.s, s32.s, s33.s, s34.s) ) s35, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 6
and cand not in (s31.s, s32.s, s33.s, s34.s, s35.s) ) s36, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 7
and cand not in (s31.s, s32.s, s33.s, s34.s, s35.s, s36.s) ) s37, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 8
and cand not in (s31.s, s32.s, s33.s, s34.s, s35.s, s36.s, s37.s) ) s38, table
(select cand S, Sump2 Y from SudokuSt2 where i = 3 and j = 9
and cand not in (s31.s, s32.s, s33.s, s34.s, s35.s, s36.s, s37.s, s38.s)
) s39
where s31.s not in (s32.s, s33.s, s34.s, s35.s, s36.s, s37.s, s38.s, s39.s)
and s32.s not in (s33.s, s34.s, s35.s, s36.s, s37.s, s38.s, s39.s)
and s33.s not in (s34.s, s35.s, s36.s, s37.s, s38.s, s39.s)
and s34.s not in (s35.s, s36.s, s37.s, s38.s, s39.s)
and s35.s not in (s36.s, s37.s, s38.s, s39.s)
and s36.s not in (s37.s, s38.s, s39.s)
and s37.s not in (s38.s, s39.s)
and s38.s not in (s39.s) ) SL3
,
(
select s41.s S41, s42.s S42, s43.s S43, s44.s S44, s45.s S45, s46.s S46, s47.s S47, s48.s S48, s49.s S49,
s41.y s41y, s42.y S42y, s43.y S43y, S44.y S44y, s45.y S45y, s46.y S46y, s47.y S47y, s48.y S48y, s49.y S49y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 1) s41, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 2 and cand not in (s41.s) ) s42, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 3 and cand not in (s41.s, s42.s) ) s43, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 4
and cand not in (s41.s, s42.s, s43.s) ) s44, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 5
and cand not in (s41.s, s42.s, s43.s, s44.s) ) s45, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 6
and cand not in (s41.s, s42.s, s43.s, s44.s, s45.s) ) s46, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 7
and cand not in (s41.s, s42.s, s43.s, s44.s, s45.s, s46.s) ) s47, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 8
and cand not in (s41.s, s42.s, s43.s, s44.s, s45.s, s46.s, s47.s) ) s48, table
(select cand S, Sump2 Y from SudokuSt2 where i = 4 and j = 9
and cand not in (s41.s, s42.s, s43.s, s44.s, s45.s, s46.s, s47.s, s48.s)
) s49
where s41.s not in (s42.s, s43.s, s44.s, s45.s, s46.s, s47.s, s48.s, s49.s)
and s42.s not in (s43.s, s44.s, s45.s, s46.s, s47.s, s48.s, s49.s)
and s43.s not in (s44.s, s45.s, s46.s, s47.s, s48.s, s49.s)
and s44.s not in (s45.s, s46.s, s47.s, s48.s, s49.s)
and s45.s not in (s46.s, s47.s, s48.s, s49.s)
and s46.s not in (s47.s, s48.s, s49.s)
and s47.s not in (s48.s, s49.s)
and s48.s not in (s49.s) ) SL4
,
(
select s51.s S51, s52.s S52, s53.s S53, s54.s S54, s55.s S55, s56.s S56, s57.s S57, s58.s S58, s59.s S59,
s51.y s51y, s52.y S52y, s53.y S53y, S54.y S54y, s55.y S55y, s56.y S56y, s57.y S57y, s58.y S58y, s59.y S59y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 1) s51, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 2 and cand not in (s51.s) ) s52, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 3 and cand not in (s51.s, s52.s) ) s53, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 4
and cand not in (s51.s, s52.s, s53.s) ) s54, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 5
and cand not in (s51.s, s52.s, s53.s, s54.s) ) s55, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 6
and cand not in (s51.s, s52.s, s53.s, s54.s, s55.s) ) s56, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 7
and cand not in (s51.s, s52.s, s53.s, s54.s, s55.s, s56.s) ) s57, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 8
and cand not in (s51.s, s52.s, s53.s, s54.s, s55.s, s56.s, s57.s) ) s58, table
(select cand S, Sump2 Y from SudokuSt2 where i = 5 and j = 9
and cand not in (s51.s, s52.s, s53.s, s54.s, s55.s, s56.s, s57.s, s58.s)
) s59
where s51.s not in (s52.s, s53.s, s54.s, s55.s, s56.s, s57.s, s58.s, s59.s)
and s52.s not in (s53.s, s54.s, s55.s, s56.s, s57.s, s58.s, s59.s)
and s53.s not in (s54.s, s55.s, s56.s, s57.s, s58.s, s59.s)
and s54.s not in (s55.s, s56.s, s57.s, s58.s, s59.s)
and s55.s not in (s56.s, s57.s, s58.s, s59.s)
and s56.s not in (s57.s, s58.s, s59.s)
and s57.s not in (s58.s, s59.s)
and s58.s not in (s59.s) ) SL5
,
(
select s61.s S61, s62.s S62, s63.s S63, s64.s S64, s65.s S65, s66.s S66, s67.s S67, s68.s S68, s69.s S69,
s61.y s61y, s62.y S62y, s63.y S63y, S64.y S64y, s65.y S65y, s66.y S66y, s67.y S67y, s68.y S68y, s69.y S69y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 1) s61, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 2 and cand not in (s61.s) ) s62, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 3 and cand not in (s61.s, s62.s) ) s63, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 4
and cand not in (s61.s, s62.s, s63.s) ) s64, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 5
and cand not in (s61.s, s62.s, s63.s, s64.s) ) s65, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 6
and cand not in (s61.s, s62.s, s63.s, s64.s, s65.s) ) s66, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 7
and cand not in (s61.s, s62.s, s63.s, s64.s, s65.s, s66.s) ) s67, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 8
and cand not in (s61.s, s62.s, s63.s, s64.s, s65.s, s66.s, s67.s) ) s68, table
(select cand S, Sump2 Y from SudokuSt2 where i = 6 and j = 9
and cand not in (s61.s, s62.s, s63.s, s64.s, s65.s, s66.s, s67.s, s68.s)
) s69
where s61.s not in (s62.s, s63.s, s64.s, s65.s, s66.s, s67.s, s68.s, s69.s)
and s62.s not in (s63.s, s64.s, s65.s, s66.s, s67.s, s68.s, s69.s)
and s63.s not in (s64.s, s65.s, s66.s, s67.s, s68.s, s69.s)
and s64.s not in (s65.s, s66.s, s67.s, s68.s, s69.s)
and s65.s not in (s66.s, s67.s, s68.s, s69.s)
and s66.s not in (s67.s, s68.s, s69.s)
and s67.s not in (s68.s, s69.s)
and s68.s not in (s69.s) ) SL6
,
(
select s71.s S71, s72.s S72, s73.s S73, s74.s S74, s75.s S75, s76.s S76, s77.s S77, s78.s S78, s79.s S79,
s71.y s71y, s72.y S72y, s73.y S73y, S74.y S74y, s75.y S75y, s76.y S76y, s77.y S77y, s78.y S78y, s79.y S79y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 1) s71, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 2 and cand not in (s71.s) ) s72, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 3 and cand not in (s71.s, s72.s) ) s73, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 4
and cand not in (s71.s, s72.s, s73.s) ) s74, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 5
and cand not in (s71.s, s72.s, s73.s, s74.s) ) s75, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 6
and cand not in (s71.s, s72.s, s73.s, s74.s, s75.s) ) s76, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 7
and cand not in (s71.s, s72.s, s73.s, s74.s, s75.s, s76.s) ) s77, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 8
and cand not in (s71.s, s72.s, s73.s, s74.s, s75.s, s76.s, s77.s) ) s78, table
(select cand S, Sump2 Y from SudokuSt2 where i = 7 and j = 9
and cand not in (s71.s, s72.s, s73.s, s74.s, s75.s, s76.s, s77.s, s78.s)
) s79
where s71.s not in (s72.s, s73.s, s74.s, s75.s, s76.s, s77.s, s78.s, s79.s)
and s72.s not in (s73.s, s74.s, s75.s, s76.s, s77.s, s78.s, s79.s)
and s73.s not in (s74.s, s75.s, s76.s, s77.s, s78.s, s79.s)
and s74.s not in (s75.s, s76.s, s77.s, s78.s, s79.s)
and s75.s not in (s76.s, s77.s, s78.s, s79.s)
and s76.s not in (s77.s, s78.s, s79.s)
and s77.s not in (s78.s, s79.s)
and s78.s not in (s79.s) ) SL7
,
(
select s81.s S81, s82.s S82, s83.s S83, s84.s S84, s85.s S85, s86.s S86, s87.s S87, s88.s S88, s89.s S89,
s81.y s81y, s82.y S82y, s83.y S83y, S84.y S84y, s85.y S85y, s86.y S86y, s87.y S87y, s88.y S88y, s89.y S89y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 1) s81, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 2 and cand not in (s81.s) ) s82, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 3 and cand not in (s81.s, s82.s) ) s83, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 4
and cand not in (s81.s, s82.s, s83.s) ) s84, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 5
and cand not in (s81.s, s82.s, s83.s, s84.s) ) s85, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 6
and cand not in (s81.s, s82.s, s83.s, s84.s, s85.s) ) s86, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 7
and cand not in (s81.s, s82.s, s83.s, s84.s, s85.s, s86.s) ) s87, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 8
and cand not in (s81.s, s82.s, s83.s, s84.s, s85.s, s86.s, s87.s) ) s88, table
(select cand S, Sump2 Y from SudokuSt2 where i = 8 and j = 9
and cand not in (s81.s, s82.s, s83.s, s84.s, s85.s, s86.s, s87.s, s88.s)
) s89
where s81.s not in (s82.s, s83.s, s84.s, s85.s, s86.s, s87.s, s88.s, s89.s)
and s82.s not in (s83.s, s84.s, s85.s, s86.s, s87.s, s88.s, s89.s)
and s83.s not in (s84.s, s85.s, s86.s, s87.s, s88.s, s89.s)
and s84.s not in (s85.s, s86.s, s87.s, s88.s, s89.s)
and s85.s not in (s86.s, s87.s, s88.s, s89.s)
and s86.s not in (s87.s, s88.s, s89.s)
and s87.s not in (s88.s, s89.s)
and s88.s not in (s89.s) ) SL8
,
(
select s91.s S91, s92.s S92, s93.s S93, s94.s S94, s95.s S95, s96.s S96, s97.s S97, s98.s S98, s99.s S99,
s91.y s91y, s92.y S92y, s93.y S93y, S94.y S94y, s95.y S95y, s96.y S96y, s97.y S97y, s98.y S98y, s99.y S99y
From
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 1) s91, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 2 and cand not in (s91.s) ) s92, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 3 and cand not in (s91.s, s92.s) ) s93, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 4
and cand not in (s91.s, s92.s, s93.s) ) s94, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 5
and cand not in (s91.s, s92.s, s93.s, s94.s) ) s95, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 6
and cand not in (s91.s, s92.s, s93.s, s94.s, s95.s) ) s96, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 7
and cand not in (s91.s, s92.s, s93.s, s94.s, s95.s, s96.s) ) s97, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 8
and cand not in (s91.s, s92.s, s93.s, s94.s, s95.s, s96.s, s97.s) ) s98, table
(select cand S, Sump2 Y from SudokuSt2 where i = 9 and j = 9
and cand not in (s91.s, s92.s, s93.s, s94.s, s95.s, s96.s, s97.s, s98.s)
) s99
where s91.s not in (s92.s, s93.s, s94.s, s95.s, s96.s, s97.s, s98.s, s99.s)
and s92.s not in (s93.s, s94.s, s95.s, s96.s, s97.s, s98.s, s99.s)
and s93.s not in (s94.s, s95.s, s96.s, s97.s, s98.s, s99.s)
and s94.s not in (s95.s, s96.s, s97.s, s98.s, s99.s)
and s95.s not in (s96.s, s97.s, s98.s, s99.s)
and s96.s not in (s97.s, s98.s, s99.s)
and s97.s not in (s98.s, s99.s)
and s98.s not in (s99.s) ) SL9
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello Lenny,
Quote:
I tried to download the query from attachment, for me it looks unreadable.
Please right-click on the attachment you posted and then "Save Target As". The saved file should be usable directly. There are problems with .txt attachments with Internet Explorer and some other browers.
Once you confirm this works for you, i'll delete all of the inline code as it is easier to simply save the code rather than copy/paste "the pieces".
I tried to download the query from attachment, for me it looks unreadable.
Please right-click on the attachment you posted and then "Save Target As". The saved file should be usable directly. There are problems with .txt attachments with Internet Explorer and some other browers.
Once you confirm this works for you, i'll delete all of the inline code as it is easier to simply save the code rather than copy/paste "the pieces".
Oh, thanks.
But do not delete the last one. This code is different from the code in the attachment. Based on the different ideas.
We can change the input table (SudokuIn) to the user created table instead of sysibm.sysdummy1:
Code:
SudokuIn (line, sudoku_str) as
(
select 1, '090100000'
from sysibm.sysdummy1
union all
select 2, '037020000'
from sysibm.sysdummy1
union all
.........
union all
select 9, '..... )
The structure of table could be any, for example:
Quote:
Sudoku_tbl
(sud_id integer not null,
sud_line_no integer not null,
sud_line varchar(100) not null,
sud_oper char(3)
)
In this case we have to insert into table before:
Code:
Insert into Sudoku_tbl
select 123, 1, '090100000', 'in'
from sysibm.sysdummy1
union all
select 123, 2, '037020000', 'in'
from sysibm.sysdummy1
union all
.........
union all
select 123, 9, '....., 'in'
Then we have to change SudokuIn:
Code:
SudokuIn (line, sudoku_str) as
(
select sud_line_no, sud_line
from Sudoku_tbl
where sud_id = 123
and sud_oper = 'in'
)
Finally on the end we have to insert the lines into Sudoku_tbl where we change sud_oper from 'in' to out.
I gave it a try :
Simple brute force , 40 lines of code
one table with 9 rows : all numbers
one recursion : each recursion fills in one more position
as recursion reaches 81 all positions are filled in.
Code:
with all_nbrs (k, ck) as
( select 1, '1' from sysibm.sysdummy1
union all
select k + 1, substr(char(k + 1),1,1) from all_nbrs where k + 1 <= 9 )
, test(lvl,sn) as
(select 0 , '090100000'
!! '037020000'
!! '060908004'
!! '009070000'
!! '206000107'
!! '000050300'
!! '700603050'
!! '000080630'
!! '000002040 ' from sysibm.sysdummy1
union all
select lvl + 1
, case when lvl= 0 then '' else substr(sn,1,lvl) end
!! a.ck
!! substr(sn,lvl+2)
from test t join all_nbrs a on 1=1
where lvl < 81
and ( substr(sn,lvl+1,1) = ck
or (substr(sn, lvl+1 ,1) = '0'
and locate( ck,substr(sn , int(lvl / 9)*9+1 ,9) ) = 0 -- ROW
and ck not in ( substr(sn , mod(lvl,9)+1 ,1)
, substr(sn , mod(lvl,9)+10 ,1)
, substr(sn , mod(lvl,9)+19 ,1)
, substr(sn , mod(lvl,9)+28 ,1)
, substr(sn , mod(lvl,9)+37 ,1)
, substr(sn , mod(lvl,9)+46 ,1)
, substr(sn , mod(lvl,9)+55 ,1)
, substr(sn , mod(lvl,9)+64 ,1)
, substr(sn , mod(lvl,9)+73 ,1) ) -- COL
and locate( ck , substr(sn , int(lvl/27)*27 + int(mod(lvl,9) / 3 ) * 3 + 1 ,3) ) = 0
and locate( ck , substr(sn , int(lvl/27)*27 + int(mod(lvl,9) / 3 ) * 3 + 10 ,3) ) = 0
and locate( ck , substr(sn , int(lvl/27)*27 + int(mod(lvl,9) / 3 ) * 3 + 19 ,3) ) = 0 -- BLOCK
) )
)
I want present to you Sudoku Solver, created by me today, which find solution for any level of Sudoku in very short time from 2 to 80 seconds, depends on difficulty level.
This solution prevents from infinite loop. So if solution exists it'll be found.
In this example I used the very hard sudoku and got solution in 2 minutes.