1 Reply Latest reply on Sep 13, 2017 6:06 PM by gaston_from_france

    Apex 5 - Bar chart - Data densification - ORA-01843

    gaston_from_france

      Dear all,

       

      I am having a problem for a quite simple bar chart in apex 5.

      I'd like to sum amounts of orders for each month for separate years. The bar chart would have sum() on the y axis, months on x axis and series would be the years.

       

      I had to make some densification for months where there is no order. The query works fine if I run it in the SQL workshop and gives me the expected results.

       

      But when I try to create the chart, I have a ORA-01843. It seems to be linked with a date problem.

       

      Could I have your advise on the query ? SQL is obviously not my job, so it might hurt your eyes

      Thanks,

       

       

       

      WITH year_month AS (

      SELECT to_char(ADD_MONTHS(TRUNC(TO_DATE('01-Jan-2016', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1), 'MMYYYY') AS ym

      FROM   DUAL

      CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('01-Jan-2016', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)

          <= TRUNC(TO_DATE(sysdate), 'MON'))

       

       

      SELECT extract(year from (to_date(year_month.ym, 'MMYYYY'))) as "Année", extract(month from (to_date(year_month.ym, 'MMYYYY'))) as "Mois",  nvl(sum(date_total.Total),0) as "Montant"

         FROM   year_month

        LEFT OUTER JOIN (

             SELECT sum(MONTANT_L) as Total, D_CMD

             FROM CMD, DET_CMD

             where REF_CMD_DET = CMD_ID

             group by D_CMD) date_total

      ON (TO_CHAR(date_total.D_CMD,'MMYYYY') = year_month.ym)

      group by extract(year from (to_date(year_month.ym, 'MMYYYY'))), extract(month from (to_date(year_month.ym, 'MMYYYY')))

      order by extract(year from (to_date(year_month.ym, 'MMYYYY'))), extract(month from (to_date(year_month.ym, 'MMYYYY')))

       

      And here is the result of the query in the SQL workshop (it goes until 09/2017) :

       

      Capture d’écran 2017-09-13 à 16.43.47.jpg

        • 1. Re: Apex 5 - Bar chart - Data densification - ORA-01843
          gaston_from_france

          Anyone here ?

           

          I'm trying different stuff, and in the end the query always gives the good results, but I still have the same error ora-01843 when I try to plot the data (not a valid month).

           

          Here the last query I tried :

           

          WITH year_month AS (

          SELECT ADD_MONTHS(TRUNC(TO_DATE('01-Jan-2016', 'DD-MON-YY'), 'MON'), ROWNUM - 1) AS ym

          FROM   DUAL

          CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('01-Jan-2016', 'DD-MON-YY'), 'MON'), ROWNUM - 1)

              <= TRUNC(sysdate, 'MON'))

           

          SELECT extract(month from year_month.ym) as Mois, extract(year from year_month.ym) as Annee, nvl(sum(chiffre_mensuel.ventes_par_mois),0) as Montant_total

          FROM   year_month

          LEFT OUTER JOIN (

                 SELECT extract(month from D_CMD) as Mois_vrai, extract(year from D_CMD) as Annee_vrai, sum(MONTANT_L) as ventes_par_mois

                 FROM CMD, DET_CMD

                 WHERE REF_CMD_DET = CMD_ID

                 GROUP BY extract(month from D_CMD), extract(year from D_CMD)

                 ORDER BY extract(year from D_CMD), extract(month from D_CMD)

                 ) chiffre_mensuel

           

                  ON (extract(month from year_month.ym) = Mois_vrai AND extract(year from year_month.ym) = Annee_vrai)

           

          GROUP BY extract(month from year_month.ym), extract(year from year_month.ym)

          ORDER BY Annee, Mois