APEX

When I have more than 2 levels in my tree, I only see the root node. The query works fine and returns 132 rows when I run it in SQL. It is just the tree that does not work.

The same tree when built in 3.2 works fine.

This is my tree query:

select case when connect_by_isleaf = 1 then 0
when level = 1 then 1
else -1
end as status,
level,
"NAME" as title,
null as icon,
"ID" as value,
null as tooltip,
null as link
from (
SELECT '__TOP_NODE__' id
, NULL pid
, 'BI Applications' name
, '' link_id
, null a1
, null a2
FROM dual
/*
1 b) BIA Modules
/**/
UNION ALL
SELECT '__MD_'||module_key id
, '__TOP_NODE__' pid
, module_name name
, module_key link_id
, '__MD__' a1
, null a2
FROM c_bia_module
/*
Level = 2
Parent = Module
Node = Functional Areas
ID = '__FA_' prefixing functional area code
/**/
UNION ALL
SELECT '__FA_'||funcarea_key id
, '__MD_'||module_key pid
, funcarea_name name
, funcarea_key link_id
, '__FA__' a1
, null a2
FROM c_functional_area
/*
Level = 3
Parent = Functional Area
Node = Fact Group
ID = '__FG_' prefixing fact group key
/**/
UNION ALL
SELECT '__FG_'||factgroup_key id
, '__FA_'||funcarea_key pid
, factgroup_name name
, factgroup_key link_id
, '__FG__' a1
, null a2
FROM c_fact_group
)
start with "PID" is null
connect by prior "ID" = "PID"
order siblings by "NAME"

If I reduce the query to just use two levels like:

SELECT '__TOP_NODE__' id
, NULL pid
, 'BI Applications' name
, '' link_id
, null a1
, null a2
FROM dual
/*
1 b) BIA Modules
/**/
UNION ALL
SELECT '__MD_'||module_key id
, '__TOP_NODE__' pid
, module_name name
, module_key link_id
, '__MD__' a1
, null a2
FROM c_bia_module

It works fine. Can somebody let me know what I am doing wrong? Is there a way to set the number of levels supported in 4.0?