SQL & PL/SQL :: XML Query For Tree
Sep 20, 2013
I need to form XML for hirarchy data,
CREATE TABLE TESTING (
EMPID VARCHAR2(1),
ENAME VARCHAR2(10),
CHILD_ID NUMBER,
PARENT_ID NUMBER);
INSERT INTO TESTING VALUES ('A', 'AText', 1001,0);
INSERT INTO TESTING VALUES ('B', 'BText', 1002,1001);
INSERT INTO TESTING VALUES ('C', 'CText', 1003,1002);
insert into testing values ('D', 'DText', 1004,1003);
SELECT LEVEL, EMPID,ENAME,CHILD_ID, PARENT_ID
FROM TESTING
START WITH PARENT_ID = 0
CONNECT BY NOCYCLE PRIOR CHILD_ID = PARENT_ID;
Need select query to return output like below
<ITEM ID="A" TEXT="AText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="B" TEXT="BText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="C" TEXT="CText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="D" TEXT="DText" IM0="folderClosed.gif" CHILD="1">
</ITEM>
</ITEM>
</ITEM>
</ITEM>
View 1 Replies
ADVERTISEMENT
Sep 8, 2012
Explain the following query to me:
in particular Lpad (' ', Level-1)*3.
select lpad(' ' , (level-1)*3) || emp_last_name employee
from emp
connect by supervisor_emp_id = prior emp_id
start with supervisor_emp_id is null;
View 1 Replies
View Related
Jun 26, 2012
How to create one complex query. here what I have in my table.
DELIVERABLESPRECURSORS
------------ -----------
4 15
15 332
15 26
26 332
29 4
29 15
224 26
224 274
224 259
259 29
274 15
288 259
288 26
288 274
916 27
917 27
918 27
I need a query which run against this table and bring me the following result.
4 > 15 > 332
15 > 332
15 > 26 > 332
26 > 332
29 > 4 > 15 > 332
..... and so on
In theory, I need query which will track DELIVERABLES'S PRECURSOR and PRECURSOR's of there PRECURSOR'S.
View 16 Replies
View Related
Apr 9, 2010
I was executing following query and it was taking about 20 sec's to execute before i decided to create B-Tree(Normal) index on column DeliVery.
Select
DeliVery,Code,Sum(Units),Sum(Loads),Count(units),
Count(Loads)
Where
[code]...
After creating B-Tree index on Column Delivery the execution time has been reduced to one second ,thats what i wanted. But If i create Bitmap index on the same column then execution time is not getting reduced and is still same around 20 sec.I think theoratically "Delivery" column is the right candidate for Bitmap index? whether should i create bitmap index or stick with B-Tree index?
View 14 Replies
View Related
Dec 24, 2012
Hierarchical Tree in oracle form 6i .i am trying to build tree in form 6i but it give error
SELECT 1,
level,
job
,
'',
ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
order by level
i want to show job wise employees ...
View 4 Replies
View Related
Nov 25, 2012
My table structure is like this
CREATE TABLE Tab1(LVL1_NM VARCHAR2(20), LVL2_NM VARCHAR2(20), LVL3_NM VARCHAR2(20), LVL4_NM VARCHAR2(20))
and the values will be for one value of LVL1_NM there will be different LVL2_NM values like wise for each and every value of LVL2_NM column there will different values in LVL3_NM column like wise for LVL4_NM is there any way to get the results as per their relation ships.
View 7 Replies
View Related
Oct 4, 2012
I have a hierarchy tree i query using connect by. The primary key is the up level to other parts. I need to copy the entire tree structure as a new structure.
This will mean renaming the IDs to new unique ID yet keeping the up levels correct.
i.e.
ID DESC UPLEVEL
1 TOP
2 desc2 1
3 desc3 1
4 desc4 3
5 desc5 4
6 desc6 1so if i coped this i would expect
7 TOP
8 desc2 7
9 desc3 7
10 desc4 9
11 desc5 10
12 desc6 7
View 5 Replies
View Related
Apr 6, 2013
---------------------------script of table for tree--------------
create table eqpt_mast
(
EQPT_ID VARCHAR2(16),
EQPT_CODE VARCHAR2(9),
PARENT_CODE VARCHAR2(9),
[code]....
output of tree like:-
grandf(0)
|__
---father1(1)
[code]...
i want to make tree like the above format.In the above tree "(value)" shows the code_type i.e. level of the tree..
version----Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
View 2 Replies
View Related
Mar 1, 2012
i've got problem when viewing my records from a tree. When i click a data from a tree it displays 2 times. and when i try to click another data my previous data was gone.
my screenshot is attached
this is my
DECLARE
mytree ITEM;
[code].....
View 37 Replies
View Related
Apr 5, 2011
I want to know how to create tree structure in forms6i. This should be created as " Control-block". For example when user expand department it shows all department names in college as sub tree. Note: I have no table for department..Just display manually..
View 1 Replies
View Related
Jan 24, 2012
I have created the 5 Level Hierarchy Tree using the Tree example
[URL]........
how-to-s/ forms- how- to- create - a- hierarchical - tree-form".
My requirement are
1. I wish to refresh the Hierarchy Tree after DML Operation.
2. I wish to Query the Record when i will select the node.
I have 5 Master Detail Record as:
Master - Detail
Detail Master -- Detail
Detail Master -- Detail
Detail Master -- Detail
Detail Master -- Detail
3. Is there any way to find out which Hierarchy Level currently selected
4. Is there any way to find out which value is for what block.
View 1 Replies
View Related
Nov 30, 2010
I need complete source from hartical tree menu using by calling reports and calling forms.
i need related Table ,triggers and Examples.
View 4 Replies
View Related
Feb 27, 2013
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
I have a problem related with hierarchical queries, I have this tree.
- a
/
b c
/ |
d e fI need to calculate an average per node but using always the child's results for example: Only leafs will have values:
d = 10
e = 20
f = 30
c = 40So the expected result is:
d = 10
e = 20
f = 30
c = 40
b = (10(d) + 20(e) + 30(f)) / 3 (number of child nodes) = 20
a = (20(b) + 40(c)) / 2 (number of child nodes) = 30.
I have tried with recursive queries, hierarchical queries, I guess it's possible with model too but I can't produce the exact results that I need. Maybe in fact there is a very simple solution but I cannot figure it.
Here is an auxiliary WITH that you can use to start your tests:
WITH tree AS
(
SELECT 'd' child, 'b' parent FROM dual UNION ALL
SELECT 'e' child, 'b' parent FROM dual UNION ALL
SELECT 'f' child, 'b' parent FROM dual UNION ALL
SELECT 'b' child, 'a' parent FROM dual UNION ALL
SELECT 'c' child, 'a' parent FROM dual UNION ALL
SELECT 'a' child, null parent FROM dual
[code]....
View 3 Replies
View Related
Jun 10, 2011
I am using oracle forms 9i. I have to create a Hierarchical Tree and attach checkbox at its node,so that user can select which tree he want in its further processing.
View 4 Replies
View Related
Jul 12, 2011
I have to create a functionality on a button through which all nodes of hierarchical tree will be selected.
I know that this is possible with mouse (one-by-one+Ctrl). But,I have to done this on button.
View 1 Replies
View Related
Jan 23, 2011
How to create multi selection tree in oracle forms ?
View 1 Replies
View Related
May 27, 2011
I created TREE object in my application main menu and I want to use it through MOUSE and KEYBOARD therefore I add WHEN-TREE-NODE-ACTIVATED trigger at my tree object
Trigger text is:
declare
htree item;
node_value VARCHAR2(100);
akt_node ftree.node;
[code]...
Problem:When I am using MOUSE and clicking on + or anyother form/report, it's opened last selected form/report or application closed but it is working fine when I am using KEYBOARD.
View 1 Replies
View Related
Nov 16, 2011
There is table:
Column A | Column B |
X | X |
X | Y |
Y | X |
Y | Y |
I have selected view with "group by rollup":
ID | Column A | Column B |
1 | | |
2 | X | |
3 | Y | |
4 | X | X |
5 | X | Y |
6 | Y | X |
7 | Y | Y |
Now I want to display it in client app like a tree.So, I need create ID's of parents in child rows, like this:
ID | Parent ID | Column A | Column B |
1 | | | |
2 | 1 | X | |
3 | 1 | Y | |
4 | 2 | X | X |
5 | 2 | X | Y |
6 | 3 | Y | X |
7 | 3 | Y | Y |
View 7 Replies
View Related
Apr 8, 2009
i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..
i have written the following code in when_tree_node_selected trigger :
if condition then
call_form(path,no_hide,do_replace);
View 3 Replies
View Related
Jun 3, 2010
I have a hierarchical tree with two parent nodes. Each parent node has different number of child nodes. Can i disable or hide a node according to some condition?
View 5 Replies
View Related
Oct 31, 2013
How can I search the tree with a given string?I don't find any function like "search(...)".I want to find the tree nodes which include the given string.
View 5 Replies
View Related
Jan 31, 2013
I have this Query in a tree page (apex 4.2), but i need open a .pdf file in a new window. It's possible?
select case when connect_by_isleaf = 1 then 0
when level = 1 then 1
else -1
end as status,
level,
[code].......
View 1 Replies
View Related
Apr 11, 2011
I got the table where are logged every single events from web site. Sometimes (depends on events)is written xml code into the xml_params column of table H ow to make an automatically procedure (sql query, function, or something like that) which every value of xml code should be written separate into the column the table. The point is I dont know what parameters will be included in the xml code in future.
For instance xml looks like:
<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="loginReason">
<value>invalidUserNameOrPassword</value>
[code]....
View 17 Replies
View Related
Feb 20, 2013
Version Info: 11.2 on Solaris 10
I have a partitioned table like below. I want to create a B-Tree index on SALES_RGN column which is neither the part of Primary key or the Partitioned key. Should I create this index as local or Global ?
CREATE TABLE sales_dtl
(
txn_id number (9),
salesman_id number(5),
salesman_name varchar2(30),
sales_rgn varchar2(10), -----------------------------> This column needs to be indexed
sales_amount number(10),
sales_date date,
constraint pk_sales_dtl primary key (txn_id)
[code]....
View 9 Replies
View Related
Nov 1, 2012
i have 3 tables
table employee
employee_no
employee_name
table manager
manager_id
manager_name
table department
dept_id
dept_name
how can show on the tree all manager with employee with department
example
manager ali
employee Mohamed
department marketing
View 6 Replies
View Related
May 17, 2010
I would like to display data using the Hierarchical tree up to 3 levels.I need to create all the nodes programatically.
Means Create the parent (Parent1) level nodes , then create the child (Child1) nodes and add them under the parent1.Similarly create child nodes (Child2) and add them under the parent (Child1).
View 1 Replies
View Related
Mar 1, 2013
I have one table test_pc :
EVENT_IDPARENT_EVENT_ID
2001
2002 2001
1001 2002
1002 1001
1004
1003 2005
1006
Scripts are mentioned below :
create table test_pc
(event_id number(10), parent_event_id number(10));
insert into test_pc values(2001,null);
insert into test_pc values(1006,null);
insert into test_pc values(1004,null);
insert into test_pc values(2002,2001);
insert into test_pc values(1001,2002);
insert into test_pc values(1002,1001);
insert into test_pc values(1003,2005);
In order to derive parent child relationship I have ran the following query :
select *
from test_pc
start with parent_event_id is null
connect by nocycle prior event_id = parent_event_id;
It is giving all the records except the last one (i.e. event_id = 1003 and parent_event_id = 2005), because the parent_event_id does not exists in the table.
But my requirement is to show all the record, i.e. if the parent_child relationship is present then show accordingly and also show the rest of the records where the parent_child does not exists even the parent_event_id exists.
View 5 Replies
View Related
Jul 19, 2012
What should be the order of columns while creating b-tree non-unique index.
Low cardinality first
or
Hight cardinality first
View 15 Replies
View Related
Sep 27, 2012
I have a requirement in which on Change event of shuttle, the tree present in another region of the same page should refresh. On refresh, the tree should include the values selected on the right hand side of the shuttle as its nodes.
View 3 Replies
View Related
Feb 8, 2013
Ive created a tree structure and i want it to popup the called page.select case when connect_by_isleaf = 1 then 0
when level = 1 then 1
else -1
end as status,
level,
'<SPAN style="color:red;">' || "ENAME" || '</SPAN>' as title,
(case when level = 1 then '#IMAGE_PREFIX#Fndtre11.gif'
[code]....
View 6 Replies
View Related