ORDS, SODA & JSON in the Database

Hi Don,

Creating trees in Apex is relatively straightforward. When you create a page, you can create a Tree region on that page. The SQL statement you use is a simplified version of the statement you've shown as Apex handles the rest for you.

If you create a Tree region, the statement should only be something like:

select "EMPNO" id, 
       "MGR" pid, 
       "ENAME" name, 
       null link, 
       null a1, 
       null a2 
from "#OWNER#"."EMP"

As long as you have those column aliases, Apex will create the tree based on the id/pid connections.

Andy

Hello Andy,



Yes, I understand that. And based on my data, I would get the following query:



select toc_seq_id id, <-- the sequence this entry should fall in under its Parent entry. Parent's toc_seq_id = their own ID. This would equate to the EMPNO column in your sample.

toc_parent pid, <-- what is the parent entry for this record. This would equate to the MGR column in your sample.

toc_desc name, <-- The description for this entry. This would equate to the ENAME in your sample.

null link,

null a1,

null a2

from toc;



The problem is - I only get 1 parent and the children that should be under that parent. Nothing I do seems to allow me to get all parent entries and their associated children. The parent entry I get is whatever value I put into the P4_TREE_ROOT hidden item.



What I would like is to get all parent entries and their children under the correct parent. I have managed to get all parent entries, but they all have the <u>same </u>children entries.



What next?



Thanks,

Don.

Hi Don,

I think that this thread may be what you need: 724206

Andy

Andy,



I tried that and got absolutely nowhere. When I run this query in SQL Developer, I get the correct answer. When I paste it into my APEX Tree query, I do NOT get the correct answer.



<p>
select toc_level id,
</p>
<p>
toc_parent pid,
</p>
<p>
toc_description name,
</p>
<p>
toc_description link,
</p>
<p>
toc_seq_id a1,
</p>
<p>
null a2
</p>
<p>
from toc where state_id = 'ZZ';



I am at a loss as to where to go next. I've thought about trying to restructure the data, but I wouldn't know what APEX needs to get things to come out right.



Maybe someone has an idea.



Thanks,

Don
</p>

Hi Don,

The value for the hidden Pn_TREE_ROOT should be the id of the topmost item in your tree. If you have more than one that could be topmost, then you have to create a dummy root - this was described in the linked thread - and the ID of the dummy root should be used.

So, in your example, if more than one toc_parent entry can contain null, you have more than one root so must create a dummy root. If only one toc_parent entry contains null, then the ID of this record must be specified as the root.

The only thing you may have to do from the linked example, would be to convert the nulls for your actual pid values into 1 to get them to link to the new dummy parent.

So:

select 1 id, null pid, 'Rootname' name, null link, null a1, null a2 from link
union all
select 
toc_level+1000000 id, 
case when toc_parent is null then 1 else toc_parent+1000000 end pid, 
toc_description name, 
null link, 
null a1,
null a2
from toc where state_id = 'ZZ';

This creates a dummy root with an id of 1 and attaches all of your actual parents to this root. The actual ID values have to be increased to ensure that none of them could be a 1 and become attached to the root.

Andy

Andy,





I'm getting there. I may have to restructure my data some, but disecting this one select statement at a time has gotten me very close. I have a ROOTLEVEL = 1 and I have a child level subbordinate to that with a value of 0 and one with a value of 1. And I think that is messing up the results somewhat. HOWEVER, it is close.





Thanks, I should be able to get this worked out at this point.





Thanks,

Don.

Hi Don,

OK - One thing you need to be aware of is that Apex actually treats nulls as zeros in PID values in trees. So, if any id value could be 0, you would have to change it. This is actually the reason why the dummy root had an ID of 1. You can't use negative numbers either.

Andy

Andy,



Yeay, I found that out the hard way. But what else I found out, and perhaps this might help others, is that you can use alpha characters. Since

all of my data was numeric, I chose 'A' for the dummy level at the top. You have to convert all the numeric values to_char and I'm not a fan of that.

But I have too much data to try and restructure with the short deadline that I have for the application.



That will be 'Phase 2' I guess. In any case, by assigning that dummy top level a value that I knew would NOT occur in my data, I was able to get

the tree to display. It was, in fact, the confusion of values in my existing data that was causing most of my problems in the first place.



Thank you very much for your help.





Don

Hi Don,

Thanks for the feedback. I tend to only use numerical IDs for my records (so, perhaps, only think in those terms!) but, you're right, the aim is to get a ID for the dummy root that can't exist in the actual data. If characters work, then that should be ok - the only potential drawback, perhaps, would be if you wanted to sort by ID as it's now a string so would be sorted left-to-right - but mostly you'd sort by the name field anyway, so that shouldn't be a problem.

Andy