Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Forces of DB2 nature: SUDOKU Solver (in seconds)
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 8:00 pm    Post subject: Forces of DB2 nature: SUDOKU Solver (in seconds)
Reply with quote

In the first step I just replaced all unknown sudoku-numbers, marked by me as "X" (instead of empty) by "0"s.
That's easy.

Very first table (from the source):
Quote:
X9X1XXXXX
X37X2XXXX
X6X9X8XX4
XX9X7XXXX
2X6XXX1X7
XXXX5X3XX
7XX6X3X5X
XXXX8X63X
XXXXX2X4X


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

This is the INPUT for Query:
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.

You can understand the logic from the text of the query.

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 |


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

Just copy the query from attachment and change the input.

Thank you, Lenny.
The query:
Sorry for big size.
Back to top
View user's profile Send private message

lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 8:11 pm    Post subject:
Reply with quote

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'
)
icon_arrow.gif icon_arrow.gif icon_arrow.gif
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 8:18 pm    Post subject:
Reply with quote

Part 2:

Code:
,
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
)
icon_arrow.gif icon_arrow.gif icon_arrow.gif
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 8:21 pm    Post subject:
Reply with quote

Part 3:

Code:
,
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
)

icon_arrow.gif icon_arrow.gif icon_arrow.gif
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 8:23 pm    Post subject:
Reply with quote

Part 4:

Code:
,
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
)

icon_arrow.gif icon_arrow.gif icon_arrow.gif
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 8:29 pm    Post subject:
Reply with quote

Part 5:
Code:
,
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; 

icon_exclaim.gif icon_exclaim.gif icon_exclaim.gif

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

Lenny
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sat Sep 05, 2009 11:51 pm    Post subject: Please respond on test of my SQL !
Reply with quote

I will appreciate your respond and your comments (positive, or negatitive) on my SQL.

Sincerely, L Khiger
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 07, 2009 1:06 pm    Post subject:
Reply with quote

Did you develop the algorithm as well ?

What happens if my input is wrong or unsolvable ?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Sep 07, 2009 2:10 pm    Post subject: Reply to: Forces of DB2 nature: SUDOKU Solver (in seconds)
Reply with quote

Quote:
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 http://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
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 07, 2009 4:48 pm    Post subject:
Reply with quote

GuyC wrote:
Did you develop the algorithm as well ?

What happens if my input is wrong or unsolvable ?


If your input is wrong you'll have the bad answer.

Yes, I have an algorithm. It's not a possible to create such big query without one.

Lenny
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 07, 2009 5:09 pm    Post subject:
Reply with quote

My question wasn't "Do you have one", it was
"Did you develop it yourself or used one (from the internet) and adopted it to sql?"


An additional question is :
I don't have to solve many sudoku's in my job. Does this Sql teach anything or is it just showing off ?
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 07, 2009 7:15 pm    Post subject:
Reply with quote

GuyC wrote:
My question wasn't "Do you have one", it was
"Did you develop it yourself or used one (from the internet) and adopted it to sql?"


An additional question is :
I don't have to solve many sudoku's in my job. Does this Sql teach anything or is it just showing off ?


]Thank you for your question.

I did develop algorithm by myself, using the rules of SUDOKU.

It shown the possibilty of DB2 and another view on it.

I did not create this SQL on my work, but at home.
It got near 2 weeks.

Thank you. Lenny
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Mon Sep 07, 2009 7:22 pm    Post subject:
Reply with quote

Lenny,
Did you not see the following request by enrico:
Quote:
Also You should have noticed that Your posts were edited to <unbold> them
please avoid doing it in the future
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 07, 2009 7:35 pm    Post subject:
Reply with quote

Terry Heinze wrote:
Lenny,
Did you not see the following request by enrico:
Quote:
Also You should have noticed that Your posts were edited to <unbold> them
please avoid doing it in the future

I am new on this Forum....

So, I am sorry about and will not use <BOLD> in the future.

