Como mostrar 0 em campos que retornam NULL por não terem dados numa consulta.
04/07/2022
0
Estou com uma consulta SQL e preciso que quando os valores a pagar não tenham valores, ele fique zerado e não NULL, futuramente quero recriar outra ocasião em que os valores de receber e pagar não terão dados e evitar que a linha da consulta suma.
Quando realizo a consulta atualmente ela aparece assim: https://prnt.sc/r_pOoF-Iw8DI
Mas quando realizo o comando "update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 1" para simular que no mês de Janeiro não tenha transações a pagar, ele me retorna NULL: https://prnt.sc/FDr8XRiKht2P
Como eu faria para fazer ele retornar 0 ao invés desse NULL? e futuramente caso os dois campos "Total a Receber" e "Total a Pagar" não tivessem valores, como ficaria para representar? porque quando ambas as linhas não tem valores, ela simplesmente some: https://prnt.sc/Gl66yph4F8PQ
/*
-- CONTAS A RECEBER
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 1
-- CONTAS A PAGAR
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 1
*/
execute block
returns ("Mês" varchar(15), "Total a Receber" numeric(15,2), "Total a Pagar" numeric(15,2), "Saldo" numeric(15,2)) --Retornos que terei na consulta (COLUNAS)
as
declare variable vMesCP integer; --VARIÁVEIS
declare variable vValor numeric(15,2);
declare variable vTipo varchar(5);
declare variable vStatus varchar (5);
declare variable vMesCR integer;
declare variable vTotalCR numeric(15,2);
declare variable vTotalCP numeric(15,2);
begin
vMesCR = 1; --Setando valor inicial igual a 1
for
select extract(month from cr.data_emissao), sum(cr.valor_aberto), 'CR'"TIPO", cr.status
from contas_receber cr
where cr.status = 'A'
group by extract(month from cr.data_emissao), cr.status
union all
select extract(month from cp.data_emissao), sum(cp.valor_aberto), 'CP'"TIPO", cp.status
from contas_pagar cp
where cp.status = 'A'
group by extract(month from cp.data_emissao),cp.status
order by 1
into vMesCP, vValor, vTipo, vStatus
do
begin
if (vMesCP <> vMesCR) then
begin
suspend;
vMesCR = vMesCP;
"Total a Pagar" = 0;
"Total a Receber" = 0;
end
"Total a Receber" = 0; --Necessário para realizar a soma de Janeiro no contas a Pagar, pois inicialmente o valor é NULL
if (vStatus = 'P') then
begin
"Total a Pagar" = 0;
"Total a Receber" = 0;
end
if (vTipo = 'CR') then --Separação das contas de acordo com o tipo
"Total a Receber" = vValor;
else if (vTipo = 'CP') then
"Total a Pagar" = vValor;
if (vMesCR = 1) then --Condicional para separação dos meses
"Mês" = 'Janeiro';
if (vMesCR = 2) then
"Mês" = 'Fevereiro';
if (vMesCR = 3) then
"Mês" = 'Março';
if (vMesCR = 4) then
"Mês" = 'Abril';
if (vMesCR = 5) then
"Mês" = 'Maio';
if (vMesCR = 6) then
"Mês" = 'Junho';
if (vMesCR = 7) then
"Mês" = 'Julho';
if (vMesCR = 8) then
"Mês" = 'Agosto';
if (vMesCR = 9) then
"Mês" = 'Setembro';
if (vMesCR = 10) then
"Mês" = 'Outubro';
if (vMesCR = 11) then
"Mês" = 'Novembro';
if (vMesCR = 12) then
"Mês" = 'Dezembro';
"Saldo" = "Total a Receber" - "Total a Pagar"; --Saldo final
if ("Total a Receber" = 0) then --Condição para realizar a soma de Contas a Pagar, pois estava saindo duplicado
vTotalCP = coalesce(vTotalCP,0) + coalesce("Total a Pagar",0);
else if ("Total a Receber" <> 0) then
vTotalCR = coalesce(vTotalCR,0) + coalesce("Total a Receber",0);
end
begin
suspend;
"Mês" = '-->Totais';
"Total a Receber" = vTotalCR;
"Total a Pagar" = vTotalCP;
"Saldo" = "Total a Receber" - "Total a Pagar";
suspend;
end
end
Renato Akira
Curtir tópico
+ 0
Responder
Post mais votado
05/07/2022
da forma que eu apresentei, quando não houver registros (seja a pagar ou receber) o valor será zero.
Emerson Nascimento
Responder
Mais Posts
04/07/2022
Renato Akira
Consegui avançar no código e deixar 0 nos campos dos valores nas colunas "Total a Receber" e "Total a Pagar". Mas quando ambas as colunas não possuem valores, ele apenas pula. Como posso resolver?
Print do resultado da consulta SQL: https://prnt.sc/CMJP1kgVLV2h
/*
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" NO MÊS DE JANEIRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 10
-- APAGANDO REGISTROS DE "CONTAS_PAGAR" NO MÊS DE FEVEREIRO
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 12
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" E "CONTAS_PAGAR" NO MÊS DE NOVEMBRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 11
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 11
*/
execute block
returns ("Mês" varchar(15), "Total a Receber" numeric(15,2), "Total a Pagar" numeric(15,2), "Saldo" numeric(15,2)) --Retornos que terei na consulta (COLUNAS)
as
declare variable vMesCP integer; --VARIÁVEIS
declare variable vValor numeric(15,2);
declare variable vTipo varchar(5);
declare variable vStatus varchar (5);
declare variable vMesCR integer;
declare variable vSaldo numeric(15,2);
declare variable vValorCR numeric(15,2);
declare variable vValorCP numeric(15,2);
declare variable vTotalCR numeric(15,2);
declare variable vTotalCP numeric(15,2);
begin
vMesCR = 1; --Setando valor inicial igual a 1
for
select extract(month from cp.data_emissao), coalesce(sum(cp.valor_aberto),0), 'CP'"TIPO", cp.status
from contas_pagar cp
where cp.status = 'A'
group by extract(month from cp.data_emissao),cp.status
union all
select extract(month from cr.data_emissao), coalesce(sum (cr.valor_aberto),0), 'CR'"TIPO", cr.status
from contas_receber cr
where cr.status = 'A'
group by extract(month from cr.data_emissao), cr.status
order by 1
into vMesCP, vValor, vTipo, vStatus
do
begin
if (vMesCP <> vMesCR) then
begin
suspend;
vMesCR = vMesCP;
vValorCP = 0;
vValorCR = 0;
end
if (vTipo = 'CR') then --Separação das contas de acordo com o tipo
vValorCR = vValor;
else if (vTipo = 'CP') then
vValorCP = vValor;
if (vMesCR = 1) then --Condicional para separação dos meses
"Mês" = 'Janeiro';
if (vMesCR = 2) then
"Mês" = 'Fevereiro';
if (vMesCR = 3) then
"Mês" = 'Março';
if (vMesCR = 4) then
"Mês" = 'Abril';
if (vMesCR = 5) then
"Mês" = 'Maio';
if (vMesCR = 6) then
"Mês" = 'Junho';
if (vMesCR = 7) then
"Mês" = 'Julho';
if (vMesCR = 8) then
"Mês" = 'Agosto';
if (vMesCR = 9) then
"Mês" = 'Setembro';
if (vMesCR = 10) then
"Mês" = 'Outubro';
if (vMesCR = 11) then
"Mês" = 'Novembro';
if (vMesCR = 12) then
"Mês" = 'Dezembro';
vSaldo = coalesce(vValorCR,0) - coalesce(vValorCP,0); --Saldo final
"Total a Receber" = vValorCR;
"Total a Pagar" = vValorCP;
"Saldo" = vSaldo;
if (vValorCR = 0) then --Condição para realizar a soma de Contas a Pagar, pois estava saindo duplicado
vTotalCP = coalesce(vTotalCP,0) + coalesce("Total a Pagar",0);
if (vValorCR <> 0) then
vTotalCR = coalesce(vTotalCR,0) + coalesce("Total a Receber",0);
end
begin
suspend;
"Mês" = '-->Totais';
"Total a Receber" = coalesce(vTotalCR,0);
"Total a Pagar" = coalesce(vTotalCP,0);
vSaldo = "Total a Receber" - "Total a Pagar";
"Saldo" = vSaldo;
suspend;
end
end
Responder
04/07/2022
Emerson Nascimento
eu postei o código abaixo na outra pergunta que você fez. não deu certo?
execute block
returns ("Mês" varchar(15), r_valorCR numeric(15,2), r_valorCP numeric(15,2), r_saldo numeric (15,2))
as
declare numeromes int = 1;
declare total_cr numeric(15,2) = 0;
declare total_cp numeric(15,2) = 0;
declare total_sld numeric(15,2) = 0;
begin
while (numeromes <= 12) do
begin
"Mês" = case numeromes
when 01 then 'Janeiro'
when 02 then 'Fevereiro'
when 03 then 'Março'
when 04 then 'Abril'
when 05 then 'Maio'
when 06 then 'Junho'
when 07 then 'Julho'
when 08 then 'Agosto'
when 09 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
end;
-- estude a possibilidade de criar um campo pra guardar o mes
-- e criar um indice por esse campo.
-- assim a condicao seria "cr.status = 'A' and mes = :numeromes"
-- desta forma de pesquisa seria mais rapida por que o indice
-- seria usado na pesquisa (com o extract() nao ha uso de indice)
select coalesce(sum(cr.valor_aberto),0)
from contas_receber cr
where cr.status = 'A' and extract(month from cr.data_emissao) = :numeromes
into r_valorCR;
-- estude a possibilidade de criar um campo pra guardar o mes
-- e criar um indice por esse campo.
-- assim a condicao seria "cp.status = 'A' and mes = :numeromes"
-- desta forma de pesquisa seria muito mais rapida por que o indice
-- seria usado na pesquisa (com o extract() nao ha uso de indice)
select colaesce(sum(cp.valor_aberto),0)
from contas_pagar cp
where cp.status = 'A' and extract(month from cp.data_emissao) = :numeromes
into r_valorCP;
if (r_valorCR is null) then
r_valorCR = 0;
if (r_valorCP is null) then
r_valorCP = 0;
r_saldo := (r_valorCR - r_valorCP);
-- totalizadores
total_cr = total_cr + r_valorCR;
total_cp = total_cp + r_valorCP;
total_sld = total_sld + r_saldo;
numeromes = numeromes + 1;
suspend;
end
-- apresenta os totais
"Mês" = 'Totais';
r_valorCR = total_cr;
r_valorCP = total_cp;
r_saldo = total_sld;
suspend;
end
Responder
05/07/2022
Renato Akira
Boa noite, eu vi que você disse sobre colocar os valores do mês numa variável e eu fiz isso no código acima que digitei. Não teria como usar essa variável para realizar o while ? E caso eu realizasse os dois updates seguintes:
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" E "CONTAS_PAGAR" NO MÊS DE NOVEMBRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 11
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 11
A consulta SQL iria conseguir me retornar o mês de Novembro ? ou ele iria pular de Outubro para Dezembro?
Responder
05/07/2022
Renato Akira
Entendi, mas como minha atividade possui algumas restrições eu vou precisar deixar a estrutura do meu Union ALL como informei antes. Mas muito obrigado, qualquer coisa venho tirar minha dúvida
Responder
05/07/2022
Renato Akira
vou ter que refazer meu código utilizando o union all e mostrando a extract da data no select pra me.retornar o mês em número inteiro pq é uma das restrições da atividade, aí eu tento replicar esse while para ver se funciona . Obrigado, qualquer coisa eu retorno para outras dúvidas
Responder
05/07/2022
Emerson Nascimento
na instrução que eu te passei, se quer apresentar o mês em inteiros, basta usar o conteúdo da variável numeromes.
Responder
05/07/2022
Renato Akira
Emerson estou tentando utilizar usando a estrutura do Union All seguinte, mas ta dando erro. Sabe me dizer onde errei ?
/*
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" NO MÊS DE JANEIRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 1
-- APAGANDO REGISTROS DE "CONTAS_PAGAR" NO MÊS DE FEVEREIRO
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 2
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" E "CONTAS_PAGAR" NO MÊS DE NOVEMBRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 10
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 10
*/
execute block
returns ("Mês" varchar(15), "Total a Receber" numeric(15,2), "Total a Pagar" numeric(15,2), "Saldo" numeric(15,2)) --Retornos que terei na consulta (COLUNAS)
as
declare variable vMes integer; --VARIÁVEIS
declare variable vValor numeric(15,2);
declare variable vTipo varchar(5);
declare variable vSaldo numeric(15,2);
declare variable vValorCR numeric(15,2);
declare variable vValorCP numeric(15,2);
declare variable vTotalCR numeric(15,2);
declare variable vTotalCP numeric(15,2);
begin
for
select coalesce(extract(month from cp.data_emissao),0), coalesce(sum(cp.valor_aberto),0), 'CP'"TIPO"
from contas_pagar cp
where cp.status = 'A'
group by extract(month from cp.data_emissao)
union all
select coalesce(extract(month from cr.data_emissao),0), coalesce(sum (cr.valor_aberto),0), 'CR'"TIPO"
from contas_receber cr
where cr.status = 'A'
group by extract(month from cr.data_emissao)
order by 1
into vMes, vValor, vTipo
do
begin
while (vMes <= 12) do
begin
"Mês" = case vMes
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
end;
if (vTipo = 'CR') then --Separação das contas de acordo com o tipo
vValorCR = coalesce(vValor,0);
else if (vTipo = 'CP') then
vValorCP = coalesce(vValor,0);
vSaldo = coalesce(vValorCR,0) - coalesce(vValorCP,0); --Saldo final
"Total a Receber" = coalesce(vValorCR,0);
"Total a Pagar" = coalesce(vValorCP,0);
"Saldo" = coalesce(vSaldo,0);
if (vValorCR = 0) then --Condição para realizar a soma de Contas a Pagar, pois estava saindo duplicado
vTotalCP = coalesce(vTotalCP,0) + coalesce("Total a Pagar",0);
if (vValorCR <> 0) then
vTotalCR = coalesce(vTotalCR,0) + coalesce("Total a Receber",0);
end
begin
suspend;
"Mês" = '-->Totais';
"Total a Receber" = coalesce(vTotalCR,0);
"Total a Pagar" = coalesce(vTotalCP,0);
vSaldo = "Total a Receber" - "Total a Pagar";
"Saldo" = coalesce(vSaldo,0);
suspend;
end
end
Responder
05/07/2022
Renato Akira
Mudei o lugar do While para o começo do código e ele rodou, mas me retornou apenas uma linha com tudo NULL
/*
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" NO MÊS DE JANEIRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 1
-- APAGANDO REGISTROS DE "CONTAS_PAGAR" NO MÊS DE FEVEREIRO
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 2
-- APAGANDO REGISTROS DE "CONTAS_RECEBER" E "CONTAS_PAGAR" NO MÊS DE NOVEMBRO
update contas_receber cr set cr.valor_aberto = 0, cr.status = 'P' where extract(month from cr.data_emissao) = 10
update contas_pagar cp set cp.valor_aberto = 0, cp.status = 'P' where extract(month from cp.data_emissao) = 10
*/
execute block
returns ("Mês" varchar(15), "Total a Receber" numeric(15,2), "Total a Pagar" numeric(15,2), "Saldo" numeric(15,2)) --Retornos que terei na consulta (COLUNAS)
as
declare variable vMes integer; --VARIÁVEIS
declare variable vValor numeric(15,2);
declare variable vTipo varchar(5);
declare variable vSaldo numeric(15,2);
declare variable vValorCR numeric(15,2);
declare variable vValorCP numeric(15,2);
declare variable vTotalCR numeric(15,2);
declare variable vTotalCP numeric(15,2);
begin
while (vMes <= 12) do
begin
"Mês" = case vMes
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
end;
select coalesce(extract(month from cp.data_emissao),0)vMes, coalesce(sum(cp.valor_aberto),0), 'CP'"TIPO"
from contas_pagar cp
where cp.status = 'A'
group by extract(month from cp.data_emissao)
union all
select coalesce(extract(month from cr.data_emissao),0)vMes, coalesce(sum (cr.valor_aberto),0), 'CR'"TIPO"
from contas_receber cr
where cr.status = 'A'
group by extract(month from cr.data_emissao)
order by 1
into vMes, vValorCR, vTipo;
if (vValorCR is null) then
vValorCR = 0;
if (vValorCP is null) then
vValorCP = 0;
if (vTipo = 'CR') then --Separação das contas de acordo com o tipo
vValorCR = coalesce(vValor,0);
else if (vTipo = 'CP') then
vValorCP = coalesce(vValor,0);
vSaldo = coalesce(vValorCR,0) - coalesce(vValorCP,0); --Saldo final
"Total a Receber" = coalesce(vValorCR,0);
"Total a Pagar" = coalesce(vValorCP,0);
"Saldo" = coalesce(vSaldo,0);
if (vValorCR = 0) then --Condição para realizar a soma de Contas a Pagar, pois estava saindo duplicado
vTotalCP = coalesce(vTotalCP,0) + coalesce("Total a Pagar",0);
if (vValorCR <> 0) then
vTotalCR = coalesce(vTotalCR,0) + coalesce("Total a Receber",0);
vMes = vMes + 1;
suspend;
end
begin
suspend;
"Mês" = '-->Totais';
"Total a Receber" = coalesce(vTotalCR,0);
"Total a Pagar" = coalesce(vTotalCP,0);
vSaldo = "Total a Receber" - "Total a Pagar";
"Saldo" = coalesce(vSaldo,0);
suspend;
end
end
Print do resultado: https://prnt.sc/aDzyiSaeB1T_
Responder
05/07/2022
Renato Akira
Avancei um pouco mais no código, agora ele está assim:
execute block
returns ("Mês" varchar (15), "Total a Receber" numeric (15,2), "Total a Pagar" numeric (15,2), "Saldo" numeric (15,2))
as
declare variable vMesAnt integer;
declare variable vMesAtu integer;
declare variable tipo varchar(15);
declare variable valor numeric(15,2);
declare variable totalCR numeric (15,2);
declare variable totalCP numeric (15,2);
begin
vMesAnt = 1;
for
select extract (month from cr.data_emissao), sum(cr.valor_aberto), 'CR'"TIPO"
from contas_receber cr
where cr.status = 'A'
group by extract (month from cr.data_emissao)
union all
select extract (month from cp.data_emissao), sum(cp.valor_aberto), 'CP'"TIPO"
from contas_pagar cp
where cp.status = 'A'
group by extract(month from cp.data_emissao)
order by 1
into vMesAtu, valor, tipo
do
begin
if (vMesAnt <> vMesAtu) then
begin
suspend;
vMesAnt = vMesAtu;
"Total a Receber" = 0;
"Total a Pagar" = 0;
end
"Total a Receber" = 0;
if (tipo = 'CR') then
"Total a Receber" = valor;
else if (tipo = 'CP') then
"Total a Pagar" = valor;
while (vMesAtu <= 12) do
begin
"Mês" = case vMesAtu
when 01 then 'Janeiro'
when 02 then 'Fevereiro'
when 03 then 'Março'
when 04 then 'Abril'
when 05 then 'Maio'
when 06 then 'Junho'
when 07 then 'Julho'
when 08 then 'Agosto'
when 09 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
end;
vMesAtu = vMesAtu + 1;
end
if ("Total a Receber" <> 0) then
"Saldo" = "Total a Receber" - "Total a Pagar";
totalCR = coalesce(totalCR, 0) + "Total a Receber";
if ("Total a Receber" = 0) then
totalCP = coalesce(totalCP,0) + "Total a Pagar";
end
suspend;
"Mês" = '-->Totais';
"Total a Receber" = totalCR;
"Total a Pagar" = totalCP;
"Saldo" = "Total a Receber" - "Total a Pagar";
suspend;
end
Pena que na consulta, ele ta me retornando apenas "Dezembro" nos meses agora.
Print: https://prnt.sc/nK48Hyvhy9qc
Responder
05/07/2022
Emerson Nascimento
nunca vai funcionar com o "for select" sendo a base da consulta, porque vai apresentar somente os meses que possuem algum valor.
porque o código como te passei não serve?
porque o código como te passei não serve?
Responder
05/07/2022
Ganso Ltda
Por que um dos requerimentos para a atividade é o filtro ter apenas where cp.status = 'A'
Aqui um print dos requerimentos da atividade: https://prnt.sc/wijhWRjKoGrT
Responder
05/07/2022
Renato Akira
Por que um dos requerimentos para a atividade é o filtro ter apenas where cp.status = 'A'
Aqui um print dos requerimentos da atividade: https://prnt.sc/wijhWRjKoGrT
Responder
05/07/2022
Emerson Nascimento
Por que um dos requerimentos para a atividade é o filtro ter apenas where cp.status = 'A'
O apenas é por tua conta, né? Porque não há esta indicação no escopo da solicitação.
Diz que você deve apresentar o que está em aberto, e sequer diz como deve ser o filtro; não diz o que você pode - ou não pode - usar no filtro, bem como não diz que você deve usar joins. A única exigência é utilizar EXECUTE BLOCK.
Daquela forma que te passei o resultado é apresentado exatamente como você precisa, exceto pela nomenclatura dos campos, que requer o ajuste necessário.
Uma dica: Nesta profissão você precisa apresentar o resultado solicitado com a melhor performance possível, não importando quais "comandos" tenham sido executados.
Eu fico por aqui.
Responder
Clique aqui para fazer login e interagir na Comunidade :)