Forum Stats

  • 3,781,578 Users
  • 2,254,530 Discussions
  • 7,879,760 Comments

Discussions

How to fill chart via different queries depending on choice in selection lists

Phil7G
Phil7G Member Posts: 10 Green Ribbon

Hi,


I'm trying to build a page (Before Regions --> Process --> Clear all Items on Page) in my application that gives some general overview on the data via presentation in a line-chart (y-axis --> Values, 0 - 100%, x-axis --> time). The Y-Axis shows the quality of the data computed by a software that writes the values into the DB and sets a timestamp when doing so. In my APEX-Application, I've got three selection lists which are getting filled by different select statements, each one has a seperate table as source (assignments are determined via IDs). Simplified, the hierachy looks like this:

  1. Sector (Display Null Value: Yes, Null Return Value: NULL)
  2. Object (Cascading LOV parent item: Sector, Display Null Value: Yes, Null Return Value: NULL)
  3. Attribute (Cascading LOV parent item: Attribute, Display Null Value: Yes, Null Return Value: NULL)

To give an example, a typical choice via the selection lists would be: Water - Pipe - Length. The line-chart should show the data for the chosen object or attribute. I'm trying to fill the chart via "PL/SQL Function Body returning SQl Query", it gets refreshed by dynamic actions on a button ("Show Data for Selection"). My code looks roughly like this:

------

DECLARE

QUERY varchar2 (1000);

BEGIN

IF V('ATTRIBUTE') IS NULL THEN

QUERY := 'SELECT a,b,c,d FROM table1 WHERE ID = '''||V('OBJECT')||''' group by ... order by ...';

ELSE

QUERY := 'SELECT a,b,TO_NUMBER(NULL) as c,d FROM table2 WHERE ID = '''||V('OBJECT')||''' AND NAME = '''||V('ATTRIBUTE')||'''group by ... order by ... ';

END IF;

RETURN QUERY;

END;

------

When testing in Oracle SQL Developer, the select statements in "QUERY" give me the correct statement to get the data I need. In my APEX-Application, when choosing an object and clicking the button, the chart shows me the data. When choosing an attribute and clicking the button however, nothing happens. Even after a lot of testing and research, I'm not getting what I'm doing wrong here - but due to being an APEX-Noob, I guess it could be quite a lot of stuff ;-)


I would really appreciate any help!


Cheers,


Phil

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,336 Red Diamond
    Accepted Answer

    Please update your forum profile with a recognisable username instead of "User_QBYL5".

    Are the 3 selection lists included in the Page Items to Submit property of the chart region/series source?

    Phil7G

Answers