Menu de navegação

[MARCIO_LOKParticipante]

Boa tarde amigos

To tentando fazer uma funcao aonde pegue os debitos e creditos dos meses ….e acumule me me retorne o total, o problema é que nao esta acumulando, ele está trazendo o saldo final, ou seja, se eu escolher 3 meses, ele tras o saldo dos tres meses, eu queria que ele trouxesse o saldo de janeiro+fevereiro+marco por exemplo, segue o codigo, se alguem puder analisar pra mim agradeço muito:

create or replace function soma(mes in date, mes2 IN VARCHAR2, Valor IN NUMBER, Conta IN VARCHAR2, Conta2 IN VARCHAR2, num IN VARCHAR2)
return NUMBER
is
v_string_retorno NUMBER(15,2);
v_credito NUMBER(15,2); v_debito NUMBER(15,2); ano_deb NUMBER(15,2); ano_cred NUMBER(15,2); total NUMBER(15,2);
i INT;
BEGIN
v_string_retorno := 0; v_credito := 0; v_debito:=0; ano_deb:=0; ano_cred:=0;
–i:=1;
FOR i IN 1..mes2 LOOP
–WHILE i <= mes2 Loop IF To_Char(mes,'mm') <= i AND To_Char(mes,'yy') = '10' AND SubStr(Conta,1,11) = num THEN v_Credito := v_Credito+valor; ELSIF To_Char(mes,'mm') <= i AND To_Char(mes,'yy') = '10' AND SubStr(Conta2,1,11) = num THEN v_Debito := v_Debito+valor; END IF; ano_cred:=(ano_cred+v_credito); ano_deb :=(ano_deb + v_debito); --i:=i+1; END LOOP; v_string_retorno:=(Ano_Cred-ano_deb); return (v_string_retorno); end;

[fsitjaParticipante]

Olá Marcio,

Acho que você já fez essa mesma pergunta várias vezes, sempre disfarçada de uma pergunta nova. O problema não é que as pessoas não saibam ou não queiram ajudar, é que você precisa facilitar para que nós possamos entender a lógica que você necessita.

Faça de conta que você não conhece nada sobre o seu problema atual e releia a questão. Você entenderia claramente aonde ela está querendo chegar?

Indo ao ponto, você precisa dar exemplo dos dados de entrada (tabelas do seu sistema que estão envolvidas, e dados delas) e a forma que você espera enxergar os dados na saída. Um exemplo pequeno mas que sirva para que validemos a nossa proposta de solução de acordo com a lógica que você pediu.

Bom, juntando um pouco do que consegui entender, o problema no momento está sendo acumular valores ao longo dos meses.

A função SUM faz somatório com acumulação incrementando, segue um exemplo:


SQL> create table clanca (conta number(10),
2 data_lanc date,
3 credito number(10, 2),
4 debito number(10, 2),
5 valor number(10, 2));

Table created
SQL> begin
2 insert into clanca (conta, data_lanc, credito, debito, valor)
3 values (100, to_date('05/01/2010', 'DD/MM/YYYY'), 1, null, 2000);
4 insert into clanca (conta, data_lanc, credito, debito, valor)
5 values (100, to_date('08/01/2010', 'DD/MM/YYYY'), null, 1, 500);
6 insert into clanca (conta, data_lanc, credito, debito, valor)
7 values (100, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 2000);
8 insert into clanca (conta, data_lanc, credito, debito, valor)
9 values (100, to_date('02/02/2010', 'DD/MM/YYYY'), null, 1, 500);
10 insert into clanca (conta, data_lanc, credito, debito, valor)
11 values (100, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 500);
12 insert into clanca (conta, data_lanc, credito, debito, valor)
13 values (200, to_date('25/01/2010', 'DD/MM/YYYY'), 1, null, 2000);
14 insert into clanca (conta, data_lanc, credito, debito, valor)
15 values (200, to_date('01/02/2010', 'DD/MM/YYYY'), null, 1, 500);
16 insert into clanca (conta, data_lanc, credito, debito, valor)
17 values (200, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 2000);
18 insert into clanca (conta, data_lanc, credito, debito, valor)
19 values (200, to_date('01/03/2010', 'DD/MM/YYYY'), null, 1, 500);
20 insert into clanca (conta, data_lanc, credito, debito, valor)
21 values (200, to_date('20/04/2010', 'DD/MM/YYYY'), null, 1, 500);
22 commit;
23 end;
24 /

