Menu de navegação

[MARCIO_LOKParticipante]

Entao, minha internet estava um lixo, escrevi correndo…

Mais acho que vc conseguiu entender nao é?

É necessário que o valor total na ultima coluna continue aparecendo… porque ele é a soma de determinada conta no periodo… tipo….
em janeiro, fevereiro… de determinada conta… qual é o total em valor.

Da forma que vc fez esta correto, ele pega conta por conta, e soma no final, ta perfeito….

DA maneira que vc apresentou, nos seus exemplos, sobre o saldo anterior, é aquilo mesmo…. pegar o valor de dezembro… somar com saldo anterior….
depois feveiro-janeiro-saldo anteiror….
e assim por diante…
os meses que nao tem lancamento (debito e credito) o valor terá que aparecer zerado…. (como aparece atualmente)…
exemplo (imaginamos que eu tenha lancamento(debito e credito) até maio….

codigo jan fev mar abr mai jun jul ago………total….
0002 3 5 6 7 8 0 0 0
2987 7 9 3 9 7 0 0 0
Correto?

Muito Obrigado

[MARCIO_LOKParticipante]

Pessoal, Alguma luz?

To tentando desenvolver alguma coisa, mais ta complicado pra mim,

brigadao

[fsitjaParticipante]

Hoje tá complicado aqui, vou pensar em algo no fim-de-semana aí respondo aqui.

8)

[MARCIO_LOKParticipante]

Sem problemas, te agradeco muito, abracos

[fsitjaParticipante]

Opa, testa o seguinte e vê se é mais ou menos isso:


SQL> create table cconta (codconta varchar2(11) primary key,
2 descricao varchar2(40));

Table created
SQL> create table saldo (codconta varchar2(11),
2 data_saldo date,
3 val_saldo number(10,2));

Table created
SQL> create table clanca (conta varchar2(11) references cconta(codconta),
2 data_lanc date,
3 credito number(10, 2),
4 debito number(10, 2),
5 valor number(10, 2));

Table created
SQL> begin
2 -- inserts de contas
3 insert into cconta (codconta, descricao) values (1, '1.0.0');
4 insert into cconta (codconta, descricao) values (2, '2.0.0');
5 insert into cconta (codconta, descricao) values (11, '1.1.0');
6 insert into cconta (codconta, descricao) values (12, '1.2.0');
7 insert into cconta (codconta, descricao) values (111, '1.1.1');
8 insert into cconta (codconta, descricao) values (112, '1.1.2');
9 insert into cconta (codconta, descricao) values (21, '2.1.0');
10 insert into cconta (codconta, descricao) values (211, '2.1.1');
11 -- inserts de saldo
12 insert into saldo (codconta, data_saldo, val_saldo)
13 values (111, to_date('31/12/2009', 'DD/MM/YYYY'), 5000);
14 insert into saldo (codconta, data_saldo, val_saldo)
15 values (12, to_date('31/12/2009', 'DD/MM/YYYY'), 2000);
16 insert into saldo (codconta, data_saldo, val_saldo)
17 values (2, to_date('31/12/2009', 'DD/MM/YYYY'), 1000);
18 -- inserts de lancamentos
19 insert into clanca (conta, data_lanc, credito, debito, valor)
20 values (11, to_date('05/01/2010', 'DD/MM/YYYY'), 1, null, 20000);
21 insert into clanca (conta, data_lanc, credito, debito, valor)
22 values (11, to_date('08/01/2010', 'DD/MM/YYYY'), null, 1, 50000);
23 insert into clanca (conta, data_lanc, credito, debito, valor)
24 values (11, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 80000);
25 insert into clanca (conta, data_lanc, credito, debito, valor)
26 values (11, to_date('02/02/2010', 'DD/MM/YYYY'), null, 1, 130000);
27 insert into clanca (conta, data_lanc, credito, debito, valor)
28 values (11, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 40000);
29 insert into clanca (conta, data_lanc, credito, debito, valor)
30 values (111, to_date('10/02/2010', 'DD/MM/YYYY'), 1, null, 20000);
31 insert into clanca (conta, data_lanc, credito, debito, valor)
32 values (112, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 10000);
33 insert into clanca (conta, data_lanc, credito, debito, valor)
34 values (12, to_date('25/01/2010', 'DD/MM/YYYY'), 1, null, 3000);
35 insert into clanca (conta, data_lanc, credito, debito, valor)
36 values (12, to_date('01/02/2010', 'DD/MM/YYYY'), null, 1, 1000);
37 insert into clanca (conta, data_lanc, credito, debito, valor)
38 values (12, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 5000);
39 insert into clanca (conta, data_lanc, credito, debito, valor)
40 values (12, to_date('01/03/2010', 'DD/MM/YYYY'), null, 1, 7000);
41 insert into clanca (conta, data_lanc, credito, debito, valor)
42 values (12, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 5000);
43 insert into clanca (conta, data_lanc, credito, debito, valor)
44 values (2, to_date('20/01/2010', 'DD/MM/YYYY'), 1, null, 100);
45 insert into clanca (conta, data_lanc, credito, debito, valor)
46 values (21, to_date('20/01/2010', 'DD/MM/YYYY'), 1, null, 200);
47 insert into clanca (conta, data_lanc, credito, debito, valor)
48 values (211, to_date('20/01/2010', 'DD/MM/YYYY'), 1, null, 300);
49 commit;
50 end;
51 /