Lenny
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 07, 2009 7:41 pm    Post subject: Re: Reply to: Forces of DB2 nature: SUDOKU Solver (in second
Reply with quote

enrico-sorichetti wrote:
Quote:
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 http://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". :

I see you are moderator and you can do it !

Thanks, Lenny.
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 07, 2009 8:20 pm    Post subject: The different way to solve SUDOKU puzzles (in minutes)
Reply with quote

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. icon_question.gif icon_idea.gif

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 2:    ' || varchar(s21) || ' | ' || varchar(s22) || ' | ' || varchar(s23) || ' | ' ||
                 varchar(s24) || ' | ' || varchar(s25) || ' | ' || varchar(s26) || ' | ' ||
                 varchar(s27) || ' | ' || varchar(s28) || ' | ' || varchar(s29) || ' | '    Line_2,
 
'Line 3:    ' || varchar(s31) || ' | ' || varchar(s32) || ' | ' || varchar(s33) || ' | ' ||
                 varchar(s34) || ' | ' || varchar(s35) || ' | ' || varchar(s36) || ' | ' ||
                 varchar(s37) || ' | ' || varchar(s38) || ' | ' || varchar(s39) || ' | '    Line_3,

'Line 4:    ' || varchar(s41) || ' | ' || varchar(s42) || ' | ' || varchar(s43) || ' | ' ||
                 varchar(s44) || ' | ' || varchar(s45) || ' | ' || varchar(s46) || ' | ' ||
                 varchar(s47) || ' | ' || varchar(s48) || ' | ' || varchar(s49) || ' | '    Line_4,

'Line 5:    ' || varchar(s51) || ' | ' || varchar(s52) || ' | ' || varchar(s53) || ' | ' ||
                 varchar(s54) || ' | ' || varchar(s55) || ' | ' || varchar(s56) || ' | ' ||
                 varchar(s57) || ' | ' || varchar(s58) || ' | ' || varchar(s59) || ' | '    Line_5,

'Line 6:    ' || varchar(s61) || ' | ' || varchar(s62) || ' | ' || varchar(s63) || ' | ' ||
                 varchar(s64) || ' | ' || varchar(s65) || ' | ' || varchar(s66) || ' | ' ||
                 varchar(s67) || ' | ' || varchar(s68) || ' | ' || varchar(s69) || ' | '    Line_6,

'Line 7:    ' || varchar(s71) || ' | ' || varchar(s72) || ' | ' || varchar(s73) || ' | ' ||
                 varchar(s74) || ' | ' || varchar(s75) || ' | ' || varchar(s76) || ' | ' ||
                 varchar(s77) || ' | ' || varchar(s78) || ' | ' || varchar(s79) || ' | '    Line_7,

'Line 8:    ' || varchar(s81) || ' | ' || varchar(s82) || ' | ' || varchar(s83) || ' | ' ||
                 varchar(s84) || ' | ' || varchar(s85) || ' | ' || varchar(s86) || ' | ' ||
                 varchar(s87) || ' | ' || varchar(s88) || ' | ' || varchar(s89) || ' | '    Line_8,

'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

where
    s11 + s12 + s13 + s14 + s15 + s16 + s17 + s18 + s19 = 45
and s21 + s22 + s23 + s24 + s25 + s26 + s27 + s28 + s29 = 45
and s31 + s32 + s33 + s34 + s35 + s36 + s37 + s38 + s39 = 45
and s41 + s42 + s43 + s44 + s45 + s46 + s47 + s48 + s49 = 45
and s51 + s52 + s53 + s54 + s55 + s56 + s57 + s58 + s59 = 45
and s61 + s62 + s63 + s64 + s65 + s66 + s67 + s68 + s69 = 45
and s71 + s72 + s73 + s74 + s75 + s76 + s77 + s78 + s79 = 45
and s81 + s82 + s83 + s84 + s85 + S86 + s87 + s88 + s89 = 45
and s91 + s92 + s93 + s94 + s95 + S96 + s97 + s98 + s99 = 45

and s11Y + s12Y + s13Y + s14Y + s15Y + s16Y + s17Y + s18Y + s19Y = 1022
and s21Y + s22Y + s23Y + s24Y + s25Y + s26Y + s27Y + s28Y + s29Y = 1022
and s31Y + s32Y + s33Y + s34Y + s35Y + s36Y + s37Y + s38Y + s39Y = 1022
and s41Y + s42Y + s43Y + s44Y + s45Y + s46Y + s47Y + s48Y + s49Y = 1022
and s51Y + s52Y + s53Y + s54Y + s55Y + s56Y + s57Y + s58Y + s59Y = 1022
and s61Y + s62Y + s63Y + s64Y + s65Y + s66Y + s67Y + s68Y + s69Y = 1022
and s71Y + s72Y + s73Y + s74Y + s75Y + s76Y + s77Y + s78Y + s79Y = 1022
and s81Y + s82Y + s83Y + s84Y + s85Y + S86Y + s87Y + s88Y + s89Y = 1022
and s91Y + s92Y + s93Y + s94Y + s95Y + S96Y + s97Y + s98Y + s99Y = 1022
   
and s11 + s21 + s31 + s41 + s51 + s61 + s71 + s81 + s91 = 45
and s12 + s22 + s32 + s42 + s52 + s62 + s72 + s82 + s92 = 45
and s13 + s23 + s33 + s43 + s53 + s63 + s73 + s83 + s93 = 45
and s14 + s24 + s34 + s44 + s54 + s64 + s74 + s84 + s94 = 45
and s15 + s25 + s35 + s45 + s55 + s65 + s75 + s85 + s95 = 45
and s16 + s26 + s36 + s46 + s56 + s66 + s76 + S86 + s96 = 45
and s17 + s27 + s37 + s47 + s57 + s67 + s77 + s87 + s97 = 45
and s18 + s28 + s38 + s48 + s58 + s68 + s78 + s88 + s98 = 45
and s19 + s29 + s39 + s49 + s59 + s69 + s79 + s89 + s99 = 45

and s11Y + s21Y + s31Y + s41Y + s51Y + s61Y + s71Y + s81Y + s91Y = 1022
and s12Y + s22Y + s32Y + s42Y + s52Y + s62Y + s72Y + s82Y + s92Y = 1022
and s13Y + s23Y + s33Y + s43Y + s53Y + s63Y + s73Y + s83Y + s93Y = 1022
and s14Y + s24Y + s34Y + s44Y + s54Y + s64Y + s74Y + s84Y + s94Y = 1022
and s15Y + s25Y + s35Y + s45Y + s55Y + s65Y + s75Y + s85Y + s95Y = 1022
and s16Y + s26Y + s36Y + s46Y + s56Y + s66Y + s76Y + S86Y + s96Y = 1022
and s17Y + s27Y + s37Y + s47Y + s57Y + s67Y + s77Y + s87Y + s97Y = 1022
and s18Y + s28Y + s38Y + s48Y + s58Y + s68Y + s78Y + s88Y + s98Y = 1022
and s19Y + s29Y + s39Y + s49Y + s59Y + s69Y + s79Y + s89Y + s99Y = 1022

and s11 + s12 + s13 + s21 + s22 + s23 + s31 + s32 + s33 = 45
and s14 + s15 + s16 + s24 + s25 + s26 + s34 + s35 + s36 = 45
and s17 + s18 + s19 + s27 + s28 + s29 + s37 + s38 + s39 = 45
and s41 + s42 + s43 + s51 + s52 + s53 + s61 + s62 + s63 = 45
and s44 + s45 + s46 + s54 + s55 + s56 + s64 + s65 + s66 = 45
and s47 + s48 + s49 + s57 + s58 + s59 + s67 + s68 + s69 = 45
and s71 + s72 + s73 + s81 + s82 + s83 + s91 + s92 + s93 = 45
and s74 + s75 + s76 + s84 + s85 + S86 + s94 + s95 + s96 = 45
and s77 + s78 + s79 + s87 + s88 + s89 + s97 + s98 + s99 = 45

and s11Y + s12Y + s13Y + s21Y + s22Y + s23Y + s31Y + s32Y + s33Y = 1022
and s14Y + s15Y + s16Y + s24Y + s25Y + s26Y + s34Y + s35Y + s36Y = 1022
and s17Y + s18Y + s19Y + s27Y + s28Y + s29Y + s37Y + s38Y + s39Y = 1022
and s41Y + s42Y + s43Y + s51Y + s52Y + s53Y + s61Y + s62Y + s63Y = 1022
and s44Y + s45Y + s46Y + s54Y + s55Y + s56Y + s64Y + s65Y + s66Y = 1022
and s47Y + s48Y + s49Y + s57Y + s58Y + s59Y + s67Y + s68Y + s69Y = 1022
and s71Y + s72Y + s73Y + s81Y + s82Y + s83Y + s91Y + s92Y + s93Y = 1022
and s74Y + s75Y + s76Y + s84Y + s85Y + S86Y + s94Y + s95Y + s96Y = 1022
and s77Y + s78Y + s79Y + s87Y + s88Y + s89Y + s97Y + s98Y + s99Y = 1022
)
select Line_1 as "Sudoku Line" from Sudoku_Final
Union All
select Line_2 as "Sudoku Line" from Sudoku_Final
Union All
select Line_3 as "Sudoku Line" from Sudoku_Final
Union All
select Line_4 as "Sudoku Line" from Sudoku_Final
Union All
select Line_5 as "Sudoku Line" from Sudoku_Final
Union All
select Line_6 as "Sudoku Line" from Sudoku_Final
Union All
select Line_7 as "Sudoku Line" from Sudoku_Final
Union All
select Line_8 as "Sudoku Line" from Sudoku_Final
Union All
select Line_9 as "Sudoku Line" from Sudoku_Final
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 07, 2009 8:30 pm    Post subject:
Reply with quote

I am sorry again for big size of the query, but this one 3 times shorter than previous.

Lenny
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 08, 2009 3:53 am    Post subject:
Reply with quote

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".
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Tue Sep 08, 2009 4:10 am    Post subject:
Reply with quote

dick scherrer wrote:
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".

Oh, thanks. icon_exclaim.gif

But do not delete the last one. This code is different from the code in the attachment. Based on the different ideas.

Thank you, Lenny
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 09, 2009 7:07 am    Post subject: Notes
Reply with quote

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.

Thanks, Lenny
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is Addressing Issue depends on nature... Robert Sample COBOL Programming 7 Sun Sep 09, 2012 11:13 pm
No new posts Sort: Does INREC forces a "pass ... Anuj Dhawan JCL & VSAM 10 Wed Mar 31, 2010 4:34 pm
No new posts DB2 Sql to perform commit operation e... dick scherrer DB2 7 Wed Sep 10, 2008 11:03 am
No new posts Summing Minutes:Seconds on a file? Div Grad DFSORT/ICETOOL 2 Fri Sep 08, 2006 1:18 am
No new posts Timestamp difference in seconds - DB2... mrajagop DB2 2 Thu Jul 06, 2006 3:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us