PL/SQL procedure successfully completed

SQL> select conta,
2 ano,
3 mes,
4 movim_liquido_mes,
5 valor_acumulado_ano
6 from (select conta,
7 to_char(data_lanc, 'YYYY') ano,
8 to_char(data_lanc, 'MM') mes,
9 sum(case when debito is not null then valor * -1
10 when credito is not null then valor end) movim_liquido_mes,
11 sum(sum(case when debito is not null then valor * -1
12 when credito is not null then valor end))
13 over (partition by conta, to_char(data_lanc, 'YYYY') order by to_char(data_lanc, 'MM') asc) valor_acumulado_ano
14 from clanca
15 group by conta, to_char(data_lanc, 'MM'), to_char(data_lanc, 'YYYY'));

  CONTA ANO  MES MOVIM_LIQUIDO_MES VALOR_ACUMULADO_ANO

---

    100 2010 01               1500                1500
    100 2010 02               1500                3000
    100 2010 03               -500                2500
    200 2010 01               2000                2000
    200 2010 02               1500                3500
    200 2010 03               -500                3000
    200 2010 04               -500                2500

7 rows selected

SQL>

A documentação Oracle tem mais detalhes, se você quiser ler fica na url abaixo:
http://download.oracle.com/docs/cd/E118 … ons004.htm

[MARCIO_LOKParticipante]

Bom primeiramente, obrigado por ajudar…
Minha intenção nunca foi fazer perguntas “disfarçadas” pelo contrário, estou fazendo algo um pouco diferente, e agora tenho pelo menos uma função….
outra coisa também, eu jamais disse que as pessoas nao tão querendo me ajudar, pelo contrario, esse Forum, tem sido de grande ajuda…. tenho aprendido mais com os amigos aqui do que em qualquer curso…
SEguindo seu exemplo, elaborei um exemplo do que estou tentando fazer, segue:

SQL> create table clanca (
2 data date,
3 credito varchar2(11),
4 debito varchar2(11),
5 valor number(10,2));

