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
ADVERTISEMENT
Apr 29, 2013
I am using Oracle database 10g R2.
suppose I have this structure
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
3 FRANK 2
What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.
For example, when I query for EMP_ID=2 ,I want to get this result
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
and when I query for EMP_ID=3 ,I want to get this result:
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
3 FRANK 2
How to write this query?
View 3 Replies
View Related
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
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
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
Jun 12, 2012
I have a hierarchy tree menu look like this
+GL
Accounts
Inform
+Export
Export Entry Form
+Security
New User Entry
when i click Gl==>Accounts, accounts form opens and i want call my forms and reports through nodes.
View 1 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
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
Nov 22, 2010
I need to trap user input by checking if the node in my tree is selected (higlighted) or not. When I test using SYSTEM.TRIGGER_NODE_SELECTED, whether I click the node to select (the node is highlighted) OR I click the plus (+) sign to expand the node, the function both retunrs TRUE.
I don't the user to add node when the current node is not selected and highlighted.
View 1 Replies
View Related
Jan 31, 2012
I have already done a hierarchy tree. Now I'd like to make it bind to text items. When i click a node, it should automatically be bound to text items.
My tree is about sections of students, now when i click a specific section to view its corresponding subjects it would be displayed on text items.
For example:
+Sections
+First Semester
+LM2
+LM3
+Second Semester
+LM4
+LM5
When i would click on LM2 node, its subjects should appear immediately.
View 1 Replies
View Related
Jun 10, 2013
Is it possible to sort records based on hierarchy in such a way that records sorts in sequence and records of same level comes in the last (in sorting order)
Eg: Default hierarchy level order: 0,1,2,2,3,3,3,4,5
Above given is the default sorting order of an query for an parent 'NODE_A1' having multiple same level i.e 2 and 3
Expected Hierarchy level order: 0,1,2,3,4,5,2,3,3
Please check attached file for an example.
View 8 Replies
View Related
Jun 4, 2013
How to get only the last level in Oracle SQL Hierarchy Query?
View 2 Replies
View Related
Mar 5, 2013
To use dynatree (URL] I want the result to be in the xml form.but the result is not what I want.
SELECT
XMLELEMENT("div",xmlattributes('tree' AS "id"),
(SELECT DBMS_XMLGEN.getXMLType(
DBMS_XMLGEN.newContextFromHierarchy('
SELECT LEVEL,
case
when CONNECT_BY_ISLEAF = 0 then
[code]....
View 6 Replies
View Related
Nov 9, 2011
We are on oracle 10.2.0.4 on solaris 10. My question is on a sql query. Is it possible to rewrite a query to avoid the connect by and prior constructs and use joins? For example i have the following query:
SELECT empno,
ename,
job,
mgr,
hiredate
FROM emp
START WITH empno in (7566,7698)
CONNECT BY PRIOR empno = mgr
How can it be rewritten using a two table join (self join)? I am not sure if it can be done and whether it is possible.
View 4 Replies
View Related
Jul 30, 2013
I've below tables.
create table tst_link (parent_pid number(5),
child_pid number(5)
);
insert into tst_link (1, 2);
[Code]....
For each parent pid i need to calculate total miles (including the child_pid also).
Required Output
PARENT_PID TOTAL_MILES
----------- ------------
1 48
4 100
8 125
View 4 Replies
View Related
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
View Related
Sep 3, 2012
I am struck in a requirement to show the organization structure in a Top Down Organizational structure.
Is there any way we can make the well structured report OR form?
View 4 Replies
View Related
Oct 1, 2009
query in order to display a hierarchy list. Example:
Database has three tables
Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content
order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.
I assumed there was no need to track child content.
Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.
Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3
Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?
View 11 Replies
View Related
Dec 20, 2012
way to achieve the below logic.
SQL to pick up parent child relationship within same table with a certain logic.
Example:
mod_product_numberProduct_Hierarchy
H555888 PH05678
H888987 H555888
H8889 H555888
[code]...
Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.
If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.
View 11 Replies
View Related
Jun 13, 2011
Ihave the following result set based on a hiearchy query
SELECT LEVEL ,
BM.ITEM item
BM.Part part
connect_by_isleaf leaf
FROM item_part_tab bm
START WITH bm.item_id='002PH'
CONNECT BY BM.ITEM_ID = prior BM.PART_id
LEVEL ITEM PART LEAF
-----------------------------------------------------------------
1 002IT XPART1 0
2 XPART1 SUBPART1 1
In the above resultset PART='SUBPART1' does'nt have any child as evident from the connect_by_isleaf (LEAF=1) .Is there any way to create a pseudo record if such values are returned by the query, i.e., if a part has no tree under it a dummy record should be created i.e. in this case an additional record should be returned by this query. i.e., as below. the LEVEL=3 record should be created.
LEVEL ITEM PART LEAF
---------------------------------------------------------
1 002IT XPART1 0
2 XPART1SUBPART1 1
3 SUBPART1 1
View 16 Replies
View Related
Feb 1, 2012
Is it possible to find the locked objects in hierarchical order. Consider the below example
--Connect to scott schema
create table block_session(a NUMBER, b VARCHAR2(100))
/
insert into block_session select rownum,rownum*10 from dual connect by level<=10
/
COMMIT
/
GRANT ALL ON block_session to HR
/
update scott.block_session
SET a=10
where b=10
[code]......
Is it possible to get all locked table details in hierarchical order?
View 4 Replies
View Related
Jul 18, 2013
I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.
CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
[code]......
Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';
At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.
SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;
I want to suppress such duplicates. So I added DISTINCT
SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;
BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.
View 11 Replies
View Related
Oct 3, 2013
I want to create a materialized view for the with same logic as below view
CREATE OR REPLACE FORCE VIEW GFL_MCP_CORE.VW_CLIENT_HIERARCHY
(KEY_INDENTED, CLIENT_LEVEL, ID, NAME, CLIENT_TYPE_CODE,
L2_CLIENT_ID, L2_CLIENT_NAME, TOP_CLIENT_ID, TOP_CLIENT_NAME, CLIENT_MCP_FLAG,
CLIENT_GROUP_NAME, ORG_REGION_NAME, COUNTRY_NAME, PROCESSOR_CLIENT_CODE)
[code]....
Here is the sample data for the client table and other tables are generic.
Row#IDNAMECOUNTRY_CODECLIENT_TYPE_CODECLIENT_GROUP_IDPARENT_CLIENT_IDSTATUS_FLAG
134801ZhuanshenguangchangCNBR10048232909A
212515229ConsolidatedFCUUSCL54812515A
314821286 Golden Mile BranchCABR53112712A
42050014513Bay CityUSBR56612009A
53864712-44-125 LeicesterGBBR56038630A
63869114736 Freestar BrickyardUSBR10020238686A
731141Walsall StoreGBL254631103A
View 1 Replies
View Related
Feb 26, 2010
I want to build a hierarchy and output it into a sub-report. Since the data in few hierarchy fields exceeds 4000 characters and therefore m getting the ORA-01489 error and the entire report was failing.
So i have used user aggregate function to print the out put. I have also used row_number() to print sequence in query output.
the query that i am using is
SELECT pr_id,
REPLACE (REPLACE (twadmin.STRAGG2(distinct RPAD (to_number(curr), 2, ' ') || ')' || NVL (DECODE (medical, NULL, clinical, medical), 'UNKNOWN') || '###' ||
RPAD (' Causality as per rport ', 50, ' ') || ': ' || reporter || '###' ||
RPAD (' Causality as per report2 ', 50, ' ') || ': ' || hospital || '###' ||
RPAD (' Dechallenge', 50, ' ') || ': '|| dechallenge || '###' ||
RPAD (' Rechallenge', 50, ' ') || ': '|| rechallenge), ',', CHR(10)), '###', CHR (10)) causality
[code]......
the output of the query is
ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Mental status changes (Mental status changes)
[code].......
but the expected out put is
ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
[code]........
but it should be as
1)
2)
3)
..
..
..
10)
11)
12) for the values in the causality field
View 25 Replies
View Related
Aug 3, 2012
I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.
AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3
[code]....
Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.
I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.
1. What is the best to way (design)to pass input parameter for the function.
2. How to generate hierarchy in xml using DBMS_XMLGEN
View 12 Replies
View Related
Jan 21, 2003
I need to find all "report to" supervisors in a hierarchical chain. A binary search won't work, as each level may contain > 2 supervisors. Example:
1
2 3 4
5 6 7 8 9
10
10 reports to 9
9 reports to 4
5 and 6 report to 2
7 and 8 report to 3
2, 3 and 4 report to 1
Result for all reporting to 1 would be a table containing 2, 3, 4, 5, 6, 7, 8, 9, 10 (order is unimportant!).
I think I need to use recursion. I can do this in C; need code for PL/SQL??? (I can't get the "tree" to display well!)
View 4 Replies
View Related
Apr 20, 2013
What is the hierarchy to stop and start oracle RAC instance ?
View 10 Replies
View Related
Aug 22, 2013
Here is the table structure I will be speaking of below:
SELECT lvl_1_id, lvl_1_dsc,
lvl_2_id, lvl_2_dsc,
lvl_3_id, lvl_3_dsc,
lvl_4_id, lvl_4_dsc,
lvl_5_id, lvl_5_dsc,
lvl_6_id, lvl_6_dsc,
lvl_7_id, lvl_7_dsc,
lvl_8_id, lvl_8_dsc
FROM item_hier_tbl;
I have a table that mimics an hierarchy of items (parent-child relationship). The top level (level 1) never has a NULL value, but it is possible for any of the lower values to have a NULL value. For example, levels 1, 2, 5, 7, & 8 may have values, but 3, 4, & 6 may all be NULL.
I need a View that can return the same data, but with out NULL values. The View would move the NOT NULL values up the levels, so there is consecutive data on each level starting at level. So using the example, the View would move value in 5 up to 3, 7 up to 4, and 8 up to 5. Then level 6 to 8 would have NULL.
I have done this already by creating a 2nd table and using a stored proc to copy the data over to the new table they I want it. But I was told a View or Materialized view "may" work, and perform quicker during the queries. I looked at the Oracle functions LAG and LEAD, but by definition they work by rows, I need to work by columns on the same row.
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
Is what I am asking possible in a View or Materialized View?
View 2 Replies
View Related
Feb 7, 2013
--this for txn details
CREATE TABLE txn_det(
txnid NUMBER PRIMARY KEY,
amount NUMBER,
status varchar2(50),
cust_id NUMBER);
----this for customer details
CREATE TABLE cust_det(
cust_id NUMBER PRIMARY KEY,
cust_name VARCHAR2(50),
cust_acc number(15));
--data to insert for customer table
INSERT INTO cust_det VALUES(101,'Miller','12345');
INSERT INTO cust_det VALUES(201,'Scott','45678');
----data to insert for txn table
INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);
INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);
insert into txn_det values('tx0049',78.12,'success',101);
INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);
Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.
View 30 Replies
View Related
Jul 11, 2012
How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"
any other way to get it? I need like table name field name datatype
View 11 Replies
View Related
