| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
02-03-10, 15:45
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Lottery Numbers Generator (LNG)
|
|
I created this query for MegaMillion USA lottery, where sometimes somebody could win millions dollars.
But if you could understood how this query works, you may change it to play your national lottery.
To play MegaMillion lottery ==> you have to select 5 numbers from 1 to 56 and 1 number from 1 to 46 (Mega Ball)....
Code:
with Lotto_tickets (tickets, R, K, Lsw, L1, L2, L3, L4, L5, SB) as
(select int(77),int(0),int(0), 'N',
varchar('', 2), varchar('', 2), varchar('', 2),
varchar('', 2), varchar('', 2), varchar('', 2)
from sysibm.sysdummy1
union all
select tickets, R + 12,
K + case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 1
else 0
end,
case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 'Y'
else 'N'
end,
M1, M2, M3, M4, M5, MB
from Lotto_tickets lt,
table (
select max(L1) M1, max(L2) M2, max(L3) M3,
max(L4) M4, max(L5) M5, max(SB) MB
from
(select L1, L2, L3, L4, L5, SB
from
(select L1, L2, L3, L4, L5, SB
from
(select
substr(varchar(rand(R)), 3, 1) ||
substr(varchar(rand(R + 1)), 4, 1) L1,
substr(varchar(rand(R + 2)), 5, 1) ||
substr(varchar(rand(R + 3)), 6, 1) L2,
substr(varchar(rand(R + 4)), 7, 1) ||
substr(varchar(rand(R + 5)), 8, 1) L3,
substr(varchar(rand(R + 6)), 9, 1) ||
substr(varchar(rand(R + 7)), 10, 1) L4,
substr(varchar(rand(R + 8)), 10, 1) ||
substr(varchar(rand(R + 9)), 11, 1) L5,
substr(varchar(rand(R + 10)), 12, 1) ||
substr(varchar(rand(R + 11)), 13, 1) SB
from sysibm.sysdummy1 ) rd1
where
L1 between '01' and '56' and
L2 between '01' and '56' and
L3 between '01' and '56' and
L4 between '01' and '56' and
L5 between '01' and '56' and
SB between '01' and '46'
) rd2
Where
L1 not in (L2, L3, L4, L5) and
L2 not in (L1, L3, L4, L5) and
L3 not in (L1, L2, L4, L5) and
L4 not in (L1, L2, L3, L5) and
L5 not in (L1, L2, L3, L4)
union all
select '00' L1, '00' L2, '00' L3, '00' L4, '00' L5, '00' SB
from sysibm.sysdummy1 ) rd3 ) rdm
where K <= tickets - 1
)
select L1, L2, L3, L4, L5, SB "Super Ball"
from Lotto_tickets
where Lsw = 'Y'
order by 6
Lenny
P.S. If you win something, using this generator, don't forget about me.... 
|
|
02-03-10, 17:25
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
|
Last edited by db2girl; 02-03-10 at 17:29.
|
02-03-10, 17:50
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Query without VARCHAR
|
|
Sorry, Bella !
I forgot about your problem with VARCHAR.... 
You can use following SQL:
Code:
with Lotto_tickets (tickets, R, K, Lsw, L1, L2, L3, L4, L5, SB) as
(select int(77),int(0),int(0), 'N',
char('', 2), char('', 2), char('', 2),
char('', 2), char('', 2), char('', 2)
from sysibm.sysdummy1
union all
select tickets, R + 12,
K + case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 1
else 0
end,
case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 'Y'
else 'N'
end,
M1, M2, M3, M4, M5, MB
from Lotto_tickets lt,
table (
select max(L1) M1, max(L2) M2, max(L3) M3,
max(L4) M4, max(L5) M5, max(SB) MB
from
(select L1, L2, L3, L4, L5, SB
from
(select L1, L2, L3, L4, L5, SB
from
(select
substr(char(rand(R)), 3, 1) ||
substr(char(rand(R + 1)), 4, 1) L1,
substr(char(rand(R + 2)), 5, 1) ||
substr(char(rand(R + 3)), 6, 1) L2,
substr(char(rand(R + 4)), 7, 1) ||
substr(char(rand(R + 5)), 8, 1) L3,
substr(char(rand(R + 6)), 9, 1) ||
substr(char(rand(R + 7)), 10, 1) L4,
substr(char(rand(R + 8)), 10, 1) ||
substr(char(rand(R + 9)), 11, 1) L5,
substr(char(rand(R + 10)), 12, 1) ||
substr(char(rand(R + 11)), 13, 1) SB
from sysibm.sysdummy1 ) rd1
where
L1 between '01' and '56' and
L2 between '01' and '56' and
L3 between '01' and '56' and
L4 between '01' and '56' and
L5 between '01' and '56' and
SB between '01' and '46'
) rd2
Where
L1 not in (L2, L3, L4, L5) and
L2 not in (L1, L3, L4, L5) and
L3 not in (L1, L2, L4, L5) and
L4 not in (L1, L2, L3, L5) and
L5 not in (L1, L2, L3, L4)
union all
select '00' L1, '00' L2, '00' L3, '00' L4, '00' L5, '00' SB
from sysibm.sysdummy1 ) rd3 ) rdm
where K <= tickets - 1
)
select L1, L2, L3, L4, L5, SB "Super Ball"
from Lotto_tickets
where Lsw = 'Y'
order by 1
Lenny
|
|
02-03-10, 18:18
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
Lenny, I'm now getting:
SQL0204N "R" is an undefined name. SQLSTATE=42704
|
|
02-04-10, 10:13
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Question
Quote:
Originally Posted by db2girl
Lenny, I'm now getting:
SQL0204N "R" is an undefined name. SQLSTATE=42704
|
Why sqlcode = -204 ?
" R" is not a table name, but column name.
Could be -206, but not -204...
Check your copy of the query.
You have to run whole query, not a part. Check ", table" .
You can't ommit the word "table".
Lenny
|
|
02-04-10, 11:13
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Line Sorted Lottery Numbers Generator (SLNG)
To create "Line Sorted Lottery Numbers Generator (SLNG)" only small change needed:
Code:
with Lotto_tickets_sorted
(tickets, R, K, Lsw, L1, L2, L3, L4, L5, SB) as
(select int(20),int(0),int(0), 'N',
char('', 2), char('', 2), char('', 2),
char('', 2), char('', 2), char('', 2)
from sysibm.sysdummy1
union all
select tickets, R + 12,
K + case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 1
else 0
end,
case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 'Y'
else 'N'
end,
M1, M2, M3, M4, M5, MB
from Lotto_tickets_sorted lt,
table (
select max(L1) M1, max(L2) M2, max(L3) M3,
max(L4) M4, max(L5) M5, max(SB) MB
from
(select L1, L2, L3, L4, L5, SB
from
(select L1, L2, L3, L4, L5, SB
from
(select
substr(char(rand(R)) , 3, 1) || substr(char(rand(R + 1)), 4, 1) L1,
substr(char(rand(R + 2)), 5, 1) || substr(char(rand(R + 3)), 6, 1) L2,
substr(char(rand(R + 4)), 7, 1) || substr(char(rand(R + 5)), 8, 1) L3,
substr(char(rand(R + 6)), 9, 1) || substr(char(rand(R + 7)), 10,1) L4,
substr(char(rand(R + 8)), 10,1) || substr(char(rand(R + 9)), 11,1) L5,
substr(char(rand(R + 10)),12,1) || substr(char(rand(R + 11)),13,1) SB
from sysibm.sysdummy1 ) rd1
where
L1 between '01' and '56' and
L2 between '01' and '56' and
L3 between '01' and '56' and
L4 between '01' and '56' and
L5 between '01' and '56' and
SB between '01' and '46'
) rd2
Where
L1 < L2 and
L2 < L3 and
L3 < L4 and
L4 < L5
Union All
select '00' L1, '00' L2, '00' L3, '00' L4, '00' L5, '00' SB
from sysibm.sysdummy1 ) rd3 ) rdm
where K <= tickets - 1
)
select distinct L1, L2, L3, L4, L5, SB "Super Ball"
from Lotto_tickets_sorted
where Lsw = 'Y'
Result will have more elegant view, but query is working much slower:
Quote:
L1 L2 L3 L4 L5 Super Ball
01 07 13 24 53 30
02 05 25 36 38 39
02 09 11 25 34 32
03 17 28 30 37 40
04 05 20 33 50 22
05 19 22 44 54 20
07 16 21 27 36 31
08 09 20 47 56 02
09 13 20 30 55 45
09 22 24 26 49 14
10 11 24 53 56 09
10 24 38 40 47 18
11 36 47 52 56 18
12 25 26 30 39 22
12 29 36 37 38 45
14 18 44 47 50 07
14 40 45 47 51 31
15 21 39 52 54 03
20 22 42 47 51 22
26 39 46 47 55 22
|
Lenny
|
Last edited by Lenny77; 02-04-10 at 11:19.
|
02-04-10, 11:36
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Lenny, I'm now getting:
SQL0204N "R" is an undefined name. SQLSTATE=42704
__________________
Bella
|
The reason might be that Bella executed the query on DB2 for LUW, Lenny executed on DB2 for z/OS.
Bella,
please try to add three more "table"(or "lateral") keywords, like this...
Code:
.....
from Lotto_tickets lt,
table (
select max(L1) M1, max(L2) M2, max(L3) M3,
max(L4) M4, max(L5) M5, max(SB) MB
from
lateral
(select L1, L2, L3, L4, L5, SB
from
lateral
(select L1, L2, L3, L4, L5, SB
from
lateral
(select
substr(char(rand(R)), 3, 1) ||
.....
|
|
02-05-10, 13:08
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Gosloto: Russia's new lottery <6 from 45> GENERATOR
Russian Lottery - Gosloto: Russia's New Lotto
Gosloto is Russia's new lottery; it started in October of 2008 with a 6/45 game. The odds of winning the jackpot in a 6/45 game are 1 in 8,145,060. Albert Begrakyan managed to beat the odds and won 100 million rubles ($2,968,372 USD) in April of 2009.
Albert Begrakyan's win was the biggest ever in Gosloto's short history. He was so afraid of somebody finding out that he won the lottery that he hired bodyguards to travel with him to Moscow to claim his prize. But he got his money and is now safe and sound with the money in the bank.
Russia's lottery is way different than lotteries, say, in North America. Canadian and American lotteries are government run, whereas Russia's new Gosloto is run by a private businessman. His name is Sandor Demjan. This is crazy. As most people probably know, lotteries generate billions in profit. And, it is usually a government monopoly as private businesses are prohibited from creating a lottery. Giving a private businessman the opportunity to create a lottery is like him winning the lottery himself. But instead of winning millions, he wins billions. Having the only lottery franchise in a country like Russia is like starting the most lucrative business ever - with absolutely no risk whatsoever. It really is crazy.
Now, Sandor Demjan does have to give a percentage of all lottery sales from Gosloto to government programs. We couldn't find out the exact percentage, but we still think this situation is crazy.
Russian Lottery Winner Albert Begrakyan - Gosloto - Russia's New Lotto
Code:
with Goslotto_tickets (tickets, R, K, Lsw, L1, L2, L3, L4, L5, L6) as
(select int(77),int(0),int(0), 'N',
char('', 2), char('', 2), char('', 2),
char('', 2), char('', 2), char('', 2)
from sysibm.sysdummy1
union all
select tickets, R + 12,
K + case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and M6 > '00'
then 1
else 0
end,
case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and M6 > '00'
then 'Y'
else 'N'
end,
M1, M2, M3, M4, M5, M6
from Goslotto_tickets lt,
table (
select max(L1) M1, max(L2) M2, max(L3) M3,
max(L4) M4, max(L5) M5, max(L6) M6
from table
(select L1, L2, L3, L4, L5, L6
from table
(select L1, L2, L3, L4, L5, L6
from table
(select
substr(char(rand(R)), 3, 1) ||
substr(char(rand(R + 1)), 4, 1) L1,
substr(char(rand(R + 2)), 5, 1) ||
substr(char(rand(R + 3)), 6, 1) L2,
substr(char(rand(R + 4)), 7, 1) ||
substr(char(rand(R + 5)), 8, 1) L3,
substr(char(rand(R + 6)), 9, 1) ||
substr(char(rand(R + 7)), 10, 1) L4,
substr(char(rand(R + 8)), 10, 1) ||
substr(char(rand(R + 9)), 11, 1) L5,
substr(char(rand(R + 10)), 12, 1) ||
substr(char(rand(R + 11)), 13, 1) L6
from sysibm.sysdummy1 ) rd1
where
L1 between '01' and '45' and
L2 between '01' and '45' and
L3 between '01' and '45' and
L4 between '01' and '45' and
L5 between '01' and '45' and
L6 between '01' and '45'
) rd2
Where
L1 not in (L2, L3, L4, L5, L6) and
L2 not in (L1, L3, L4, L5, L6) and
L3 not in (L1, L2, L4, L5, L6) and
L4 not in (L1, L2, L3, L5, L6) and
L6 not in (L1, L2, L3, L4, L5)
union all
select '00' L1, '00' L2, '00' L3, '00' L4, '00' L5, '00' L6
from sysibm.sysdummy1 ) rd3 ) rdm
where K <= tickets - 1
)
select distinct L1, L2, L3, L4, L5, L6
from Goslotto_tickets
where Lsw = 'Y'
Quote:
L1 L2 L3 L4 L5 L6
01 04 24 15 12 08
01 29 30 17 07 24
01 32 39 26 45 19
04 18 45 36 17 07
04 26 35 06 31 24
04 28 21 29 03 41
05 11 26 15 28 42
05 15 45 14 32 39
05 22 35 39 43 08
06 10 17 41 01 13
06 40 34 12 44 03
07 21 24 18 32 11
07 26 28 12 19 30
08 18 25 28 12 09
08 31 19 06 39 11
09 07 02 45 36 33
09 10 25 12 04 36
09 14 12 29 31 01
09 18 19 07 15 38
09 22 30 36 23 21
09 33 21 25 20 14
10 06 31 24 21 34
10 37 07 01 24 03
10 38 26 27 34 08
11 35 21 08 28 02
12 15 37 21 01 03
13 35 11 25 31 45
14 39 34 05 30 40
14 41 07 36 22 05
15 34 07 01 18 33
15 38 23 21 39 09
16 20 10 23 45 07
16 38 26 32 11 30
17 15 03 22 21 28
18 01 10 44 25 40
18 03 31 20 08 37
18 09 13 32 42 33
18 42 41 20 11 21
18 44 30 02 37 16
19 13 11 16 14 34
19 22 05 14 37 43
20 12 23 29 14 27
20 12 42 35 28 14
20 18 15 39 42 23
21 18 12 01 25 37
21 44 32 45 10 02
24 06 25 05 43 23
25 09 37 01 22 29
25 22 17 09 15 18
26 11 22 34 03 45
27 02 33 04 25 38
27 24 42 41 15 11
28 12 25 27 07 36
29 35 30 38 34 15
29 41 17 24 13 33
29 42 15 06 32 09
30 12 03 45 07 01
30 17 28 12 41 15
30 37 01 17 15 40
31 34 04 41 19 25
32 07 36 27 02 33
33 37 31 10 43 22
34 12 19 14 08 44
34 22 26 04 36 25
35 24 30 06 42 32
37 14 06 09 30 07
37 42 07 16 23 02
38 05 16 25 39 32
39 09 08 05 37 33
39 14 26 37 42 30
41 08 39 14 01 44
41 33 02 10 36 11
42 16 13 27 15 43
42 21 17 35 10 43
42 32 33 13 25 28
45 23 20 02 38 08
45 34 10 28 21 07
|
Lenny
|
Last edited by Lenny77; 02-05-10 at 13:12.
|
02-05-10, 15:30
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Hi, tomkuma !
Can you explain me how is Japanes Lottery works ?
Lenny
|
|
02-05-10, 17:25
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
02-05-10, 17:39
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
|
To be, or not ?
|
02-08-10, 18:02
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
All winning numbers for MegaMillions for last year
02/05/2010 10 20 45 51 53 41
02/02/2010 06 07 26 27 49 09
01/29/2010 01 10 22 23 38 19
01/26/2010 07 08 38 39 48 22
01/22/2010 08 13 27 28 52 09
01/19/2010 04 05 34 38 41 18
01/15/2010 04 07 19 21 38 29
01/12/2010 05 09 31 37 56 16
01/08/2010 18 20 31 36 43 33
01/05/2010 13 28 39 50 55 10
01/01/2010 06 08 27 40 41 21
12/29/2009 02 05 29 35 51 03
12/25/2009 04 16 44 52 54 20
12/22/2009 03 33 35 39 45 13
12/18/2009 10 20 30 44 49 24
12/15/2009 27 31 32 36 47 35
12/11/2009 02 21 27 34 44 45
12/08/2009 20 23 28 30 46 29
12/04/2009 04 33 41 51 56 38
12/01/2009 17 30 39 52 53 21
11/27/2009 12 25 37 39 45 11
11/24/2009 10 11 35 37 50 12
11/20/2009 03 04 07 16 56 39
11/17/2009 08 22 27 49 50 28
11/13/2009 27 43 45 49 54 44
11/10/2009 08 21 29 34 37 15
11/06/2009 07 11 27 40 46 08
11/03/2009 05 18 23 31 38 20
10/30/2009 04 15 17 29 38 20
10/27/2009 07 13 37 46 51 02
10/23/2009 27 45 48 54 56 02
10/20/2009 13 17 25 45 55 08
10/16/2009 10 13 18 33 51 43
10/13/2009 17 31 34 45 51 24
10/09/2009 03 14 21 24 51 14
10/06/2009 09 33 51 53 56 39
10/02/2009 15 24 51 53 55 11
09/29/2009 02 21 25 45 50 21
09/25/2009 07 27 42 49 52 19
09/22/2009 26 29 33 39 46 24
09/18/2009 18 27 31 36 52 33
09/15/2009 29 30 32 35 41 39
09/11/2009 16 27 48 49 54 02
09/08/2009 04 10 18 28 50 35
09/04/2009 02 04 06 21 44 37
09/01/2009 02 09 28 51 53 19
08/28/2009 01 17 31 37 54 31
08/25/2009 03 12 19 22 40 02
08/21/2009 09 38 44 48 49 13
08/18/2009 04 05 26 37 56 25
08/14/2009 08 22 25 33 35 46
08/11/2009 06 12 15 32 42 40
08/07/2009 07 18 35 45 56 03
08/04/2009 01 28 34 42 50 27
07/31/2009 17 35 44 52 56 22
07/28/2009 04 17 24 25 48 34
07/24/2009 03 06 43 51 52 36
07/21/2009 03 08 21 50 52 02
07/17/2009 08 17 40 47 50 13
07/14/2009 20 29 35 45 53 41
07/10/2009 05 10 26 27 28 04
07/07/2009 25 27 35 38 39 28
07/03/2009 05 06 07 11 25 31
06/30/2009 07 34 49 54 55 34
06/26/2009 11 15 17 29 46 16
06/23/2009 12 14 16 31 50 09
06/19/2009 04 09 12 16 46 44
06/16/2009 09 15 21 26 45 25
06/12/2009 06 11 20 32 44 38
06/09/2009 05 17 31 36 56 46
06/05/2009 05 20 38 41 52 20
06/02/2009 09 13 26 30 35 33
05/29/2009 23 30 36 39 48 34
05/26/2009 09 18 21 37 55 33
05/22/2009 03 29 34 42 49 29
05/19/2009 02 04 09 15 42 13
05/15/2009 07 12 24 36 48 27
05/12/2009 04 08 10 14 51 31
05/08/2009 12 27 29 32 34 34
05/05/2009 05 23 38 39 54 03
05/01/2009 09 16 24 40 43 35
04/28/2009 15 20 24 36 44 06
04/24/2009 09 20 21 48 49 07
04/21/2009 05 24 37 47 52 06
04/17/2009 05 13 26 35 45 32
04/14/2009 04 11 22 48 50 42
04/10/2009 18 25 36 42 51 22
04/07/2009 02 04 13 17 36 15
04/03/2009 16 22 38 39 48 42
03/31/2009 14 39 47 48 53 29
03/27/2009 10 15 24 38 50 19
03/24/2009 04 25 34 43 44 45
03/20/2009 15 16 20 39 40 26
03/17/2009 09 15 24 28 31 17
03/13/2009 10 12 26 46 50 23
03/10/2009 02 27 31 39 40 23
03/06/2009 11 18 37 46 55 45
03/03/2009 26 32 35 43 52 10
02/27/2009 24 37 44 50 56 35
02/24/2009 01 27 28 35 40 06
02/20/2009 21 28 41 45 55 24
02/17/2009 01 09 23 27 33 24
02/13/2009 25 36 37 40 51 20
02/10/2009 18 29 38 43 47 07
I want to change my generator to get generated numbers only if we have 2 or less for any of these numbers.
Lenny
|
|
02-08-10, 18:10
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Complete Generator, using information for last 28 weeks
Complete code of Random Lottery Generator, using official information for the last 28 weeks (MegaMillions):
Code:
with past_lot_win_int (S1, S2, S3, S4, S5, SS) as
(
select 10,20,45,51,53,41 from sysibm.sysdummy1 union all
select 06,07,26,27,49,09 from sysibm.sysdummy1 union all
select 01,10,22,23,38,19 from sysibm.sysdummy1 union all
select 07,08,38,39,48,22 from sysibm.sysdummy1 union all
select 08,13,27,28,52,09 from sysibm.sysdummy1 union all
select 04,05,34,38,41,18 from sysibm.sysdummy1 union all
select 04,07,19,21,38,29 from sysibm.sysdummy1 union all
select 05,09,31,37,56,16 from sysibm.sysdummy1 union all
select 18,20,31,36,43,33 from sysibm.sysdummy1 union all
select 13,28,39,50,55,10 from sysibm.sysdummy1 union all
select 06,08,27,40,41,21 from sysibm.sysdummy1 union all
select 02,05,29,35,51,03 from sysibm.sysdummy1 union all
select 04,16,44,52,54,20 from sysibm.sysdummy1 union all
select 03,33,35,39,45,13 from sysibm.sysdummy1 union all
select 10,20,30,44,49,24 from sysibm.sysdummy1 union all
select 27,31,32,36,47,35 from sysibm.sysdummy1 union all
select 02,21,27,34,44,45 from sysibm.sysdummy1 union all
select 20,23,28,30,46,29 from sysibm.sysdummy1 union all
select 04,33,41,51,56,38 from sysibm.sysdummy1 union all
select 17,30,39,52,53,21 from sysibm.sysdummy1 union all
select 12,25,37,39,45,11 from sysibm.sysdummy1 union all
select 10,11,35,37,50,12 from sysibm.sysdummy1 union all
select 03,04,07,16,56,39 from sysibm.sysdummy1 union all
select 08,22,27,49,50,28 from sysibm.sysdummy1 union all
select 27,43,45,49,54,44 from sysibm.sysdummy1 union all
select 08,21,29,34,37,15 from sysibm.sysdummy1 union all
select 07,11,27,40,46,08 from sysibm.sysdummy1 union all
select 05,18,23,31,38,20 from sysibm.sysdummy1 union all
select 04,15,17,29,38,20 from sysibm.sysdummy1 union all
select 07,13,37,46,51,02 from sysibm.sysdummy1 union all
select 27,45,48,54,56,02 from sysibm.sysdummy1 union all
select 13,17,25,45,55,08 from sysibm.sysdummy1 union all
select 10,13,18,33,51,43 from sysibm.sysdummy1 union all
select 17,31,34,45,51,24 from sysibm.sysdummy1 union all
select 03,14,21,24,51,14 from sysibm.sysdummy1 union all
select 09,33,51,53,56,39 from sysibm.sysdummy1 union all
select 15,24,51,53,55,11 from sysibm.sysdummy1 union all
select 02,21,25,45,50,21 from sysibm.sysdummy1 union all
select 07,27,42,49,52,19 from sysibm.sysdummy1 union all
select 26,29,33,39,46,24 from sysibm.sysdummy1 union all
select 18,27,31,36,52,33 from sysibm.sysdummy1 union all
select 29,30,32,35,41,39 from sysibm.sysdummy1 union all
select 16,27,48,49,54,02 from sysibm.sysdummy1 union all
select 04,10,18,28,50,35 from sysibm.sysdummy1 union all
select 02,04,06,21,44,37 from sysibm.sysdummy1 union all
select 02,09,28,51,53,19 from sysibm.sysdummy1
)
,
past_lot_win_Ch2 (S1, S2, S3, S4, S5, SS) as
(select right(digits(S1), 2),right(digits(S2), 2),
right(digits(S3), 2),right(digits(S4), 2),
right(digits(S5), 2),right(digits(SS), 2)
from past_lot_win_int
)
,
Lotto_tickets (tickets, R, K, Lsw, L1, L2, L3, L4, L5, SB) as
(select int(20),int(0),int(0), 'N',
char('', 2), char('', 2), char('', 2),
char('', 2), char('', 2), char('', 2)
from sysibm.sysdummy1
union all
select tickets, R + 12,
K + case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 1
else 0
end,
case when M1 > '00' and M2 > '00' and M3 > '00' and
M4 > '00' and M5 > '00' and MB > '00'
then 'Y'
else 'N'
end,
M1, M2, M3, M4, M5, MB
from Lotto_tickets lt,
TABLE (
select max(L1) M1, max(L2) M2, max(L3) M3,
max(L4) M4, max(L5) M5, max(SB) MB
from TABLE
(select L1, L2, L3, L4, L5, SB
from
(select L1, L2, L3, L4, L5, SB
from TABLE
(select
substr(char(rand(R)), 3, 1) ||
substr(char(rand(R + 1)), 4, 1) L1,
substr(char(rand(R + 2)), 5, 1) ||
substr(char(rand(R + 3)), 6, 1) L2,
substr(char(rand(R + 4)), 7, 1) ||
substr(char(rand(R + 5)), 8, 1) L3,
substr(char(rand(R + 6)), 9, 1) ||
substr(char(rand(R + 7)), 10, 1) L4,
substr(char(rand(R + 8)), 10, 1) ||
substr(char(rand(R + 9)), 11, 1) L5,
substr(char(rand(R + 10)), 12, 1) ||
substr(char(rand(R + 11)), 13, 1) SB
from sysibm.sysdummy1 ) rd1
where
L1 between '01' and '56' and
L2 between '01' and '56' and
L3 between '01' and '56' and
L4 between '01' and '56' and
L5 between '01' and '56' and
SB between '01' and '46'
) rd2
Where
L1 not in (L2, L3, L4, L5) and
L2 not in (L1, L3, L4, L5) and
L3 not in (L1, L2, L4, L5) and
L4 not in (L1, L2, L3, L5) and
L5 not in (L1, L2, L3, L4)
and not exists
(select 1 from past_lot_win_Ch2
where
case when
L1 in (s1, s2, s3, s4, s5) then '1' else ''
end ||
case when
L2 in (s1, s2, s3, s4, s5) then '1' else ''
end ||
case when
L3 in (s1, s2, s3, s4, s5) then '1' else ''
end ||
case when
L4 in (s1, s2, s3, s4, s5) then '1' else ''
end ||
case when
L5 in (s1, s2, s3, s4, s5) then '1' else ''
end ||
case when
SB = SS then '1' else ''
end >= '111'
)
Union All
select '00' L1, '00' L2, '00' L3, '00' L4, '00' L5, '00' SB
from sysibm.sysdummy1 ) rd3 ) rdm
where K <= tickets - 1
)
select L1, L2, L3, L4, L5, SB "MegaBall"
from Lotto_tickets
where Lsw = 'Y'
order by 1
As result we'll get:
Quote:
L1 L2 L3 L4 L5 MegaBall
02 47 22 50 37 31
04 56 01 47 11 18
07 26 28 12 19 30
07 40 26 55 25 42
10 38 56 31 41 34
13 08 53 39 21 05
14 27 34 42 38 42
18 32 48 11 04 33
19 13 22 29 38 45
29 41 17 24 13 33
30 51 28 22 24 13
32 52 54 24 26 06
41 44 32 45 10 02
43 37 23 47 17 22
44 19 40 02 35 33
51 43 39 41 23 40
52 36 55 35 12 05
53 16 02 52 15 37
54 06 25 05 43 23
56 51 02 26 03 11
|
P.S. Added to the basic code of Generator in BOLD
Lenny
|
Last edited by Lenny77; 02-08-10 at 18:16.
|
02-08-10, 23:16
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
Better
For me it looks better then Lenny's statement:
Code:
...........................................
and not exists
(select 1 from past_lot_win_Ch2
where
case when
L1 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L2 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L3 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L4 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L5 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
SB = SS then 1 else 0
end >= 3
)
|
|
02-09-10, 11:23
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Same idea
Quote:
Originally Posted by DB2Plus
For me it looks better then Lenny's statement:
Code:
...........................................
and not exists
(select 1 from past_lot_win_Ch2
where
case when
L1 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L2 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L3 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L4 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
L5 in (s1, s2, s3, s4, s5) then 1 else 0
end +
case when
SB = SS then 1 else 0
end >= 3
)
|
Not bad, but nothing is new. Same idea.
Lenny
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Lottery Numbers Generator (LNG)
