This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,285 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

Query returning empty months

Javier P
Javier P Member Posts: 900 Silver Badge

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

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond

    @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.

    Javier P
«1

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    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
    Javier P Member Posts: 900 Silver Badge

    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
    Javier P Member Posts: 900 Silver Badge

    Hi,

    Am getting this error:

    Thanks,

  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown

    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
  • Javier P
    Javier P Member Posts: 900 Silver Badge

    Hi Fernigrini,

    Thanks for your help!

    I will try it that way.

    Thanks

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Mar 10, 2022 8:39PM
    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
  • Javier P
    Javier P Member Posts: 900 Silver Badge

    Hi SY

    Thanks for the complete code and your help.

    I appreciate it.

  • Javier P
    Javier P Member Posts: 900 Silver Badge

    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
    fac586 Senior Technical Architect EdinburghMember Posts: 21,579 Red Diamond

    XY question

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


  • Javier P
    Javier P Member Posts: 900 Silver Badge

    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!