PL/SQL procedure successfully completed

SQL> with mov_mensal as (
2 select codconta,
3 nivel1,
4 nivel2,
5 nivel3,
6 ano,
7 nvl(sum(case when mes = '12' and ano = (2010 - 1) then mov_liq_mes end), 0) saldo_ant,
8 nvl(sum(case when mes <= '01' then mov_liq_mes end), 0) jan,
9 nvl(sum(case when mes <= '02' then mov_liq_mes end), 0) fev,
10 nvl(sum(case when mes <= '03' then mov_liq_mes end), 0) mar,
11 nvl(sum(case when mes


Só tem que ver como que está armazenado o saldo na sua tabela de saldo. Eu coloquei uma coluna “data_saldo” lá para guardar a data do final do ano de 2009, com a data de 31/12/2009. A solução foi feita pensando nisso.

Talvez dependendo de como for sua tabela, tenha que mexer no CASE para a coluna de SALDO_ANT e algumas outras coisinhas.

[MARCIO_LOKParticipante]

Estou tendo algums dificuldades para adaptar…. ao meu codigo…

Minha Tabela saldo, só tem dois campos…

codconta e valor

essa tabela sempre guarda o valor de dezembro do ultimo mes do ano passado, ou seja, dezembro de 2009…. por isso q nao tem o campo data….. ao tirar esse campo esta dando alguns erros…

[MARCIO_LOKParticipante]

To com dificuldade de entender esse all union….

ta dando um erro…. ORA-000600

[CassinhaParticipante]

😆 Coloca todo o codigo, o atual, o ultimo que voce está mexendo, vai que eu consiga entender, posso ajudar você a tirar o saldo anterior dos valores, nao prometo, mais posso tentar.

[b] 8)
Cassia[/b]

[MARCIO_LOKParticipante]

Obrigado.

O fsitja fez um script perfeito, rapido e eficaz, só ta faltando subtrair o valor que encontra na tabela saldo (só tem dois campos…. codconta e valor) esses valores sao referentes a dezembro de 2009….

Então é só subtrair os valores conforme cada conta…

A tabela dele é um pouco diferente que a minha, alguns campos, o problema é que nao to tendo competencia, pra aproveitar o ultimo codigo dele…. veja o que vc pode me ajudar, muito obrigado.

segue o codigo…

with mov_mensal as (
SELECT codconta,
nivel1,
nivel2,
nivel3,
nivel4,
nivel5,
nivel6,
nivel7,
nivel8,
nivel9,
nivel10,
nivel11,
nivel12,
nivel13,
nivel14,
nivel15,
nivel16,
nivel17,
nivel18,
nivel19,
nivel20,
ano,
sum(case when mes = '01' then mov_liq_mes end) jan,
sum(case when mes = '02' then mov_liq_mes end) fev,
sum(case when mes = '03' then mov_liq_mes end) mar,
sum(case when mes = '04' then mov_liq_mes end) abr,
sum(case when mes = '05' then mov_liq_mes end) mai,
sum(case when mes = '06' then mov_liq_mes end) jun,
sum(case when mes = '07' then mov_liq_mes end) jul,
sum(case when mes = '08' then mov_liq_mes end) ago,
sum(case when mes = '09' then mov_liq_mes end) sete,
sum(case when mes = '10' then mov_liq_mes end) out,
sum(case when mes = '11' then mov_liq_mes end) nov,
sum(case when mes = '12' then mov_liq_mes end) dez,
sum(mov_liq_mes) tot_ano

from (select c.codconta,
             substr(c.codconta, 1, 1) nivel1,
             substr(c.codconta, 1, 2) nivel2,
             substr(c.codconta, 1, 3) nivel3,
             substr(c.codconta, 1, 4) nivel4,
             substr(c.codconta, 1, 5) nivel5,
             substr(c.codconta, 1, 6) nivel6,
             substr(c.codconta, 1, 7) nivel7,
             substr(c.codconta, 1, 8) nivel8,
             substr(c.codconta, 1, 9) nivel9,
             substr(c.codconta, 1,10) nivel10,
             substr(c.codconta, 1,11) nivel11,
             substr(c.codconta, 1,12) nivel12,
             substr(c.codconta, 1,13) nivel13,
             substr(c.codconta, 1,14) nivel14,
             substr(c.codconta, 1,15) nivel15,
             substr(c.codconta, 1,16) nivel16,
             substr(c.codconta, 1,17) nivel17,
             substr(c.codconta, 1,18) nivel18,
             substr(c.codconta, 1,19) nivel19,
             substr(c.codconta, 1,20) nivel20,
             to_char(data, 'YYYY') ano,
             to_char(data, 'MM') mes,
             sum(case when debito  is not null then -valor
                      when credito is not null then valor end) mov_liq_mes
        from (select rpad(codconta, 20, '0') codconta, descricao from cconta c  WHERE SubStr(codconta,1,1)='1') c
        left join (select rpad(c.codconta,20, '0') codconta, l.*
                     FROM cLanca L join cConta C
                     ON    L.CodColigada  = C.CodColigada
                     AND   (L.Credito     = C.CodConta
                     OR    L.Debito       = C.CodConta)

                    where to_char(l.data, 'YYYY') = '2010') l on l.codconta = c.codconta
       group by c.codconta, to_char(data, 'MM'), to_char(data, 'YYYY'))
group by codconta, nivel1, nivel2, nivel3, nivel4, nivel5, nivel6, nivel7, nivel8, nivel9, nivel10, nivel11, nivel12, nivel13, nivel14, nivel15, nivel16, nivel17, nivel18, nivel19, nivel20, ano)
 select t.codconta, c.descricao,t.jan,t.fev,t.mar,t.abr,t.mai,t.jun,t.jul,t.ago,t.sete,t.out,t.nov,t.dez,t.tot_ano,t.media
 from (select DISTINCT coalesce(nivel1, nivel2, nivel3, nivel4 ,nivel5, nivel6, nivel7, nivel8, nivel9, nivel10, nivel11, nivel12, nivel13, nivel14, nivel15, nivel16, nivel17, nivel18, nivel19, nivel20) codconta,
              Nvl(sum(jan),0) jan,
              Nvl(sum(fev),0) fev,
              Nvl(sum(mar),0) mar,
              Nvl(sum(abr),0) abr,
              Nvl(sum(mai),0) mai,
              Nvl(sum(jun),0) jun,
              Nvl(sum(jul),0) jul,
              Nvl(sum(ago),0) ago,
              Nvl(sum(sete),0) sete,
              Nvl(sum(out),0) out,
              Nvl(sum(nov),0) nov,
              Nvl(sum(dez),0) dez,
              nvl(sum(tot_ano),0) tot_ano,
              Nvl(Round(sum(tot_ano),2),0)/12 media
         from mov_mensal
         WHERE  ( jan !=0 OR fev !=0 OR mar !=0 OR abr !=0 OR mai !=0 OR jun !=0 OR jul !=0 OR ago !=0 OR sete !=0 OR out !=0 OR nov !=0 OR dez !=0)
        group by grouping sets ((nivel1), (nivel2), (nivel3), (nivel4), (nivel5), (nivel6), (nivel7), (nivel8), (nivel9), (nivel10), (nivel11), (nivel12), (nivel13), (nivel14), (nivel15), (nivel16), (nivel17), (nivel18), (nivel19), (nivel20))
       having (substr(nivel1, length(nivel1), 1) != '0')
           or (substr(nivel2, length(nivel2), 1) != '0')
           or (substr(nivel3, length(nivel3), 1) != '0')
           or (substr(nivel4, length(nivel4), 1) != '0')
           or (substr(nivel5, length(nivel5), 1) != '0')
           or (substr(nivel6, length(nivel6), 1) != '0')
           or (substr(nivel7, length(nivel7), 1) != '0')
           or (substr(nivel8, length(nivel8), 1) != '0')
           or (substr(nivel9, length(nivel9), 1) != '0')
           or (substr(nivel10, length(nivel10), 1) != '0')
           or (substr(nivel11, length(nivel11), 1) != '0')
           or (substr(nivel12, length(nivel12), 1) != '0')
           or (substr(nivel13, length(nivel13), 1) != '0')
           or (substr(nivel14, length(nivel14), 1) != '0')
           or (substr(nivel15, length(nivel15), 1) != '0')
           or (substr(nivel16, length(nivel16), 1) != '0')
           or (substr(nivel17, length(nivel17), 1) != '0')
           or (substr(nivel18, length(nivel18), 1) != '0')
           or (substr(nivel19, length(nivel19), 1) != '0')
           or (substr(nivel20, length(nivel20), 1) != '0')) t

 join cconta c on c.codconta = t.codconta

-- left join Saldo O on c.codconta = O.CodConta
order by t.codconta


Brigadao

[CassinhaParticipante]

To mexendo aqui todo esse tempo, o codigo ficou bem legal e completo, avançado para mim. Ele usa bastante sub select, até mesmo no from, bacana, vou continuar fazendo testes, vamos aguardar por enquanto.
😆

[fsitjaParticipante]

Eu tinha alterado várias outras coisas que no seu post acima ainda não estavam atualizadas, portanto não iria funcionar. Dá uma olhada abaixo e recomeça dali, pois tem algumas diferenças comparando com o que você tinha.

Tenta o seguinte:

with mov_mensal as (
select codconta,
nivel1,
nivel2,
nivel3,
ano,
nvl(sum(case when mes = '12' and ano = (2010 - 1) then mov_liq_mes end), 0) saldo_ant,
nvl(sum(case when mes <= '01' then mov_liq_mes end), 0) jan,
nvl(sum(case when mes <= '02' then mov_liq_mes end), 0) fev,
nvl(sum(case when mes <= '03' then mov_liq_mes end), 0) mar,
nvl(sum(case when mes <= '12' and ano = 2010 then mov_liq_mes end), 0) dez,
nvl(sum(case when ano = 2010 then mov_liq_mes end), 0) tot_ano
from (select c.codconta,
substr(c.codconta, 1, 1) nivel1,
substr(c.codconta, 1, 2) nivel2,
substr(c.codconta, 1, 3) nivel3,
to_char(data_lanc, 'YYYY') ano,
to_char(data_lanc, 'MM') mes,
sum(case when debito is not null then valor * -1
when credito is not null then valor end) mov_liq_mes
from (select rpad(codconta, 11, '0') codconta, descricao from cconta) c
left join (select rpad(conta, 11, '0') codconta,
l.data_lanc,
l.valor,
l.credito,
l.debito
from clanca l
where to_char(l.data_lanc, 'YYYY') = 2010
union
all
select rpad(s.codconta, 11, '0') codconta,
to_date('31/12/' || (2010 - 1), 'DD/MM/YYYY') data_lanc,
s.val_saldo valor,
0 credito,
null debito
from saldo s) l on l.codconta = c.codconta
group by c.codconta, to_char(data_lanc, 'MM'), to_char(data_lanc, 'YYYY'))
group by codconta, nivel1, nivel2, nivel3, ano)
-- end with
select c.descricao, t.*
from (select coalesce(nivel1, nivel2, nivel3) codconta,
sum(saldo_ant) saldo_ant,
sum(saldo_ant + jan) jan,
sum(saldo_ant + fev) fev,
sum(saldo_ant + mar) mar,
sum(saldo_ant + dez) dez,
sum(saldo_ant + tot_ano) saldo_final,
sum(tot_ano) tot_ano
from mov_mensal
group by grouping sets ((nivel1), (nivel2), (nivel3))
having (substr(nivel1, length(nivel1), 1) != '0')
or (substr(nivel2, length(nivel2), 1) != '0')
or (substr(nivel3, length(nivel3), 1) != '0')) t
join cconta c on c.codconta = t.codconta
where tot_ano != 0
or saldo_ant != 0
or jan != 0
or fev != 0
or mar != 0
or dez != 0
order by t.codconta;

A união é para juntar o saldo aos lançamentos, como se ele fosse um lançamento “inicial” de crédito.

Se continuar dando ORA-600, aí é algum bug na versão do Oracle que você está usando, geralmente tem correção no metalink para isso pois já deve ter sido identificado. No 11gR2 e no 10g (10.2.0.5.0) funciounou tranquilo.

[MARCIO_LOKParticipante]

Entao, minha tabela saldo… só tem tres campos codconta (codigo das contas)descricao e o valor…. mais o campo descricao nem utilizo dessa tabela, por isso digo que o que interessa é os dois campos, codigo e valor…

SQL> SELECT * FROM SALDO WHERE SUBSTR(CODCONTA,1,1) = '1';

CODCONTA DESCRICAO DEZ

12111900001 CAIXA -6500
12131900002 REAL ABN AMRO - C/C 5.710.132-5 -27012,87
12131900007 ITAU - UNIBANCO - C/C 822.506-6 -13564,98
12131900008 CAIXA ECONÔMICA FEDERAL - C/C 0.313.792- -12324,36
12131900009 NOSSA CAIXA NOSSO BANCO - C/C 04.001.052 -960,36
12131900010 SAFRA - C/C 286.140-8 -177,1
12311130001 .....

SQL>

brigadao, valeu

[fsitjaParticipante]

Realmente. Não sei o que uma coluna de descrição da Conta faz na tabela de saldo… 😆

[MARCIO_LOKParticipante]

é… infelizmente, deu pau..aquele erro…. ORA-000600….

minha versão é: 10.2.0.1.0, ou seja, inferior as versões citadas, vou atualizar e testar novamente, obrigado por enquanto ,assim te testar coloco aqui o resultado,….

É possivel atualizar a versão, sem ter que desinstalar a atual versao, instalar a nova, e restaurar bkps ne?

Brigadao

[fsitjaParticipante]

[quote=”MARCIO_LOK”:1jxfss0p]é… infelizmente, deu pau..aquele erro…. ORA-000600….

minha versão é: 10.2.0.1.0, ou seja, inferior as versões citadas, vou atualizar e testar novamente, obrigado por enquanto ,assim te testar coloco aqui o resultado,….

É possivel atualizar a versão, sem ter que desinstalar a atual versao, instalar a nova, e restaurar bkps ne?

Brigadao[/quote]

Foi só culpa da União? Tenta tirar o ALL, usar apenas “UNION” e vê se rola, pois internamente a operação feita é outra: há um sort e um “distinct” implícito, que é desnecessário e onera desempenho, mas pode ser um workaround temporário.

[Autor]

Posts