Query returning empty months

Javier P

Hi all,

I have this query that returns the production of the last 12 months.

This is the query:

select
    null
  , sum(f.comision)         value
  , trunc(f.efectiva, 'MM') label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM')

How can I get a line for every month no matter if it has any production or is just 0? Can this be done?

Thanks for the help!

Javier

Solomon Yakobson

@Javier P: I mean five years back from the end of the current year.

with t as (
      select 0 comision,
          add_months(trunc(sysdate,'yy'),(1-level) * 12) efectiva
       from dual
       connect by level <= 5
      union all
      select comision,
          trunc(efectiva,'yy')
       from risk_facturas
       where fefectiva >= add_months(trunc(sysdate,'yy'),-48)
        and efectiva < add_months(trunc(sysdate,'yy'),12)
        and organizacion_id = :APP_GET_ORGANIZACION_ID
     )
select null     NullValue,
    sum(comision) value,
    efectiva   label
 from t
 group by efectiva
 order by efectiva
/

SY.

User_H3J7U

select * from (your query) t
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = t.label

Javier P

Thanks User_H3J7U

What do mean with your query?

The full select statement?

select * from (select
    null
  , sum(f.comision)         value
  , trunc(f.efectiva, 'MM') label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM')) t
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = t.label

Like this?

Thanks again!

Javier P

Hi,

Am getting this error:

Thanks,

L. Fernigrini

Replace "f.label" with:

trunc(f.efectiva, 'MM')

Label does not exist as a column on the risk_facturas table, it is just an alias and cannot be used in the same query except on the ORDER BY clause. Or you can use it on a higher level (parent) query, like this:

select * from ( select
    null AS NullValue
  , sum(f.comision)         AS value
  , trunc(f.efectiva, 'MM') AS label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM') ) oq
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = oq.label

Javier P

Hi Fernigrini,

Thanks for your help!

I will try it that way.

Thanks

Solomon Yakobson

with t as (
            select  0 comision,
                    trunc(add_months(sysdate,1-level),'mm') efectiva
              from  dual
              connect by level < 14
           union all
            select  comision,
                    trunc(efectiva,'MM')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate),-13)
                and efectiva < trunc(sysdate) + 1
                and organizacion_id = :APP_GET_ORGANIZACION_ID
          )
select  null          NullValue,
        sum(comision) value,
        efectiva      label
  from  t
  group by efectiva
  order by efectiva
/

SY.

-- correction: f. is removed

Javier P

Hi SY

Thanks for the complete code and your help.

I appreciate it.

Javier P

Hi Sy

Can you help me with the same query but returning the last 5 year and grouping by year?

I need the query group by year and returning all last 5 years no matter if it has any production

Return 0 If it does not have any production for that year.

Thanks a lot for your help!

fac586

XY question

In this case it is simple enough to use the APEX chart settings to handle the data densification automatically.

https://community.oracle.com/tech/developers/discussion/4495775/graph-questions

Javier P

Hi fac586,

Yes, that is what I figured and am using the setting you mentioned to me but having other problems that I posted in the same thread.

Thanks always for you help!