| Hierarchical Tree [message #540842] |
Thu, 26 January 2012 03:09  |
 |
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!  God bless
[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 #540859 is a reply to message #540844] |
Thu, 26 January 2012 06:09  |
|
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 #540880 is a reply to message #540879] |
Thu, 26 January 2012 08:19 |
cookiemonster
Messages: 13963
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 #540885 is a reply to message #540884] |
Thu, 26 January 2012 08:37 |
cookiemonster
Messages: 13963
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 |
Gogetter
Messages: 39
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 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Gogetter wrote on Thu, 26 January 2012 14:39I 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 |
Gogetter
Messages: 39
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 |
cookiemonster
Messages: 13963
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 #540895 is a reply to message #540893] |
Thu, 26 January 2012 09:13 |
|
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 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
saychiz13 wrote on Thu, 26 January 2012 15:13I 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 #540905 is a reply to message #540900] |
Thu, 26 January 2012 09:30 |
cookiemonster
Messages: 13963
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 #540917 is a reply to message #540916] |
Thu, 26 January 2012 09:55 |
cookiemonster
Messages: 13963
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 #540930 is a reply to message #540905] |
Thu, 26 January 2012 10:35 |
cookiemonster
Messages: 13963
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:30How 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 |
cookiemonster
Messages: 13963
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 |
Gogetter
Messages: 39
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
