3 Replies Latest reply: Dec 3, 2012 8:28 AM by 714270 RSS

    APEX Application Express Pie chart

    977596
      Trying to get a Pie Chart working in Application Express, I have one working as part of a report that I can turn into a pie chart through the search bar functions but can't seem to get it to work as a standalone pie chart.

      The SQL for the report:

      SELECT COUNT(DISTINCT record_number),
      'ACCEPTED' AS status
      FROM students
      WHERE record_number IN
      (SELECT DISTINCT a.record_number
      FROM applications a
      JOIN application_history h
      ON h.application_id = a.application_id
      WHERE h.status_id = 7)
      UNION
      SELECT COUNT(DISTINCT record_number), 'NOT ACCEPTED' AS status
      FROM students
      WHERE record_number IN
      (SELECT a.record_number
      FROM applications a
      JOIN application_history h
      ON h.application_id = a.application_id
      WHERE h.status_id != 7
      MINUS
      SELECT DISTINCT a.record_number
      FROM applications a
      JOIN application_history h
      ON h.application_id = a.application_id
      WHERE h.status_id = 7);

      And here's the SQL code that I'm TRYING to get to work:

      SELECT
      LINK null,
      LABEL status,
      VALUE COUNT(DISTINCT record_number)
      'ACCEPTED' AS status

      FROM students

      WHERE record_number IN
      (SELECT DISTINCT a.record_number
      FROM applications a
      JOIN application_history h
      ON h.application_id = a.application_id
      WHERE h.status_id = 7)
      UNION
      SELECT COUNT(DISTINCT record_number), 'NOT ACCEPTED' AS status
      FROM students
      WHERE record_number IN
      (SELECT a.record_number
      FROM applications a
      JOIN application_history h
      ON h.application_id = a.application_id
      WHERE h.status_id != 7
      MINUS
      SELECT DISTINCT a.record_number
      FROM applications a
      JOIN application_history h
      ON h.application_id = a.application_id
      WHERE h.status_id = 7);

      Keeps on throwing up "Failed to parse SQL query!"

      Any help/ideas would be much appreciated.

      Cheers

      Edited by: 974593 on 03-Dec-2012 04:08
        • 1. Re: APEX Application Express Pie chart
          714270
          When using set operators UNION, UNION ALL, INTERSECT, and MINUS, make sure that each SQL SELECT statement within the query must have the same number of fields in the result sets with similar data types. So, you need to modify your individual select statements to include all the columns that you are selecting in your first select - Link, Label, Value and Status.
          • 2. Re: APEX Application Express Pie chart
            977596
            So something like this? Still throwing up the same error :/

            SELECT
            LINK null,
            LABEL status,
            VALUE COUNT(DISTINCT record_number)
            'ACCEPTED' AS status

            FROM students

            WHERE record_number IN
            (SELECT
            LINK null,
            LABEL status,
            VALUE COUNT(DISTINCT applications.record_number)
            'ACCEPTED' AS status
            FROM applications a
            JOIN application_history h
            ON h.application_id = a.application_id
            WHERE h.status_id = 7);
            • 3. Re: APEX Application Express Pie chart
              714270
              The syntax of your query is wrong. First of all, there is no comma between
              VALUE COUNT(DISTINCT record_number) 
              'ACCEPTED' AS status
              Secondly, the select statement should be:
              select
              null as link,
              status as label,
              record_number as value
              from
              students
              where
              <your condition>
              The syntax is defined here:

              http://docs.oracle.com/cd/E18283_01/appdev.112/e11945/bar_chart.htm


              Moreover, you cannot directly use an aggregate function like count in the select statement unless you have a group by clause:
              http://ora-00937.ora-code.com/

              SO, using something like
              COUNT(DISTINCT record_number)
              is invalid unless you use a group by clause (that includes the status column).

              If you are in Apex 4.2, you can install the "Sample Charts" packaged application. This application has a number of examples illustrating different types of charts.