Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Unanswered: Sudoku in seconds (plain DB2)

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

    Very first table:


    X9X1XXXXX
    X37X2XXXX
    X6X9X8XX4
    XX9X7XXXX
    2X6XXX1X7
    XXXX5X3XX
    7XX6X3X5X
    XXXX8X63X
    XXXXX2X4X
    All "X" replaced by "0".
    Now we have another table, which I suppose to use in the query:

    Table after replace:


    090100000
    037020000
    060908004
    009070000
    206000107
    000050300
    700603050
    000080630
    000002040
    Then using the main rules of SUDOKU, I created the query.
    I will not explain the details how it works, but it works.

    And after few seconds I get the

    Final table:


    | 5 | 9 | 4 | 1 | 6 | 7 | 8 | 2 | 3 |
    | 8 | 3 | 7 | 4 | 2 | 5 | 9 | 1 | 6 |
    | 1 | 6 | 2 | 9 | 3 | 8 | 5 | 7 | 4 |
    | 3 | 8 | 9 | 2 | 7 | 1 | 4 | 6 | 5 |
    | 2 | 5 | 6 | 3 | 4 | 9 | 1 | 8 | 7 |
    | 4 | 7 | 1 | 8 | 5 | 6 | 3 | 9 | 2 |
    | 7 | 4 | 8 | 6 | 1 | 3 | 2 | 5 | 9 |
    | 9 | 2 | 5 | 7 | 8 | 4 | 6 | 3 | 1 |
    | 6 | 1 | 3 | 5 | 9 | 2 | 7 | 4 | 8 |
    If you suppose to use this query somewhere you have to reference on
    Leonid Khiger (author).

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

    Thank you, Lenny.


    The query:

    Sorry for big size.

    Lenny

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    The query (part 1)

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

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    The query (part 2):

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

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    The query (part 3):

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

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    The query (part 4):

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

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    The query (part 5):

    ,
    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;

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    If you want to try how it works, you have to copy all parts in one huge query and execute.

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

    Lenny

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Do I just build SudokuIn using my numbers and run the query? I'll send you a message with my email, please send me the complete query. Thx

  9. #9
    Join Date
    Jul 2009
    Posts
    150
    This is really good one ! Ossam !

    I never saw query like this before.


    Thanks.

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow The complete statement "Sudoku in seconds"

    Just COPY and USE. See an attachment.

    Lenny
    Attached Files Attached Files

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You have to replace

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

    Good Luck !

    Lenny

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Thanks, Lenny

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Copy an attachment and go ahead !
    Lenny

  14. #14
    Join Date
    Jul 2009
    Posts
    150

    Thumbs up QA approved

    Thank you, Lenny.

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

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

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

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


    Kara S.
    Last edited by DB2Plus; 09-02-09 at 00:17.

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Different way to solve Sudoku Puzzle (by single SQL)

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

    Sudoku Line
    Line 1: 5 | 9 | 4 | 1 | 6 | 7 | 8 | 2 | 3 |
    Line 2: 8 | 3 | 7 | 4 | 2 | 5 | 9 | 1 | 6 |
    Line 3: 1 | 6 | 2 | 9 | 3 | 8 | 5 | 7 | 4 |
    Line 4: 3 | 8 | 9 | 2 | 7 | 1 | 4 | 6 | 5 |
    Line 5: 2 | 5 | 6 | 3 | 4 | 9 | 1 | 8 | 7 |
    Line 6: 4 | 7 | 1 | 8 | 5 | 6 | 3 | 9 | 2 |
    Line 7: 7 | 4 | 8 | 6 | 1 | 3 | 2 | 5 | 9 |
    Line 8: 9 | 2 | 5 | 7 | 8 | 4 | 6 | 3 | 1 |
    Line 9: 6 | 1 | 3 | 5 | 9 | 2 | 7 | 4 | 8 |
    It's maybe interesting to understand by yourself how it works.
    So, I'll not explain.

    Open an attachment and run.

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

    Thanks everybody.
    Sincerely, Leonid Khiger.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •