Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to open a tree to a specific node? (Apex 19.2)

John like many othersOct 26 2020 — edited Oct 26 2020

Hello
I would like to open a tree to a specific hierarchical position after it was refresh (comes in closed state).
Based on the tree widget API I assume I have to open all nodes above the target. I can get all the NodeIDs before I would refresh the treeView: getExpandedNodeIds() but there are two problems with that:
This function returns the Node IDs not in a sorted way (from root to target node)
The Node ID is not stored in the HTML structure, so can't be referenced that way
The nicest/easiest way would be to hand over a jQuery object or a DOM object that defines the target node to which the tree should be expanded. Is something like this available and I didn't see it? If not, is there any other way to get the tree expanded to a specific node?

Comments

Mike Kutz

No procedure needed.

A simply hierarchic SQL using the CONNECT BY clause should be suffice.

with my_table_name( a_number, a_name, a_group, primary_number ) as (

  select 1, 'TEST 1', 'ABC', null from dual union all

  select 2, 'TEST 2', 'ABC', 1 from dual union all

  select 3, 'TEST 3', 'ABC', 1 from dual union all

  select 4, 'TEST 4', 'ABC', 2 from dual union all

  select 5, 'TEST 5', 'ABC', 2 from dual union all

  select 6, 'TEST 6', 'ABC', 3 from dual union all 

  select 7, 'TEST 7', 'ABC', 4 from dual union all 

  select 8, 'TEST 8', 'ABC', 5 from dual

)

select * from my_table_name

connect by prior a_number = primary_number

start with a_number = 2

order by a_number;

kattavijay-JavaNet

Thanks for your response mike ,

how can i execute if it is dynamic table with random data !!

unknown-7404

Don't be silly - random data doesn't have a pattern.


PhHein

Moved from General questions

BluShadow

katta vijay wrote:

Thanks for your response mike ,

how can i execute if it is dynamic table with random data !!

As already mentioned, random data doesn't have a tree structure so such a question is pointless to ask.

Also, a properly designed application and database doesn't have "dynamic" table names.  You should know the tables you're accessing and the structure of those tables.  The moment you start thinking "how can I do this dynamically?" you should stop yourself and ask "what's wrong with the design?"

Mike already provided the answer for how to traverse hierarchical data in a table.  That answers your question, and there's no need for any PL code to do it, as SQL is perfectly capable of processing it.

Another method if you're on 11gR2 upwards, is to use recursive subquery factoring, for example:

SQL> ed
Wrote file afiedt.buf

  1  with my_table_name(a_number, a_name, a_group, primary_number) as
  2                    (select 1, 'TEST 1', 'ABC', null from dual union all
  3                     select 2, 'TEST 2', 'ABC', 1 from dual union all
  4                     select 3, 'TEST 3', 'ABC', 1 from dual union all
  5                     select 4, 'TEST 4', 'ABC', 2 from dual union all
  6                     select 5, 'TEST 5', 'ABC', 2 from dual union all
  7                     select 6, 'TEST 6', 'ABC', 3 from dual union all
  8                     select 7, 'TEST 7', 'ABC', 4 from dual union all
  9                     select 8, 'TEST 8', 'ABC', 5 from dual
10                    )
11  --
12  -- end of test data
13  --
14      ,r as (select &starting_num as start_num from dual)
15      ,rec(n) as
16            (select a_number as n
17             from my_table_name, r
18             where primary_number = r.start_num
19             union all
20             select a_number as n
21             from my_table_name, rec
22             where primary_number = rec.n
23            )
24  select *
25* from   rec
SQL> /
Enter value for starting_num: 2
old  14:     ,r as (select &starting_num as start_num from dual)
new  14:     ,r as (select 2 as start_num from dual)

         N
----------
         4
         5
         7
         8

Which is using recursion techniques rather than hierarchical techniquest to achieve the same.

1 - 5

Post Details

Added on Oct 26 2020
4 comments
574 views