Get next number drawn

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 05 Aug 2009 8:45 am

Good morning, I have a table where it has some random records that are sold to customers, which gives right to a lottery next month.
It turns out that some numbers are not sold, so I need to get the next number sold.

Ex.
the number drawn was 123456, however he was not sold then have to add 1 number (123456 + 1 = 123457) and check whether it was sold or not, neither case is sold, do the same thing to find a winner.


Can anyone give me an idea of how to do this?
facc
Location: Cerquilho / SP

Poston Wed, 05 Aug 2009 8:49 am

How do you know if it was sold or not? some flag? If Yes! does this filter in its query and get the numbers sold ... If not in the same table or not can make relationship, makes 2 cursors, one for opening the number and another to check if it was sold. throw it in a for loop and hug!
RodrigoValentim
Location: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Poston Wed, 05 Aug 2009 9:02 am

Yes, there is a flag. But this giveaway is for the lottery and not an internal system, then the number drawn may not be sold, so I have to take the number drawn and add a unit and do this until you find a sold.
facc
Location: Cerquilho / SP

Poston Wed, 05 Aug 2009 10:52 am

Doing a bixo game is? lol well, you can do this with a loop until you find the number that has been drawn, then, if it was not sold, you search the number drawn +1 as you wish and so on going until I find one that is sold ...
RodrigoValentim
Location: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Poston Thu, 06 Aug 2009 11:07 am

If I'vê got this right:
Code: Select all
SQL> set serveroutput on
SQL> declare
  2    cursor t_sorteio (p_num_sorteado in number) is
  3    with t as (
  4    select 1 cod_venda, 5 num, 'S' flag_venda from dual
  5    union all select 2, 12, 'N' from dual
  6    union all select 3, 15, 'S' from dual
  7    union all select 4, 20, 'S' from dual)
  8    select min(t.num) from t
  9    where t.num >= p_num_sorteado
10    and t.flag_venda = 'S';
11    v_num_sorteado number := 10;
12    v_cod_venda_sort number;
13  begin
14    open t_sorteio (p_num_sorteado => v_num_sorteado);
15    fetch t_sorteio
16      into v_cod_venda_sort;
17    dbms_output.put_line('Cod Venda Sorteada: ' || v_cod_venda_sort);
18    dbms_output.put_line(' - Número da sorte: ' || v_num_sorteado);
19    close t_sorteio;
20  end;
21  /
Cod Venda Sorteada: 15
- Número da sorte: 10

Procedimento PL/SQL concluído com sucesso.

SQL>
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Poston Thu, 06 Aug 2009 11:16 am

Correcting my previous post, I posted a little without thinking.

Code: Select all
SQL> set serveroutput on
SQL> declare
  2    cursor t_sorteio (p_num_sorteado in number) is
  3    with t as (
  4    select 1 cod_venda, 5 num, 'S' flag_venda from dual
  5    union all select 2, 12, 'N' from dual
  6    union all select 3, 15, 'S' from dual
  7    union all select 4, 20, 'S' from dual)
  8    select t.cod_venda, t.num
  9      from t
10     where t.num = (select min(t.num) from t
11                     where t.num >= p_num_sorteado
12                       and t.flag_venda = 'S');
13    v_num_sorteado number := 10;
14    v_cod_venda_venc number;
15    v_num_sorte_venc number;
16  begin
17    open t_sorteio (p_num_sorteado => v_num_sorteado);
18    fetch t_sorteio
19      into v_cod_venda_venc, v_num_sorte_venc;
20    dbms_output.put_line('Número Sorte Vencedor: ' || v_num_sorte_venc);
21    dbms_output.put_line('Código da Venda Vencedor: ' || v_cod_venda_venc);
22    dbms_output.put_line(' - Número da sorte sorteado: ' || v_num_sorteado);
23    close t_sorteio;
24  end;
25  /
Número Sorte Vencedor: 15
Código da Venda Vencedor: 3
- Número da sorte sorteado: 10

Procedimento PL/SQL concluído com sucesso.

SQL>

just have to have a unique key in the column for the lucky number, if you have repeated there will be 2 winners and will take exception to the select into. Would have to make a loop.
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Poston Thu, 06 Aug 2009 3:43 pm

Done that way, in my tests it worked cool.

Code: Select all
SELECT NROSORTE, SERIESORTEIO, LOJA, CODCLIENTE,
            TO_CHAR(DATANRSORTE, 'DD/MM/YYYY') ,
            CPF,
            NOMCLI,
            TELCLI
FROM CYBELAR_NROSORTE"
  WHERE NROSORTE IN (SELECT MIN(NROSORTE)
                                      FROM CYBELAR_NROSORTE
                                        WHERE NROSORTE >= :Numero
                                            AND FLGUSO = 1
                                            AND EXTRACT(MONTH FROM MES_ANO) = :mês
                                            AND EXTRACT(YEAR FROM MES_ANO) = :Ano
                                    GROUP BY MES_ANO)
    AND EXTRACT(MONTH FROM MES_ANO) = :mês
    AND EXTRACT(YEAR FROM MES_ANO) = :Ano
    AND FLGUSO = 1
facc
Location: Cerquilho / SP


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 4 guests