O que ha de errado nessa function? Nao acumula valor

[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