Hi everyone,
I have the following query that is triggering the ORA-00918: column ambiguously defined and the ORA-02063: preceding line from ORCL errors:
select 'KPI305-20130113', '% do Volume de Negócios investido em IDI', 'A', cod_mes, 'T' , round(nvl(sum(nvl(tot1,0))+sum(nvl(tot2,0)),0)/sum(nvl(tot3,0)),4) total, sysdate
from ( select to_date(ctb.cod_mes, 'YYYYMM') cod_mes
, sum(case when substr(poc.cod_conta_poc_real,1,1) in ('6') then ctb.val_saldo else 0 end) tot1
, 0 tot2
, 0 tot3
from lpr.bi_lpr_fct_patrimonial@orcl ctb
, lpr.bi_lpr_dim_poc@orcl poc
, lpr.bi_lpr_dim_centro_custo_ctb@orcl cus
where ctb.cod_conta_poc = poc.cod_conta_poc
and ctb.cod_centro_custo = cus.cod_centro_custo
and substr(poc.cod_conta_poc_real, 1, 2) in ('61','62','63','64','65')
and substr(cus.dsc_centro_custo, 1, 7) = '9303999'
and ctb.cod_mes >= '201001'
and ctb.cod_mes < to_char(sysdate, 'YYYYMM')
and to_date(ctb.cod_mes, 'RRRRMM')=(select max(to_date(x.cod_mes, 'RRRRMM'))
from lpr.bi_lpr_fct_patrimonial@orcl x
where to_char(to_date(x.cod_mes, 'RRRRMM'),'Q')=4
and substr(x.cod_mes,1,4)=substr(ctb.cod_mes,1,4))
group by to_date(ctb.cod_mes, 'YYYYMM')
union all
select to_date(ctb.cod_mes, 'YYYYMM')
, 0 tot1
, sum(case when substr(poc.cod_conta_poc_real,1,1) in ('4') then ctb.val_debito else 0 end) tot2
, 0 tot3
from lpr.bi_lpr_fct_patrimonial@orcl ctb
,lpr.bi_lpr_dim_poc@orcl poc
,lpr.bi_lpr_dim_centro_custo_ctb@orcl cus
where ctb.cod_conta_poc = poc.cod_conta_poc
and ctb.cod_centro_custo = cus.cod_centro_custo
and substr(poc.cod_conta_poc_real, 1, 4) in ('4')
and substr(cus.dsc_centro_custo, 1, 7) = '9303999'
and ctb.cod_mes >= '201001'
and ctb.cod_mes < to_char(sysdate, 'YYYYMM')
and to_date(ctb.cod_mes, 'RRRRMM')=(select max(to_date(x.cod_mes, 'RRRRMM'))
from lpr.bi_lpr_fct_patrimonial@orcl x
where to_char(to_date(x.cod_mes, 'RRRRMM'),'Q')=4
and substr(x.cod_mes,1,4)=substr(ctb.cod_mes,1,4))
group by to_date(ctb.cod_mes, 'YYYYMM')
union all
select to_date(ctb.cod_mes, 'YYYYMM')
, 0 tot1
, 0 tot2
, sum(case when substr(poc.cod_conta_poc_real,1,1) = '7' then -ctb.val_saldo else 0 end) tot3
from lpr.bi_lpr_fct_patrimonial@orcl ctb
,lpr.bi_lpr_dim_poc@orcl poc
,lpr.bi_lpr_dim_centro_custo_ctb@orcl cus
where ctb.cod_conta_poc = poc.cod_conta_poc
and ctb.cod_centro_custo = cus.cod_centro_custo
and substr(poc.cod_conta_poc_real, 1, 2) in ('71')
and ctb.cod_mes >= '201001'
and ctb.cod_mes < to_char(sysdate, 'YYYYMM')
and to_date(ctb.cod_mes, 'RRRRMM')=(select max(to_date(x.cod_mes, 'RRRRMM'))
from lpr.bi_lpr_fct_patrimonial@orcl x
where to_char(to_date(x.cod_mes, 'RRRRMM'),'Q')=4
and substr(x.cod_mes,1,4)=substr(ctb.cod_mes,1,4))
group by to_date(ctb.cod_mes, 'YYYYMM') )
group by cod_mes;
This query is used in a select statement and i'm testing it with SQL Developer.What am i doing wrong here and how can i fix this?
Many thanks for your replies!