Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes

 Forces of DB2 nature: SUDOKU Solver (in seconds) Goto page 1, 2  Next
Author Message
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

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

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.

lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Sat Sep 05, 2009 8:11 pm    Post subject:

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' )
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Sat Sep 05, 2009 8:18 pm    Post subject:

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 )
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Sat Sep 05, 2009 8:21 pm    Post subject:

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 )

lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Sat Sep 05, 2009 8:23 pm    Post subject:

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 )

lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Sat Sep 05, 2009 8:29 pm    Post subject:

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;

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

Lenny
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

 Posted: Sat Sep 05, 2009 11:51 pm    Post subject: Please respond on test of my SQL ! I will appreciate your respond and your comments (positive, or negatitive) on my SQL. Sincerely, L Khiger
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

 Posted: Mon Sep 07, 2009 1:06 pm    Post subject: Did you develop the algorithm as well ? What happens if my input is wrong or unsolvable ?
enrico-sorichetti

Global Moderator

Joined: 14 Mar 2007
Posts: 10211
Location: italy

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

 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 are : (c) Copyright yyyy-yyyy, and are released under the
a list of the files contained in the package
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Mon Sep 07, 2009 4:48 pm    Post subject:

 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
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

 Posted: Mon Sep 07, 2009 5:09 pm    Post subject: 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 ?
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Mon Sep 07, 2009 7:15 pm    Post subject:

 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
Terry Heinze

JCL Moderator

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

Posted: Mon Sep 07, 2009 7:22 pm    Post subject:

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

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Mon Sep 07, 2009 7:35 pm    Post subject:

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 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
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

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

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 are : (c) Copyright yyyy-yyyy, and are released under the
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.
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

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

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 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
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

 Posted: Mon Sep 07, 2009 8:30 pm    Post subject: I am sorry again for big size of the query, but this one 3 times shorter than previous. Lenny
dick scherrer

Site Director

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

Posted: Tue Sep 08, 2009 3:53 am    Post subject:

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".
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Tue Sep 08, 2009 4:10 am    Post subject:

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.

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
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Wed Sep 09, 2009 7:07 am    Post subject: Notes

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
 All times are GMT + 6 HoursGoto page 1, 2  Next
 Page 1 of 2

Search our Forum:

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

 © 2003-2016 IBM MAINFRAME Software Support Division
 Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us