This discussion is archived
7 Replies Latest reply: May 1, 2013 8:26 AM by 759545 RSS

Struggling with a chart

759545 Newbie
Currently Being Moderated
Sorry if this is long. I am trying to create a "Top 5" chart in an application. My SQL query is:

select null, location, tickets
FROM
(select d.location_name as location, count(*) as tickets
from job_ticket@WHD a,
client@WHD b,
tech@WHD c,
location@WHD d
where a.assigned_tech_id = c.client_id
AND a.CLIENT_ID = b.CLIENT_ID
and b.location_id = d.location_id
and a.report_date > sysdate - 7
group by d.location_name
order by tickets desc)
where rownum < 6

The resulting data:

NULL     LOCATION     TICKETS
-     Data Services     13
-     IT     8
-     Finance     8
-     Maintenance     7
-     Helpdesk - Staff     6


If I choose a Flash chart I receive "XML Parser failure: unterminated attribute", for an HTML5 chart just a blank page. However, if I take the result data and do something like this the chart works:

select null,'Data Services',13
from dual
union all
select null,'IT',8
from dual
union all
select null,'Finance',7
from dual
union all
select null,'Maintenance',6
from dual
union all
select null,'Helpdesk - Staff',6
from dual

I even tried to create a table from the the query and still experience the same issue. Any thoughts?
  • 1. Re: Struggling with a chart
    pjflynn Newbie
    Currently Being Moderated
    I have done something similar, hope it helps...

    I have a stats page which builds its chart dynamically, date ranges can be entered as parameters along with All Offices or an individual office.

    The source of the chart is a pl/sql package function returning VARCHAR2

    i.e.
    Series Name - Series 1
    Query - RETURN my_stats_pkg.my_chart_sql;

    You then build the SQL in the PL/SQL, I return something similar to the following

    SELECT NULL
    , wc.wh_date
    , MAX(DECODE(ao.area_office_id,1,s.lpid_count,0)) Ayr ,MAX(DECODE(ao.area_office_id,2,s.lpid_count,0)) series1
    ,MAX(DECODE(ao.area_office_id,3,s.lpid_count,0)) Dumfries ,MAX(DECODE(ao.area_office_id,4,s.lpid_count,0)) series2
    ,MAX(DECODE(ao.area_office_id,5,s.lpid_count,0)) Galashiels ,MAX(DECODE(ao.area_office_id,6,s.lpid_count,0)) series3
    ,MAX(DECODE(ao.area_office_id,7,s.lpid_count,0)) Hamilton ,MAX(DECODE(ao.area_office_id,8,s.lpid_count,0)) series4
    FROM lpis_statistic s
    ,lpis_wh_control wc
    ,lpis_area_office ao
    ,lpis_measurement m
    WHERE s.wh_control_id = wc.wh_control_id
    AND s.area_office_id = ao.area_office_id
    AND s.measurement_id = m.measurement_id
    AND m.measurement_num = 2
    AND wc.wh_day = 1
    GROUP BY wc.wh_date
    ORDER BY wc.wh_date
  • 2. Re: Struggling with a chart
    759545 Newbie
    Currently Being Moderated
    Thanks but no matter how I structure the chart series - SQL Query or Function Returning SQL Query, the error or no results occurs.
  • 3. Re: Struggling with a chart
    759545 Newbie
    Currently Being Moderated
    Interesting find this morning. If I change the query and do 'select SUBSTR(d.location_name,1,1) as location...' a chart shows up. Anything beyond 1 character, I receive the error or a blank chart. I remained stumped.
  • 4. Re: Struggling with a chart
    Hilary Expert
    Currently Being Moderated
    Hi Drago,

    When you run the page with Debug set to "Yes", and click on the "Show XML" link under your chart, what information do you see when you click the link? If there are no issues, generally you should expect to see the complete XML for your chart. However, if an error is reported during the rendering, it should be captured there.

    Would you mind putting a testcase up on apex.oracle.com, and updating this thread with the necessary login credentials, so that I can take a closer look?

    Regards,
    Hilary
  • 5. Re: Struggling with a chart
    759545 Newbie
    Currently Being Moderated
    This is what I see when clicking on "Show XML."

    This page contains the following errors:

    error on line 56 at column 24: Char 0x0 out of allowed range
    Below is a rendering of the page up to the first error.
  • 6. Re: Struggling with a chart
    Hilary Expert
    Currently Being Moderated
    Hi Drago,

    Would you mind placing a testcase on apex.oracle.com, and updating this thread with the login credentials, please?

    Thanks,
    Hilary
  • 7. Re: Struggling with a chart
    759545 Newbie
    Currently Being Moderated
    I solved this. Some special character in the data. I used:

    regexp_replace(d.location_name,'[^'||chr(32)||'-'||chr(126)||']')

    and the chart displays correctly. Your idea to debug and look at XML got me pointed in the right direction. Thanks.

Legend

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