How to transform a zero null return.

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
joaoluizbt
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 37
Joined: Thu, 05 May 2011 3:53 pm
Location: Rio de Janeiro

Hello,

I try to update a field with its own value added to a value returned from a query. The problem is that when the value returned is nullo, that is, there was no return, at the time of adding, the end result is null as well.
How do I transform this null return in zero?

follows the code:

Select all

 
update TABELA_GERAL ect 
set VALOR_PAGO = VALOR_PAGO +(select NVL(soma_paga ,0) 
                              from(select cod, sum(NVL(valor,0)) as soma_paga  
                                    from tabela1 
                                    where dt_criacao between p_data_inicio And p_data_fim 
                                    and (esquema = 'TEXTO1' OR esquema = 'TEXTO') 
                                    group by cod_dealer)soma 
                              where soma.cod = ect.pdv); 
commit; 
Thank you.
ishii
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 82
Joined: Tue, 28 Dec 2010 7:41 pm
Location: São Paulo - SP

Hello,

The NVL already does this but apparently missed in a place:

Select all

 
update TABELA_GERAL ect  
set VALOR_PAGO = nvl(VALOR_PAGO,0) +(select NVL(soma_paga ,0)  
from(select cod, sum(NVL(valor,0)) as soma_paga  
from tabela1  
where dt_criacao between p_data_inicio And p_data_fim  
and (esquema = 'TEXTO1' OR esquema = 'TEXTO')  
group by cod_dealer)soma  
where soma.cod = ect.pdv);  
commit;  
[] S ISHII
marcus.kobel
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 45
Joined: Thu, 12 May 2011 4:54 pm
Location: Porto Alegre - RS

It is with NVL yes, you just have to see that if your subquery brings results.
If she does not bring, I think NVL will not work.
Maybe you have to encapsulate any quoting result with an NVL to see if it works.
TESTA AI.
joaoluizbt
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 37
Joined: Thu, 05 May 2011 3:53 pm
Location: Rio de Janeiro

because it is ... it did not work

I will try to create a table to save the result of the query, and then add only those who have the same code ... is my last hope ...
gfkauer
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 389
Joined: Tue, 27 Jul 2010 1:34 pm
Location: Sapiranga - RS
Contact:
Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

The question is, you are not looking for a null and yes a not found !!

by what you understand, if your cursor does not find records (not_found) you that he returns 0 (zero).

If this you will have to open your query. Run an update based on a given that does not exist is complicated. I would change its structure to something like this:

Select all

Declare 
  Cursor c_dados_base_upd Is 
    Select cod, 
           Sum(nvl(valor, 0)) As soma_paga 
      From tabela1 
     Where dt_criacao Between p_data_inicio And p_data_fim 
       And (esquema = 'TEXTO1' Or esquema = 'TEXTO') 
     Group By cod_dealer; 
  -- 
  v_reg    c_dados_base_upd%Rowtype; 
  -- 
Begin 
  Open c_dados_base_upd; 
  Loop 
    Fetch c_dados_base_upd Into v_reg; 
    Exit When c_dados_base_upd%Notfound; 
    -- 
    Update tabela_geral etc 
       Set etc.valor_pago = etc.valor_pago + v_reg.soma_paga 
     Where etc.pdv = v_reg.cod; 
    -- 
  End Loop; 
  Close c_dados_base_upd; 
  -- 
  Commit; 
  -- 
End;
I prefer to make more extensive codes, sometimes much more extensive, and makes them clearer to anyone which will do the maintenance.

For your first SELECT, and considering that you are in a block and pl / sql, I would solve thus.

I hope it helps.
joaoluizbt
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 37
Joined: Thu, 05 May 2011 3:53 pm
Location: Rio de Janeiro

I'm using the decode to try to replace by zero.

type

DECODE (Express, NULL, 0,
Expression)

See if tomorrow worked ...
Tomara ...

valeu aí.
joaoluizbt
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 37
Joined: Thu, 05 May 2011 3:53 pm
Location: Rio de Janeiro

It worked with Decode, guys.

valeu for help.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest