LKhiger

/*
Any time when you add the new sudoku puzzle to the Sd_Source table
you have to use following query, or any query which
you want to use for insert start numbers of your Sudoku puzzle.
After insertion into the table performed, you can run
select sudoku_solution from SDVW_SOLUTION view.
*/

select max(sd_id) from final table
(insert into Sd_Source  
select ifnull(max(sd_id), 0) + 1, 1, '007206800'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())       
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 2, '006500010'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 3, '803000000'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all 
select ifnull(max(sd_id), 0) + 1, 4, '000060350'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 5, '070000080'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 6, '052000000'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 7, '000000703'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 8, '960003100'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 9, '005401900'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
);

select sudoku_solution from SDVW_SOLUTION;

Result:

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

Lenny

Интересно создать ещё одну функцию RandInt() , которая действует на всём интервале целых чисел:

CREATE FUNCTION RandInt()
RETURNS integer 
LANGUAGE SQL 
CONTAINS SQL 
NO EXTERNAL ACTION 
not DETERMINISTIC 
RETURN RandInt(-2147483648, 2147483647);

Эта функция без аргументов.

Интересно изучить её поведение. Даёт ли она целое близкое к 0 ?

Dice game / two players / 3 dices

We will use the RandInt(m, n) function, because of dice has numbers (in random sequence)
from 1 to 6, so it will be RandInt(1, 6) for each dice:

Select 
player1.dice1, player1.dice2, player1.dice3, 
player2.dice1, player2.dice2, player2.dice3, 
(player1.dice1 + player1.dice2 + player1.dice3) player1,
(player2.dice1 + player2.dice2 + player2.dice3) player2,
case when (player1.dice1 + player1.dice2 + player1.dice3) >
          (player2.dice1 + player2.dice2 + player2.dice3)
     then 'Player1  WIN !' 
     when (player1.dice1 + player1.dice2 + player1.dice3) <
          (player2.dice1 + player2.dice2 + player2.dice3)
     then 'Player2  WIN !' 
     else 'Tie (draw)' 
end result   
from
(select randint(1, 6) dice1, randint(1, 6) dice2, randint(1, 6) dice3
from sysibm.sysdummy1) player1
,
(select randint(1, 6) dice1, randint(1, 6) dice2, randint(1, 6) dice3
from sysibm.sysdummy1) player2 

Change RandInt function, override RandInt

The function RandInt(integer) is working now in intervals:
[0, X] when X > 0
[X, 0] when X < 0
[0, 1] when X in (0, 1)

drop function RandInt(integer);

CREATE FUNCTION RandInt (X integer) 
RETURNS integer 
LANGUAGE SQL 
CONTAINS SQL 
NO EXTERNAL ACTION 
not DETERMINISTIC 
RETURN 
case when X = 0 
then int(1 * Rand() + .5)
else int(X * Rand() + 0.5 * sign(X))
end
;

The function RandInt(integer, integer) is overriding RandInt(integer) and
working in intervals:
[start#, end#] when end# >= start#
[end#, start#] when end# <= start#

CREATE FUNCTION RandInt (start# integer, end# integer) 
RETURNS integer 
LANGUAGE SQL 
CONTAINS SQL 
NO EXTERNAL ACTION 
not DETERMINISTIC 
RETURN RandInt(end# - start#) + start#;

What could be also interesting to us:
Create statement of RandInt(integer, integer) function consists call to
parent function RandInt(integer).

Example of using new functions:

select 
randint(500) int1, randint(1000) int2, randint(500, 1000) int3
from sysibm.sysdummy1;

High Level Sudoku

/* Any time when you add the new sudoku puzzle to the Sd_Source table
you have to use following query, or any query which you want to use for insert
After insert into table, you can select sudoku_solution from SDVW_SOLUTION
*/

select max(sd_id) from final table
(insert into Sd_Source  
select ifnull(max(sd_id), 0) + 1, 1, '700800060'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())       
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 2, '000000001'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 3, '508609000'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all 
select ifnull(max(sd_id), 0) + 1, 4, '300700049'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 5, '000000000'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 6, '980006003'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 7, '000501906'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 8, '600000000'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
union all
select ifnull(max(sd_id), 0) + 1, 9, '170403005'
     , ifnull(max(sd_id), 0) + 1, timestamp(generate_unique())
  from Sd_Source 
);

select sudoku_solution from SDVW_SOLUTION;

Result:

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