O que ha de errado nessa function? Nao acumula valor

[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