Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Unanswered: 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....

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Did you win any money?

    varchar doesn't work here, got the same sql0440n error:
    http://www.dbforums.com/db2/1650696-...r-funtion.html
    Last edited by db2girl; 02-03-10 at 18:29.

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

    Red face 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

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Lenny, I'm now getting:
    SQL0204N "R" is an undefined name. SQLSTATE=42704

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

    Question Question

    Quote Originally Posted by db2girl View Post
    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

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

    Exclamation 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:

    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 12:19.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)       ||  
    .....

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

    Wink 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'
    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 14:12.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Hi, tomkuma !

    Can you explain me how is Japanes Lottery works ?

    Lenny

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know.

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

    To be, or not ?

    Quote Originally Posted by tonkuma View Post
    I don't know.
    You don't want become a millioner ?!

    I can't read: http://www.takarakuji.mizuhobank.co.jp

    Lenny

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

    Post 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

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

    Exclamation 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:

    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 19:16.

  14. #14
    Join Date
    Jul 2009
    Posts
    150

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

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

    Wink Same idea

    Quote Originally Posted by DB2Plus View Post
    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

Posting Permissions

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