Chart won't show up if query is given where condition

[rst30]

Hello, i've been trying this by using the default query in the sample app, and it works. I then modified the query to grab data from my own tables, and it works as well. However, when i put a where condition in the query, the chart will show up blank. Here is the code :

WITH tasks AS ( --> START YOUR TASKS QUERY HERE
    SELECT
        XMLELEMENT(
            "task",
            XMLATTRIBUTES(
                ACTIVITY AS "id",
                ACTIVITY AS "text",
                TO_CHAR(START_DATE,'yyyy-mm-dd') AS "start_date",
                TO_CHAR(NVL(PROGRESS_QTY_PCT,0)/100,'TM9','nls_numeric_characters=''.,''') AS "progress",
                TO_CHAR((trunc(DUE_DATE) - trunc(START_DATE))+1,'TM9','nls_numeric_characters=''.,''') AS "duration",
                PARENT AS "parent",
                -- For the visualization, if child tasks should be expanded(shown) or not:
                'true' AS "open",
                -- If you provide here a URL, then this URL is automatically opened by the plugin when a task is double clicked.
                -- This saves you time during development and also extra AJAX calls to the server to prepare the url
                -- in a dynamic action. The triggering element is set her to #my_gantt which is the static id of the
                -- gantt chart region. You get then on this region the event "Dialog Closed". With this event you can
                -- refresh the gantt chart with a dynamic action:
                apex_util.prepare_url(
                    p_url => 'f?p=' || :app_id || ':10920102:' || :app_session || ':::2:P10920102_ROWID:' || rowid,
                    p_triggering_element => 'apex.jQuery("#my_gantt")'
                ) AS "url_edit",
                -- The url to call when the user click a plus sign to create a child task (our task id is here the parent):
                apex_util.prepare_url(
                    p_url => 'f?p=' || :app_id || ':10920102:' || :app_session || ':::2:P10920102_PARENT,P10920102_PROJECT_CODE:' || ACTIVITY||','||PROJECT_CODE,
                    p_triggering_element => 'apex.jQuery("#my_gantt")'
                ) AS "url_create_child"
            )
        ) AS task_xml
    FROM
        PM_PROJECT_SCHEDULE --< STOP YOUR TASKS QUERY HERE
    where PROJECT_CODE = :P1092010_PROJECT_CODE
), links AS ( --> START YOUR LINKS QUERY HERE
    SELECT
        XMLELEMENT(
            "link",
            XMLATTRIBUTES(
                ACTIVITY AS "id",
                PARENT AS "source",
                ACTIVITY AS "target",
                1 AS "type", --kalau valuenya 0 bentuknya beda, kyk ujung ke awalnya lagi
                --apex_util.prepare_url(
                --    p_url => 'f?p=' || :app_id || ':3:' || :app_session || ':::3:P3_L_ID:' || l_id,
                --    p_triggering_element => 'apex.jQuery("#my_gantt")'
                --) AS "url_edit"
				null "url_edit"
            )
        ) AS link_xml
    FROM
        PM_PROJECT_SCHEDULE --< STOP YOUR LINKS QUERY HERE
    where PROJECT_CODE = :P1092010_PROJECT_CODE
), holidays AS ( --> START YOUR HOLIDAYS QUERY HERE
    SELECT
        XMLELEMENT(
            "holiday",
            XMLATTRIBUTES(
                to_char(h_date, 'yyyy-mm-dd') AS "date"
            )
        ) AS holiday_xml
    FROM
        plugin_gantt_demo_holidays --< STOP YOUR HOLIDAYS QUERY HERE
	WHERE 1=2
), special_urls AS ( --> START SPECIAL URL's (optional)
    SELECT
        XMLELEMENT(
            "task_create_url_no_child",
            XMLATTRIBUTES(
                -- The url to call when the user click the first plus sign in the chart to
                -- create a new task (no child, because without parent id):
                apex_util.prepare_url(
                    p_url => 'f?p=' || :app_id || ':10920102:' || :app_session || ':::10920102:P10920102_PROJECT_CODE:'||:P1092010_PROJECT_CODE,
                    p_triggering_element => 'apex.jQuery("#my_gantt")'
                ) AS "url"
            )
        ) AS special_url_xml
    FROM
        dual --< STOP SPECIAL URL's
) SELECT
    XMLSERIALIZE(DOCUMENT(
        XMLELEMENT(
            "data",
            (SELECT XMLAGG(task_xml) FROM tasks),
            (SELECT XMLAGG(link_xml) FROM links)
            --(SELECT XMLAGG(holiday_xml) FROM holidays),
            --(SELECT XMLAGG(special_url_xml) FROM special_urls)
        )
    ) INDENT) AS single_clob_result
FROM
    dual;

Adding the line

where PROJECT_CODE = :P1092010_PROJECT_CODE

for the task and link xml elements would make the chart go blank. If both lines are removed, it will show up as normal. I have tested the queries in oracle apex's SQL Commands, and it returns data normally. Please help in resolving this problem.
Thank you in advance.
Ristyo

[ogobrecht]

Hi Ristyo,

can you please have a look at your browser console if you see some JavaScript errors?

Also, please provide me the XML data which your query seems to deliver successful. I can try then to reproduce the problem in my development environment without the need to setup your data model.

Best regards
Ottmar

[rst30]

Thank you, i have resolved the issue by changing the where conditions.
Before, i used something like
WHERE COL1 = :P_PAGE_ITEM
Now, i used
WHERE COL1 = :APPLICATION_ITEM
The application item is filled by the page item value on page load.

Regarding your answer, i am not currently accessing said program, however

1. If i remember correctly there were no javascript errors, the region just didnt show up
2. I compared the XML data between the results using the where condition and without. It was the exact same (because the where condition happens to apply to the entire data in the table).

[ogobrecht]

Hi Ristyo,

I don't understand how moving from an page item to an application item solved your problem. Anyway, thank you for your feedback and nice that you found a solution for the problem. I will close this issue.

Best regards
Ottmar