1 Reply Latest reply: Oct 15, 2013 4:08 PM by Mike Kutz RSS

    Dynamic Query Data issue...Apex Charts

    bostonmacosx

      Database version and APEX version are in the tags...11g  and4.1.2

       

        I have data in this format

       

      NODE_NAMEREPORT_DATESTORAGE_AMOUNT_ALLOC
      Anole01/01/20133175
      Fiji01/01/20130
      Anole02/01/20133175
      Fiji02/01/20130
      Anole04/01/20133276
      Fiji04/01/20130
      Anole05/01/20133276
      Fiji05/01/20130
      Anole06/01/20133276
      Fiji06/01/20130

      I've build a Dynamic query which returns:

       

      SELECT NULL LINK,report_date DATIME,decode(NODE_NAME,'Anole',STORAGE_AMOUNT_ALLOC)"Anole",decode(NODE_NAME,'Fiji',STORAGE_AMOUNT_ALLOC)"Fiji" FROM STORAGE_REPORTS WHERE report_date between to_date('01-JAN-2013','DD-MON-YYYY') and to_date('01-JUN-2013','DD-MON-YYYY') and (INSTR(':Anole:Fiji:', ':'||NODE_NAME||':') > 0) ORDER BY report_date ASC

       

      The resultant data is

       

      LINKDATIMEAnoleFiji
      -01/01/20133175-
      -01/01/2013-0
      -02/01/20133175-
      -02/01/2013-0
      -04/01/20133276-
      -04/01/2013-0
      -05/01/2013-0
      -05/01/20133276-
      -06/01/20133276-
      -06/01/2013-0

       

      When I put the above query to the chart it give me accurate series with one..however when I add a second NODE to the query it completly hacks the chart. I have a feeling this is because I have repeating dates and it doesn' know how to handle this. So I'm wondering how I get rid of the repeating dates. and make them all one row with columns for each series.

       

      Thanks

      Rob

        • 1. Re: Dynamic Query Data issue...Apex Charts
          Mike Kutz

          apex version?  database version?

           

          if you're on 11g, it sounds like you want the PIVOT command.

          you should also post a simple CREATE TABLE and a few INSERT statements so others can test.

           

          select null link

            ,report_date as datime

            ,"Anole", "Fiji"

          from ( select REPORT_DATE, NODE_NAME, STORAGE_AMOUNT_ALLOC

            from STORAGE_REPORTS

          )

          PIVOT (

            sum( storage_amount_alloc )

            for (node_name)

            in ( 'Anole' "Anole", 'Fiji' "Fiji" ) -- rename columns here

          )

          where report_date between to_date('01-JAN-2013','DD-MON-YYYY') and to_date('01-JUN-2013','DD-MON-YYYY')

          order by report_date asc