SQL> create table cConta (Codconta varchar2(11);
2 descricao varchar2(40);

SQL> create table Saldo (Codconta varchar2(11);
2 descricao varchar2(40);
dez number(10,2);

Ligacoes: A coluna Debito ou credito da tabela cLanca liga com a Coluna CodConta da tabela cConta e Saldo.

–Insert cLanca
insert into clanca (data, credito, debito, valor)
values (to_date(’05/01/2010′, ‘DD/MM/YYYY’), 12345678909, null, 2000);
insert into clanca (data, credito, debito, valor)
values (to_date(’06/01/2010′, ‘DD/MM/YYYY’), 12345678909, null, 4034);
insert into clanca (data, credito, debito, valor)
values (to_date(’05/01/2010′, ‘DD/MM/YYYY’), 12345678909, null, 2066);
insert into clanca (data, credito, debito, valor)
values (to_date(’06/01/2010′, ‘DD/MM/YYYY’), 12345678909, null, 4450);
insert into clanca (data, credito, debito, valor)
values (to_date(’10/01/2010′, ‘DD/MM/YYYY’), 12345678911, null, 2000);
insert into clanca (data, credito, debito, valor)
values (to_date(’23/01/2010′, ‘DD/MM/YYYY’), 12345678911, null, 4000);

–Insert Saldo
insert into Saldo (CodConta, dez)
values (‘12345678909’,234.77);
insert into Saldo (CodConta, dez)
values (‘12345678911’,500);

Correto?

O script que estou utilizando é este aqui:
Select
c.codConta As CODCONTAS

 ,C.Descricao As DESCRICAO

  , (CASE WHEN
  length(c.codConta) = 01  THEN (Sum(case when To_Char(L.Data, 'mm')  = '01' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm')  = '01' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN  L.Valor end)+Nvl(O.dez,0))END) Janeiro

, (CASE WHEN &mes >= 02 THEN (Sum(case when To_Char(L.Data, 'mm') <= '02' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 03 THEN (Sum(case when To_Char(L.Data, 'mm') <= '03' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 04 THEN (Sum(case when To_Char(L.Data, 'mm') <= '04' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 05 THEN (Sum(case when To_Char(L.Data, 'mm') <= '05' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 06 THEN (Sum(case when To_Char(L.Data, 'mm') <= '06' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 07 THEN (Sum(case when To_Char(L.Data, 'mm') <= '07' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 08 THEN (Sum(case when To_Char(L.Data, 'mm') <= '08' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 09 THEN (Sum(case when To_Char(L.Data, 'mm') <= '09' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 10 THEN (Sum(case when To_Char(L.Data, 'mm') <= '10' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 11 THEN (Sum(case when To_Char(L.Data, 'mm') <= '11' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') = 12 THEN (Sum(case when To_Char(L.Data, 'mm') <= '12' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito  THEN -L.Valor
           when To_Char(L.Data, 'mm') <= '12' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN  L.Valor end)+Nvl(O.dez,0)) END) Dezembro

From cLanca L right join cConta C
ON L.CodColigada = C.CodColigada
AND (L.Credito = C.CodConta
OR L.Debito = C.CodConta)
AND To_Char(L.Data,'yy') = '10'
left join Saldo O
ON O.CodConta = C.CodConta
where
Substr(C.Codconta,1,1) = '1'

GROUP BY C.CodConta,
C.Descricao, O.dez

ORDER BY C.codConta

---

Então o que eu to querendo fazer é, no final de tudo, depois dele apresentar o valor de todos os meses que eu quero… to querendo apresentar uma ultima coluna….. chamada total…
que é a soma dos meses por conta… exemplo:

codConta Janeiro fevereiro total (Esta coluna tem que trazer o total dos meses que escolhi, conforme cada conta)
12345678911 345,88 45,88 391,76
12345678909 45,88 55,77 101,65
Obs: No exemplo a cima, escolhi 2 meses… caso estivesse escolhido 12 meses de 2009, me traria o valor dos 12 e a somatoria no final….

Depois preciso somar tambem, todas as contas que comecem com ‘1’ (substr(c.CodConta,1,1) = ‘1’))….

Se puder me ajudar, vou trabalhando em cima do seu exemplo, vamos ver se consigo desenvolver alguma coisa, obrigado mais uma vez.

Marcio

[MARCIO_LOKParticipante]

Entao, no exemplo que voce deu…. o que eu preciso saber é….
retornar apenas o valor…..
somar por contas…
conta valor
100 2500 (total dos 3 meses por exemplo)
200 2500

  CONTA ANO  MES MOVIM_LIQUIDO_MES VALOR_ACUM_ANO 

---

    100 2010 01               1500                1500 
    100 2010 02               1500                3000 
    100 2010 03               -500                2500 
    200 2010 01               2000                2000 
    200 2010 02               1500                3500 
    200 2010 03               -500                3000 
    200 2010 04               -500                2500 

7 rows selected

SQL>
valeu

[fsitjaParticipante]

Opa, beleza Marcio,

Então, mas como vai ser feita a consulta? Se o Mês for passado para o SQL daria para colocar o que postei antes no from (ou numa view) e selecionar apenas a linha do mês desejado. Exemplo:


SQL> select conta, ano, mes, valor_acumulado_ano
2 from
3 (
4 select conta,
5 ano,
6 mes,
7 movim_liquido_mes,
8 valor_acumulado_ano
9 from (select conta,
10 to_char(data_lanc, 'YYYY') ano,
11 to_char(data_lanc, 'MM') mes,
12 sum(case when debito is not null then valor * -1
13 when credito is not null then valor end) movim_liquido_mes,
14 sum(sum(case when debito is not null then valor * -1
15 when credito is not null then valor end))
16 over (partition by conta, to_char(data_lanc, 'YYYY') order by to_char(data_lanc, 'MM') asc) valor_acumulado_ano
17 from clanca
18 group by conta, to_char(data_lanc, 'MM'), to_char(data_lanc, 'YYYY'))
19 ) where mes = 3;

  CONTA ANO  MES VALOR_ACUMULADO_ANO

---

    100 2010 03                 2500
    200 2010 03                 3000

Ou colocando numa view:

SQL> create or replace view vw_movimento_mensal as
2 select conta,
3 ano,
4 mes,
5 movim_liquido_mes,
6 valor_acumulado_ano
7 from (select conta,
8 to_char(data_lanc, 'YYYY') ano,
9 to_char(data_lanc, 'MM') mes,
10 sum(case when debito is not null then valor * -1
11 when credito is not null then valor end) movim_liquido_mes,
12 sum(sum(case when debito is not null then valor * -1
13 when credito is not null then valor end))
14 over (partition by conta, to_char(data_lanc, 'YYYY') order by to_char(data_lanc, 'MM') asc) valor_acumulado_ano
15 from clanca
16 group by conta, to_char(data_lanc, 'MM'), to_char(data_lanc, 'YYYY'));

View created

SQL> select * from vw_movimento_mensal where mes = 3;

  CONTA ANO  MES MOVIM_LIQUIDO_MES VALOR_ACUMULADO_ANO

---

    100 2010 03               -500                2500
    200 2010 03               -500                3000

SQL>


Como o mês de abril continha um lançamento também, ele não aparece nessa consulta no saldo de março ainda.

[MARCIO_LOKParticipante]

Hum, assim deu certo.
Da pra pegar o total…

Um ultimo problema que nao consegui resolver é:
Nesse script que te passei ele apresenta as contas assim:
Conta Descricao Janeiro fevereiro….até dezembro
1 Ativo
12 Circulante
123 Aplicacoes
1234 Bancos
1234567899 Brasil 345.88 456.88
1234567888 Bradesco 6567.99 6789.99

Entao, eu preciso somar na frente do Ativo todos os codigos da conta que comecam com 1….
Somar no circulante todos as contas que comecam com 12….
Somar na frente da aplicacoes todos as contas que comecam 123..
assim por diante… pegar mes a mes…

no script que eu fiz, só tenho valores (cconta) nos codigos com 11 caracteres, precisava somar eles na frente do ativo…

Caso vc nao possa me ajudar, vou postar como um novo topico, e encerrar esse, que voce ja me ajudou muito…
Valeu

[fsitjaParticipante]

Umm… não entendi muito bem. “Ativo”, “Circulante”, “Aplicacoes” e “Banco” etc são tipo uma hierarquia, onde cada algarismo que compõe o número da conta define qual é?

Tipo, Conta que comece com 1 é Ativo, Conta que comece por 2 é passivo e assim por diante? Se for assim onde fica essa hierarquia, onde a gente pega a descrição “Ativo”, “Circulante”, etc? A hierarquia fica na mesma tabela CCONTA onde ficam as contas que possuem saldo, no problema anterior?

O que você quer é assim?

Conta Desc JAN ... DEZ TOTAL
1 ATIVO 1000 3000 25000
1.1 CIRCULANTE 300 2000 15000
1.2 NÃO CIRCULANTE 700 1000 10000

Totalizando no nível superior o quanto tem nos níveis de baixo?

Se for, esclarece por gentileza se a tabela de contas é uma só, e se pode haver débitos e créditos apenas em nós do último nível (folhas).

A consulta sempre será filtrada para um único ano do calendário?

[MARCIO_LOKParticipante]

Isso… exatamente….

Vou responder suas perguntas:(negrito)

Umm… não entendi muito bem. “Ativo”, “Circulante”, “Aplicacoes” e “Banco” etc são tipo uma hierarquia, onde cada algarismo que compõe o número da conta define qual é?
Tabela: CCONTA

Tipo, Conta que comece com 1 é Ativo, Conta que comece por 2 é passivo e assim por diante? Se for assim onde fica essa hierarquia, onde a gente pega a descrição “Ativo”, “Circulante”, etc? A hierarquia fica na mesma tabela CCONTA onde ficam as contas que possuem saldo, no problema anterior?
Exatamente

O que você quer é assim?
Código:

Conta Desc JAN … DEZ TOTAL
1 ATIVO 1000 3000 25000
1.1 CIRCULANTE 300 2000 15000
1.2 NÃO CIRCULANTE 700 1000 10000
Exatamente

Totalizando no nível superior o quanto tem nos níveis de baixo?
Exatamente

Se for, esclarece por gentileza se a tabela de contas é uma só, e se pode haver débitos e créditos apenas em nós do último nível (folhas).
[b]A tabela de contas é uma só (CCONTAS)
Tabela de movimentacao (CLANCA)
pode haver debitos e creditos sim[/b]

A consulta sempre será filtrada para um único ano do calendário?
Não, eu terei que entrar para o cliente.

Valeu

[MARCIO_LOKParticipante]

Só pra completar a resposta do email a cima…..

O script que postei acima…. faz todo esse procedimento, só nao totaliza nos niveis superiores…. deve ser porque usei right join em uma tabela, e left join na outra… valeu , brigadao

[MARCIO_LOKParticipante]

Alguem pode me ajudar?[b][/b]

[fsitjaParticipante]

Marcio,

Desculpe se é minha falha, mas eu estou tendo dificuldades para entender o layout do relatório.

O que você pediu inicialmente, para acumular os dados ao longo dos meses, é uma coisa incompatível com a subtotalização por nível de conta. Isso porque se for subtotalizar os acumulados vai dar todo o cálculo errado.

A função grouping sets pode ser usada para definir rollups e agrupamentos “customizados”, mas o que você está procurando é, essencialmente, um outro relatório, distinto do anterior.

— Para ilustrar:

No exemplo de script que você postou não existem os dados que você citou que quer enxergar, então não tem como testar. O script tem erros de sintaxe, logo nem roda. Quando for postar código, por favor, tente validar antes e no post use as tags de code entre colchetes para manter a formatação e ficar legível.

De toda forma, veja no exemplo que criei abaixo:

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

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, 'ATIVO');
4 insert into cconta (codconta, descricao) values (2, 'PASSIVO');
5 insert into cconta (codconta, descricao) values (11, 'CIRCULANTE');
6 insert into cconta (codconta, descricao) values (12, 'NÃO-CIRCULANTE');
7 insert into cconta (codconta, descricao) values (111, 'APLICAÇÕES');
8 -- inserts de lancamentos
9 insert into clanca (conta, data_lanc, credito, debito, valor)
10 values (11, to_date('05/01/2010', 'DD/MM/YYYY'), 1, null, 2000);
11 insert into clanca (conta, data_lanc, credito, debito, valor)
12 values (11, to_date('08/01/2010', 'DD/MM/YYYY'), null, 1, 500);
13 insert into clanca (conta, data_lanc, credito, debito, valor)
14 values (11, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 2000);
15 insert into clanca (conta, data_lanc, credito, debito, valor)
16 values (11, to_date('02/02/2010', 'DD/MM/YYYY'), null, 1, 500);
17 insert into clanca (conta, data_lanc, credito, debito, valor)
18 values (11, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 500);
19 insert into clanca (conta, data_lanc, credito, debito, valor)
20 values (111, to_date('10/02/2010', 'DD/MM/YYYY'), 1, null, 999);
21 insert into clanca (conta, data_lanc, credito, debito, valor)
22 values (12, to_date('25/01/2010', 'DD/MM/YYYY'), 1, null, 2000);
23 insert into clanca (conta, data_lanc, credito, debito, valor)
24 values (12, to_date('01/02/2010', 'DD/MM/YYYY'), null, 1, 500);
25 insert into clanca (conta, data_lanc, credito, debito, valor)
26 values (12, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 2000);
27 insert into clanca (conta, data_lanc, credito, debito, valor)
28 values (12, to_date('01/03/2010', 'DD/MM/YYYY'), null, 1, 500);
29 insert into clanca (conta, data_lanc, credito, debito, valor)
30 values (12, to_date('20/04/2010', 'DD/MM/YYYY'), null, 1, 500);
31 commit;
32 end;
33 /

PL/SQL procedure successfully completed

SQL>
SQL> select codconta,
2 nivel1,
3 nivel2,
4 ano,
5 mes,
6 movim_liquido_mes,
7 valor_acumulado_ano
8 from (select c.codconta,
9 substr(c.codconta, 1, 1) nivel1,
10 substr(c.codconta, 1, 2) nivel2,
11 to_char(data_lanc, 'YYYY') ano,
12 to_char(data_lanc, 'MM') mes,
13 sum(case when debito is not null then valor * -1
14 when credito is not null then valor end) movim_liquido_mes,
15 sum(sum(case when debito is not null then valor * -1
16 when credito is not null then valor end))
17 over (partition by c.codconta, to_char(data_lanc, 'YYYY') order by to_char(data_lanc, 'MM') asc) valor_acumulado_ano
18 from (select rpad(codconta, 11, '0') codconta, descricao from cconta) c
19 left join (select rpad(conta, 11, '0') codconta, l.* from clanca l) l on l.codconta = c.codconta
20 group by
21 grouping sets (
22 (c.codconta, to_char(data_lanc, 'MM'), to_char(data_lanc, 'YYYY')),
23 rollup(substr(c.codconta, 1, 1)),
24 rollup(substr(c.codconta, 1, 2))
25 ));

CODCONTA NIVEL1 NIVEL2 ANO MES MOVIM_LIQUIDO_MES VALOR_ACUMULADO_ANO

---

10000000000
11000000000 2010 01 1500 1500
11000000000 2010 02 1500 3000
11000000000 2010 03 -500 2500
11100000000 2010 02 999 999
12000000000 2010 01 2000 2000
12000000000 2010 02 1500 3500
12000000000 2010 03 -500 3000
12000000000 2010 04 -500 2500
20000000000
20 23996
10 23996
5999 23996
12 2500 23996
11 3499 23996
1 5999 23996
5999 23996
2 23996

18 rows selected

SQL>


A coluna de acumulado mostra uma totalização do acumulado anteriormente, o que dá um resultado maluco que não é correto (23.996).

Outra coisa: o seu modelo de dados está bastante confuso, com as contas de vários níveis dentro da mesma tabela, sem relacionamentos nem restrições de integridade (FKs). Mesmo para poder fazer a subtotalização simples (sem a coluna de acumulado), seria necessário saber de antemão o número de níveis da hierarquia.

[MARCIO_LOKParticipante]

O que eu to querendo é apenas somar as contas….e subcontas… apenas isso…

conta descricao janeiro fevereiro …ate dezembro

1 contaprincipal 6 7
12 segundaconta 6 7
123 terceiraconta 6 7
1234 quartaconta 6 7
12345 etc quintaconta1 1 5
12346 quintaconta2 2 1
12347 quintaconta3 3 1

Obs: As Contas com o mesmo tamanho e que comecam com um (por exemplo) ele soma, como no caso 12345, 12346 e 12347 e ai somando em cima das contas que comecam com 1….. e assim com todas que comecam com dois…etc…

Parece que o caminho deve ser com esse grouping set, que eu nao tinha nem ouvido falar, vou pesquisar, se vc puder me ajudar, agradeco muito.

Marcio

[fsitjaParticipante]

Marcio, fiz um exemplo simplificado, apenas para 3 meses (jan, fev e mar) e para uma hierarquia de contas com 3 níveis de profundidade. Porém para incrementar com mais níveis é bem simples, só evoluir conforme está abaixo.

No where é preciso selecionar apenas um único ano cada vez que executar o SQL. No exemplo coloquei ‘2010’.

A tabela de saldo eu não coloquei, mas já mostrei num outro tópico como que faz com ela.


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as FSITJA

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

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 lancamentos
12 insert into clanca (conta, data_lanc, credito, debito, valor)
13 values (11, to_date('05/01/2010', 'DD/MM/YYYY'), 1, null, 20000);
14 insert into clanca (conta, data_lanc, credito, debito, valor)
15 values (11, to_date('08/01/2010', 'DD/MM/YYYY'), null, 1, 50000);
16 insert into clanca (conta, data_lanc, credito, debito, valor)
17 values (11, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 80000);
18 insert into clanca (conta, data_lanc, credito, debito, valor)
19 values (11, to_date('02/02/2010', 'DD/MM/YYYY'), null, 1, 130000);
20 insert into clanca (conta, data_lanc, credito, debito, valor)
21 values (11, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 40000);
22 insert into clanca (conta, data_lanc, credito, debito, valor)
23 values (111, to_date('10/02/2010', 'DD/MM/YYYY'), 1, null, 20000);
24 insert into clanca (conta, data_lanc, credito, debito, valor)
25 values (112, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 10000);
26 insert into clanca (conta, data_lanc, credito, debito, valor)
27 values (12, to_date('25/01/2010', 'DD/MM/YYYY'), 1, null, 3000);
28 insert into clanca (conta, data_lanc, credito, debito, valor)
29 values (12, to_date('01/02/2010', 'DD/MM/YYYY'), null, 1, 1000);
30 insert into clanca (conta, data_lanc, credito, debito, valor)
31 values (12, to_date('01/02/2010', 'DD/MM/YYYY'), 1, null, 5000);
32 insert into clanca (conta, data_lanc, credito, debito, valor)
33 values (12, to_date('01/03/2010', 'DD/MM/YYYY'), null, 1, 7000);
34 insert into clanca (conta, data_lanc, credito, debito, valor)
35 values (12, to_date('20/03/2010', 'DD/MM/YYYY'), null, 1, 5000);
36 insert into clanca (conta, data_lanc, credito, debito, valor)
37 values (2, to_date('20/01/2010', 'DD/MM/YYYY'), 1, null, 100);
38 insert into clanca (conta, data_lanc, credito, debito, valor)
39 values (21, to_date('20/01/2010', 'DD/MM/YYYY'), 1, null, 200);
40 insert into clanca (conta, data_lanc, credito, debito, valor)
41 values (211, to_date('20/01/2010', 'DD/MM/YYYY'), 1, null, 300);
42 commit;
43 end;
44 /

PL/SQL procedure successfully completed

SQL>
SQL> with mov_mensal as (
2 select codconta,
3 nivel1,
4 nivel2,
5 nivel3,
6 ano,
7 sum(case when mes = '01' then mov_liq_mes end) jan,
8 sum(case when mes = '02' then mov_liq_mes end) fev,
9 sum(case when mes = '03' then mov_liq_mes end) mar,
10 sum(mov_liq_mes) tot_ano
11 from (select c.codconta,
12 substr(c.codconta, 1, 1) nivel1,
13 substr(c.codconta, 1, 2) nivel2,
14 substr(c.codconta, 1, 3) nivel3,
15 to_char(data_lanc, 'YYYY') ano,
16 to_char(data_lanc, 'MM') mes,
17 sum(case when debito is not null then valor * -1
18 when credito is not null then valor end) mov_liq_mes
19 from (select rpad(codconta, 11, '0') codconta, descricao from cconta) c
20 left join (select rpad(conta, 11, '0') codconta, l.*
21 from clanca l
22 where to_char(l.data_lanc, 'YYYY') = '2010') l on l.codconta = c.codconta
23 group by c.codconta, to_char(data_lanc, 'MM'), to_char(data_lanc, 'YYYY'))
24 group by codconta, nivel1, nivel2, nivel3, ano)
25 -- end with
26 select c.descricao, t.*
27 from (select coalesce(nivel1, nivel2, nivel3) codconta,
28 sum(jan) jan,
29 sum(fev) fev,
30 sum(mar) mar,
31 sum(tot_ano) tot_ano
32 from mov_mensal
33 group by grouping sets ((nivel1), (nivel2), (nivel3))
34 having (substr(nivel1, length(nivel1), 1) != '0')
35 or (substr(nivel2, length(nivel2), 1) != '0')
36 or (substr(nivel3, length(nivel3), 1) != '0')) t
37 join cconta c on c.codconta = t.codconta
38 order by t.codconta;

DESCRICAO CODCONTA JAN FEV MAR TOT_ANO

---

1.0.0 1 -27000 -26000 -62000 -115000
1.1.0 11 -30000 -30000 -50000 -110000
1.1.1 111 20000 20000
1.1.2 112 -10000 -10000
1.2.0 12 3000 4000 -12000 -5000
2.0.0 2 600 600
2.1.0 21 500 500
2.1.1 211 300 300

8 rows selected

SQL>


[MARCIO_LOKParticipante]

Parabens, o que eu queria era exatamente isso desde o inicio,
outro ponto positivo é que o tempo de execução do script….o que eu estava tentando desenvolver demorava em media de 4 a cinco MINUTOS, o teu… leva 09 segundos em uma base enorme….. parabens mesmo, pela paciência… pelo seu nivel de conhecimento… fiz algumas adaptações, levarei um bom tempo pra entender o que se passa ali…
eu estava tentando usar funções, criar matriz bidimensional para guardar os codigos, etc….. vc conseguiu resolver tudo, num unico codigo, e em um tempo de execução incrivel.

Só que alguns detalhes precisam ser feitos, acredito que agora seja bem simples pra voce…

1) eu quero que ele traga apenas todos os codigos que comecem com ‘3’ , seria apenas incluir um –and substr(c.codconta),1,1) = ‘3’ só nao sei aonde coloco isso, tentei em alguns selects e deu erro…. parece ser simples isso correto?

2) a tabela clanca… nao tem o campo CONTA, ai esta dando erro nesta expressao sua…
“….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.*
from clanca l….

Obs: Substitui por debito só para testar, porem acredito que precisaria vincula-la com o credito tambem, para que os valores batam correto?
Como poderia fazer isso?

3) o numero maximo de contas que tenho hoje é 11 digitos…. preparei para 20 digitos…. funcionou perfeitamente… só que percebi que ele traz os valores das contas…. só nao tras das contas que que terminam…01 (onze digitos), exemplo:
311331123401 (termina com 01) essa ele nao tras o valor na frente…
311331123402 tras perfeitamente
311331123403 tras perfeitamente

Só nao tras mesmo as que teminam com 01…

4) ultima coisa:
Existem algumas contas que nao trazem valor nenhum no mes….exemplo:
conta jan fev marc …..
1122 0 0 0 nao mostrar essa conta,
567 0 345 0 mostrar essa…pq tem movimento..

nao tras nenhum valor… eu queria que ele nao trouxesse, nao mostrasse as contas que nao tem valor…. se tiver valor em um dos meses pode trazer… nao é pra trazer se nao houver movimento no periodo… parece simples ne?

Brigadao… vou mexendo aqui pra ver se resolvo algo…

meu codigo adaptado ficou assim:

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 * -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(debito, 11, '0') codconta, l.*
                     from clanca l
                    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)

— end with
select c.descricao, t.*
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,
sum(jan) jan,
sum(fev) fev,
sum(mar) mar,
sum(abr) abr,
sum(mai) mai,
sum(jun) jun,
sum(jul) jul,
sum(ago) ago,
sum(sete) sete,
sum(out) out,
sum(nov) nov,
sum(dez) dez,
sum(tot_ano) tot_ano,
Round(sum(tot_ano),2)/12 media
from mov_mensal
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

order by t.codconta;

Obs: Mudei o nome do campo Data_Lanc, para Data….acrecentei 20 niveis…. embora só trabalhei com 11 no maximo, vou deixar pronto para crescer até 20……

Valeu… pela eficiencia do codigo, pela paciencia, e pela forca de vontade em ajudar…. ainda existe profissionais competentes como voce… prontos a ajudar os iniciantes… obrigado

[MARCIO_LOKParticipante]

amigo, eu pedi para que você me ajudasse em quatro questoes…

uma delas conseguir resolver…. e outras consegui identificar…

Resolvida:
1) eu quero que ele traga apenas todos os codigos que comecem com ‘3’ , seria apenas incluir um –and substr(c.codconta),1,1) = ‘3’ só nao sei aonde coloco isso, tentei em alguns selects e deu erro…. parece ser simples isso correto?
Coloquei aqui, deu certo:
… from (select rpad(codconta, 20, ‘0’) codconta, descricao from cconta WHERE SubStr(codconta,1,1)=’3′) c…

A que consegui identificar é a questao 2..
2) a tabela clanca… nao tem o campo CONTA, ai esta dando erro nesta expressao sua…
“….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.*
from clanca l….
Conforme mencionado acima… o problema é no campo conta da clanca… eu nao tenho esse campo…. se eu substituir para debito da um valor… para credito da outro, acho que precisaria colocar os dois.. ou vincular…
acredito que resolvendo isso, a questao numero três tambem será resolvida, porque os codigos com final 001 nao aparecem porque o campo conta foi substituido por credito, quando substitui por debito apareceu, acredito que resolvendo um, resolverá o outro…

estou tentando resolver a questao numero 4…
assim que possivel, me ajuda, obrigado

[Autor]

Posts