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

Structure In Tree Query

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

SQL & PL/SQL :: Hierarchy Query And Tree Structure

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

SQL & PL/SQL :: Bitmap Or B-Tree Index?

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

Forms :: Hierarchical Tree In 6i

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

SQL & PL/SQL :: To Get The Results In The Form Of A Tree

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

PL/SQL :: Copying A Tree Structure

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

Make Oracle Tree In Specified Format?

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

Forms :: Viewing Records From A Tree?

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

Forms :: How To Create Tree Structure In 6i

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

Forms :: Refresh Tree After DML Operation

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

Forms :: Hierarchical Tree Menu

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

PL/SQL :: Average In Tree With Partial Results

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

Forms :: Add Checkbox With Hierarchical Tree Node

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

Forms :: Multiple Selection In Hierarchical Tree?

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

Forms :: How To Create Multi-selection Tree

Jan 23, 2011

How to create multi selection tree in oracle forms ?

View 1 Replies View Related

Forms :: Tree Object - Want To Use It Through MOUSE And KEYBOARD?

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

SQL & PL/SQL :: Roll-up Tree With Parent-child Identifiers?

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

Forms :: Tree Hierarchy - Not Working Properly?

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

Forms :: Disabling A Node In Hierarchical Tree?

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

Application Express :: How To Search Tree With A Given String

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

Application Express :: Tree Link On A New Window

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

JDeveloper, Java & XML :: Parse Automatically By Tree?

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

B-Tree Index On Column - Local Or Global

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

Forms :: Show Manager / Employee And Department On The Tree

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

Forms :: Display Data Using Hierarchical Tree Up To 3 Levels?

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

SQL & PL/SQL :: Error For Creating Tree Structure - Not Showing All Records

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

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

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

Application Express :: Refresh Tree On Change Of Shuttle

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

Application Express :: Tree To Popup A Called Page

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







Copyrights 2005-15 www.BigResource.com, All rights reserved