This discussion is archived
3 Replies Latest reply: Dec 3, 2012 6:28 AM by 714270 RSS

APEX Application Express Pie chart

977596 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points