Home » Developer & Programmer » Forms » Hierarchical Tree (Oracle 10g)
Hierarchical Tree [message #540842] Thu, 26 January 2012 03:09 Go to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
Hi guys, I'm new to oracle forms builder and I want to make an Information System of Subjects and its corresponding sections.

Below is my desired output:
+ Sections
 - Section 1
    - Advanced Algebra
    - Physics
 - Section 2
    - Chemistry
    - Biology
 - Section 3
    - Calculus
    - Geometry


I wanna do it in a hierarchical tree, since I don't know how to do it, I'm asking the experts here to help me.

Your help would greatly be appreciated! Smile God bless Smile

[EDITED by LF: applied PRE tags to preserve formatting]

[Updated on: Fri, 27 January 2012 01:02] by Moderator

Report message to a moderator

Re: Hierarchical Tree [message #540844 is a reply to message #540842] Thu, 26 January 2012 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This should help:
Click on "Search" link above;
Type "tree" in the "Forum search" field;
Choose "Forms" in "Search in Forum" list box;
Click on "Search" button.

Regards
Michel
Re: Hierarchical Tree [message #540859 is a reply to message #540844] Thu, 26 January 2012 06:09 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
thanks, I've already tried the samples there but my problem is the data aren't appearing during run time.


this is the code in my when_new_form_instance trigger
DECLARE
vi_tree item;
vn_tree_rg NUMBER;
BEGIN
vi_tree := FIND_ITEM ('tree_block.subject_tree');
--vn_tree_rg := POPULATE_GROUP ('SUBJ_RG');
ftree.SET_TREE_PROPERTY (vi_tree, ftree.record_group, 'SUBJ_RG');

GO_BLOCK ('tblsubjectsection');
EXECUTE_QUERY;
END;


code for when_tree_node_activated
DECLARE
vi_tree ITEM;
vn_tree_rg NUMBER;
BEGIN
vi_tree := FIND_ITEM('tree_block.subject_tree');
vn_tree_rg := POPULATE_GROUP('SUBJ_RG');
ftree.SET_TREE_PROPERTY(vi_tree, ftree.RECORD_GROUP, 'SUBJ_RG');
END;

code for when_tree_node_selected
DECLARE
vi_htree item;
vc_node_value VARCHAR2 (100);
BEGIN
vi_htree := FIND_ITEM ('TREE_BLOCK.SUBJECT_TREE');
vc_node_value := ftree.GET_TREE_NODE_PROPERTY (vi_htree, :SYSTEM.trigger_node, ftree.node_value);
SET_BLOCK_PROPERTY ('tblsubjectsection', default_where, 'Section = ' ||vc_node_value);
GO_BLOCK ('tblsubjectsection');
EXECUTE_QUERY;
END;
Re: Hierarchical Tree [message #540860 is a reply to message #540842] Thu, 26 January 2012 06:11 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
this is my screenshot i41.tinypic.com/equjrp.jpg
Re: Hierarchical Tree [message #540867 is a reply to message #540860] Thu, 26 January 2012 06:53 Go to previous messageGo to next message
Gogetter
Messages: 34
Registered: December 2009
Location: Cologne Germany
Member
Can you provide the select for the record group and the table structure?
Re: Hierarchical Tree [message #540879 is a reply to message #540867] Thu, 26 January 2012 08:15 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
SELECT 1, LEVEL, Section, NULL, Subject_Code
FROM tblSubjectSection
CONNECT BY PRIOR ID = Subject_Code
START WITH Subject_Code IS NULL


CREATE TABLE TBLSUBJECTSECTION(ID number(1) PRIMARY KEY, Section varchar2(10) NOT NULL, Subject_Code varchar2(10) NOT NULL, Day);
Re: Hierarchical Tree [message #540880 is a reply to message #540879] Thu, 26 January 2012 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
This:
START WITH Subject_Code IS NULL

contradicts this:
Subject_Code varchar2(10) NOT NULL


Maybe you should test if your query returns anything in sqlplus before trying to use it in forms.
Re: Hierarchical Tree [message #540883 is a reply to message #540880] Thu, 26 January 2012 08:29 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
okay! thanks I will try it again
Re: Hierarchical Tree [message #540884 is a reply to message #540883] Thu, 26 January 2012 08:31 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
so what will i do with
START WITH Subject_Code IS NULL
? am i to change to NOT NULL?
Re: Hierarchical Tree [message #540885 is a reply to message #540884] Thu, 26 January 2012 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well Changing the NULL to NOT NULL will tell it to start with every row won't it?

But I can't tell you what it should be as I don't know anything about your data.
How could we know which row you need to start with unless you tell us?
Re: Hierarchical Tree [message #540886 is a reply to message #540884] Thu, 26 January 2012 08:39 Go to previous messageGo to next message
Gogetter
Messages: 34
Registered: December 2009
Location: Cologne Germany
Member
You need a root entry.
In this case, I think you can change it to NOT NULL and your root entry (Sections) must be updated no null. But this should be the only one with null values. Or start with ID = 1 (?)
But it belongs on your data you have and will store in future.


Re: Hierarchical Tree [message #540888 is a reply to message #540886] Thu, 26 January 2012 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
Gogetter wrote on Thu, 26 January 2012 14:39
I think you can change it to NOT NULL and your root entry (Sections) must be updated no null. But this should be the only one with null values.

I'm confused. Make what not null? What null values?
Re: Hierarchical Tree [message #540892 is a reply to message #540888] Thu, 26 January 2012 08:57 Go to previous messageGo to next message
Gogetter
Messages: 34
Registered: December 2009
Location: Cologne Germany
Member
I meant
1.Change the NULL table column "Subject_Code" to NOT NULL
2.Update your_table SET Subject_Code = NULL WHERE Section = 'Sections'

But it only works when this is the only one row with null value. In this case better use the primary key for the start-clause.

Re: Hierarchical Tree [message #540893 is a reply to message #540892] Thu, 26 January 2012 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
Gogetter wrote on Thu, 26 January 2012 14:57

1.Change the NULL table column "Subject_Code" to NOT NULL

You mean change the NOT NULL table column "Subject_Code" to NULL
Re: Hierarchical Tree [message #540894 is a reply to message #540893] Thu, 26 January 2012 09:13 Go to previous messageGo to next message
Gogetter
Messages: 34
Registered: December 2009
Location: Cologne Germany
Member
Upps, sorry, really ashamed Embarassed
Re: Hierarchical Tree [message #540895 is a reply to message #540893] Thu, 26 January 2012 09:13 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
Just to let you know, I have 3 composite keys, ID, Section, and Subject_Code(which is already NOT NULL).

I want my data to be displayed as this:
+ SECTIONS
  - SECTION 1
    - MATH
    - CHEMISTRY
  - SECTION 2
    - PHYSICS
    - CALCULUS

MY root node is SECTIONS. Then section1 is one of my parent node. My child nodes are the subjects under section1


[EDITED by LF: applied PRE tags to preserve formatting]

[Updated on: Fri, 27 January 2012 01:01] by Moderator

Report message to a moderator

Re: Hierarchical Tree [message #540897 is a reply to message #540895] Thu, 26 January 2012 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
saychiz13 wrote on Thu, 26 January 2012 15:13
I have 3 composite keys, ID, Section, and Subject_Code

That makes no sense. A composite key is a key that has more than one column in it. How can you have 3 composite keys with 3 columns?
Even if you mean 1 composite key of 3 columns that contradicts the create table statement you posted above.

saychiz13 wrote on Thu, 26 January 2012 15:13

MY root node is SECTIONS.

So why not use SECTIONS in the start with clause?
Re: Hierarchical Tree [message #540900 is a reply to message #540897] Thu, 26 January 2012 09:26 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
thanks for the correction mate, hmm so i already changed the start with clause to Sections but still data are nowhere to be found.
Re: Hierarchical Tree [message #540905 is a reply to message #540900] Thu, 26 January 2012 09:30 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about you post the revised query along with some insert statements so we can recreate your data. Then we might be able to spot the problem.
Re: Hierarchical Tree [message #540914 is a reply to message #540905] Thu, 26 January 2012 09:50 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
SELECT 1, LEVEL, Subject_Code, NULL, to_Char(ID)
FROM tblSubjectSection
CONNECT BY PRIOR ID = Subject_Code
START WITH Section IS NULL
Re: Hierarchical Tree [message #540916 is a reply to message #540914] Thu, 26 January 2012 09:51 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
you said i should change start with subject_code to section
Re: Hierarchical Tree [message #540917 is a reply to message #540916] Thu, 26 January 2012 09:55 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suggested you change it to something that actually identifies the root node of SECTIONS.
I suspect that means:
START WITH Subject_Code = 'SECTIONS'
Re: Hierarchical Tree [message #540921 is a reply to message #540917] Thu, 26 January 2012 10:06 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
i changed it already
Re: Hierarchical Tree [message #540922 is a reply to message #540921] Thu, 26 January 2012 10:09 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
And?
Re: Hierarchical Tree [message #540928 is a reply to message #540922] Thu, 26 January 2012 10:25 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
it doesn't appear Sad
Re: Hierarchical Tree [message #540930 is a reply to message #540905] Thu, 26 January 2012 10:35 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sigh.
As previously stated:

cookiemonster wrote on Thu, 26 January 2012 14:37

But I can't tell you what it should be as I don't know anything about your data.
How could we know which row you need to start with unless you tell us?


Guessing games have got us nowhere and I refuse to play anymore. So if you want an answer:

cookiemonster wrote on Thu, 26 January 2012 15:30
How about you post the revised query along with some insert statements so we can recreate your data. Then we might be able to spot the problem.


If you'd done that when originally asked, or better yet at the start of the thread, we would have sorted this out by now.
Re: Hierarchical Tree [message #540933 is a reply to message #540930] Thu, 26 January 2012 10:38 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
I have spotted another obvious flaw:
CONNECT BY PRIOR ID = Subject_Code

ID is a number, subject_code is a varchar, so how, exactly, are they ever going to be equal?

Do you actually have hierarchical data?
Re: Hierarchical Tree [message #540934 is a reply to message #540928] Thu, 26 January 2012 10:41 Go to previous messageGo to next message
Gogetter
Messages: 34
Registered: December 2009
Location: Cologne Germany
Member
I tried this
CREATE TABLE tblSubjectSection
  (ID NUMBER PRIMARY KEY,
   SECTION VARCHAR2(50),
   Subject_Code NUMBER);

INSERT INTO tblSubjectSection VALUES (1,'SECTIONS',NULL);  
INSERT INTO tblSubjectSection VALUES (2,'SECTION 1',1); 
INSERT INTO tblSubjectSection VALUES (3,'MATH',2); 
INSERT INTO tblSubjectSection VALUES (4,'CHEMISTRY',2); 
INSERT INTO tblSubjectSection VALUES (5,'SECTION 2',1); 
INSERT INTO tblSubjectSection VALUES (6,'PHYSICS',5); 
INSERT INTO tblSubjectSection VALUES (7,'CALCULUS',5);
COMMIT;

SELECT SUBSTR(LPAD(' ',LEVEL,'-')||SECTION,1,20) TREE
FROM tblSubjectSection a
CONNECT BY PRIOR ID =  Subject_Code
START WITH ID = 1


And that is what i get:
TREE
--------------------
SECTIONS
- SECTION 1
-- MATH
-- CHEMISTRY
- SECTION 2
-- PHYSICS
-- CALCULUS

[Updated on: Thu, 26 January 2012 10:42]

Report message to a moderator

Re: Hierarchical Tree [message #540936 is a reply to message #540934] Thu, 26 January 2012 10:47 Go to previous message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
Fairly safe bet the OPs data doesn't look like that, so why don't we wait for him to tell us what he's really got.
Previous Topic: When-Timer-Expired
Next Topic: Current user(database items) (merged 2)
Goto Forum:
  


Current Time: Fri Aug 22 18:45:18 CDT 2014

Total time taken to generate the page: 0.08528 seconds