SQL & PL/SQL :: Getting Parent And Leaf Child Along With ID

Jun 14, 2012

I've the following data and i need the parent and leaf most child,including their id's

Create table par_chld (id,p_id,c_id) as
(select 1,900,501 from dual union all
select 2,900,502 from dual union all
select 3,900,503 from dual union all
select 4,100,900 from dual union all
select 5,200,900 from dual union all
select 6,300,400 from dual union all
select 7,101,500 from dual union all
select 8,102,500 from dual union all
select 9,103,500 from dual union all
select 10,201,600 from dual union all
select 11,201,601 from dual union all
select 12,201,602 from dual )

In the above data
p_id =100 has c_id 900 , and this 900 acts as a parent which has child 501 , 502 ,503

And the output should be in the following format , Where i don't need the middle level data. I need parent and its leaf child along with the id's of parent and child

Expected output

id_p p_id id_c c_id

4 100 1 501
4 100 2 502
4 100 3 503
5 200 1 501
5 200 2 502
5 200 3 503
6 300 6 400
7 101 7 500
8 102 8 500
9 103 9 500
10 201 10 600
11 201 11 601
12 201 12 602

I tried the following query where i got the p_id,id_c,c_id columns , but unable to get the column id_p

select connect_by_root p_id p_id ,id id_c,c_id
from par_chld
where connect_by_isleaf =1
start with p_id not in (select c_id
from par_chld
where c_id is not null)
connect by nocycle p_id =prior c_id

getting the id_p column as expected above.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Permissions On Given Leaf Node Then All Parent / Grand Nodes Should Return?

Feb 4, 2012

writing an SQL query for the following scenario : have table ACCOUNTS that stores results in parent child relationship.

ACCT_ID |ACCT_NAME |ACCT_PARENT |ACCT_TYPE
1 |Group1 | 0 | 1
2 |Group2 | 1 | 1
3 |Group3 | 2 | 1
4 |Account1 | 3 | 0
5 |Account2 | 3 | 0
6 |Account3 | 2 | 0

Here ACCT_TYPE=0 denotes leaf node.Another table USER_ACCOUNT stores user permissions on the leaf nodes from ACCOUNTS table.

USER_ID |ACCT_ID| PROPERTY
1 | 4| Yes
2 | 5 | No
3 | 6| Yes

This shows user=1 has permission on Account=4 , user=2 does not have access priviledge on Account_id=5.Now my requirement is to write a SQL query such that If user has permissions on a given leaf node then all the parent / grand parent nodes should also be returned.
Eg:
SELECT * FROM (.......) where USER_ID=2;

should give result something as below :

USER_ID | ACCT_ID |ACCT_NAME | PROPERTY
1 | 4 | Account1 |Yes
1 | 3 | Group3 |Yes
1 | 2 | Group2 |Yes
1 | 1 | Group1 |Yes

with this ASAP as this is quite an urgent issue that I need to fix.

View 15 Replies View Related

PL/SQL :: How To Update Parent And Child Tables While Updating Parent Table

Jun 13, 2012

I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.

Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well

Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.

View 5 Replies View Related

Parent Child Relationship

May 13, 2011

Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????

Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?

View 1 Replies View Related

SQL & PL/SQL :: Parent-Child Hierarchy?

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

SQL & PL/SQL :: Parent Child Relation

Sep 17, 2013

I have one requirment like below

Table1 have records like below

parent childflag
null.....A......Y
A........B......Y
B........C
C........D
D........E......Y

I want it should be like

parent child flag
null......A.....Y
A.........B.....Y
B.........E.....Y

I want to replace 'D' (parent of 'E') with any of the upper level which has Flag 'Y' (first level parent which satisfy falg 'Y')

View 7 Replies View Related

SQL & PL/SQL :: Creating Parent Child Relationship

Aug 8, 2011

I have a table called FAMILY which holda the data like , for father_gen_1 --->father_gen_2 --> father_gen_3 -- son.

father_gen_1 father_gen_2 father_gen_3 son
ABC XYZ GEF MNC
ABC lmn kth

I want to get the output which is a three column heirarical table and holds the parent child relationship Like following:-

id name parent_id
1 ABC NULL
2 XYZ 1
3 GEF 2
4 MNC 3
5 LMN 1
6 KTH 5

View 7 Replies View Related

SQL & PL/SQL :: Way Of Getting Only Parent And Child Record From Table

Jul 27, 2010

i have a table with data as follows:

select genres.* from genres

data is as follows

INTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
14430015Biography 157 0100
14430015Classics & Poetry 173 0116
14430015Literature & Anthologies 175 173118

now when i give

select level,genres.* from genres connect by prior INTGENREPAGEID= INTPARENTID

i get

LEVELINTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
114430015Biography 157 0100
114430015Classics & Poetry 173 0116
214430015Literature & Anthologies 175 173118
114430015Literature & Anthologies 175 173118

i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.

View 5 Replies View Related

SQL & PL/SQL :: Parent And Child Table - Delete Row

May 18, 2010

I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.

SQL> create table t1(a number primary key, b number);
SQL> create table t2(c number, d number references t1(a));
SQL> insert into t1 values(1,2);
SQL> insert into t1 values(2,3);
SQL> insert into t1 values(3,4);
SQL> insert into t2 values(10,3);
SQL> insert into t2 values(20,2);
SQL> delete from t1 where a=2;
delete from t1 where a=2
*

ERROR at line 1:
ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child
record found

View 11 Replies View Related

SQL & PL/SQL :: Update Parent Child Table?

May 26, 2011

i want to update primary key of 1 master and 2 child table is it possible if i do it simultaniously if not

View 9 Replies View Related

SQL & PL/SQL :: Delete - Parent / Child Relationship

May 22, 2012

I have a DB that has one parent table with many child tables. I would like to delete all records(child - parent) based upon a particular "net_id". I have three versions that "seem" to work BUT I'd like to use "version2" because I want to go to the parent one time(for the parent delete I just made it simple for me) only and be able to delete the child record(s) from the child table(s). I want to achieve this w/o using triggers...

My question: Is version2 an "ok" way to delete child records or should I try another method(version1 or 3)??

create table PARENT
(
event_id NUMBER,
event_title VARCHAR2(50),
net_id VARCHAR2(16)
);

--truncate table parent;
insert into parent
(event_id, event_title, net_id)
values
(1, 'title1', 'CURLY');
[code]...

View 10 Replies View Related

SQL & PL/SQL :: Delete Records From Child And Parent Table

Jan 2, 2013

I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.

MASS_MASTER --parent table.
MASS_CHILD --child table.

The below query is used to delete records from parent table.

DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);

The child table MASS_CHILD is not having last_date column. provide me the query to delete same records from child table.

View 21 Replies View Related

Forms :: How To Assign Value To Parent From Child Form

Feb 29, 2012

I have two forms, FormPARN, FormCHLD.

One Text item is there in FormPARN, near to that Text item, Button is there.While clicking Button FormCHLD will be opened. Inside the FormCHLD, i will do some calculation and i need to store the result in FormPARN's Text Item.

How can i do it?

View 10 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

SQL & PL/SQL :: Procedure To Get Path From Parent-child Relationship?

Apr 23, 2010

I have a table which uses parent-child relationship to store the options available. I need a procedure to give me the full path given the id of a particular option.

I have two different functions. One of them uses the Oracle built in function and the other uses simple queries with a loop.The code of the functions are given below.

Now, the problem is with their "performance". The difference in their performance is significant. The function using the Oracle function takes more than 2 hours to run a query whereas the other function takes less than 2 minutes.

I am having trouble trusting the other function. No matter how many tests I perform on the output of both the functions, it always comes out to be the same.

Function 1
=====================
FUNCTION Gettree (opt_id IN NUMBER,i_app_id IN NUMBER)
RETURN VARCHAR2
IS
path VARCHAR2(32767);
application_no NUMBER;
BEGIN

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Finding Child And Parent Rows In Table?

Jun 6, 2011

I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.

for ex

E
A-->B-->C-->D
F-->G
H

When i will pass d as node it will return c,b,a,e,f,g,h

View 3 Replies View Related

SQL & PL/SQL :: ORA-02291 Parent / Child Relationship Error

Jan 4, 2012

I am working on a project, the insert statement is not functioning properly and I cannot find the error The error is in the insert statement for CustOrder. The error states that NULL cannot be inserted into CustOrder.CustID. I have declared CustID and its reference. I have looked over the code several times and I cannot find the problem.

If it makes a difference I am using APEX. The company has not database or program to work with a database so I am building everything from scratch.

declare
l_CustOrderID number;
l_CustID number := :P11_CustID;
begin

[code]....

View 25 Replies View Related

SQL & PL/SQL :: Join 2 Tables To Get Parent Child Data

Mar 24, 2012

I have 2 tables, ITEM_T is the base table and RELATIVE_T is the relation table which establish the relationship between parent and child.

I want to give ZZZ child as input and I want get output in below format. I tried various method, but I am not able to get the below result.

Result:

ChildParent_1Parent_2
ZZZYYY XXX
ITEM_T
PK_IDITEM_ID
PK_1XXX

[Code]...

Expected Result:

When give input as ZZZ:

ChildParent_1Parent_2
ZZZ YYY XXX

View 7 Replies View Related

PL/SQL :: Query To Fetch Parent Records Only If No Child With Particular Status?

Sep 16, 2012

I need to fech parent records only when no child record with status 'N' exists. There are only two possible values for status column of child table 'Y' / 'N'.

Below are table structures and insert statements for data.

CREATE TABLE MASTER
(
COL1  NUMBER,

[Code]....

COMMIT;Query I framed is below

select * from master where exists (select null from child where child.col2 = master.col1
group by child.col2 having count(distinct col3) =1 )

Output in above case would be 3 as for 1 there's one record with status as 'N' and for 2 there's no child record. I am on 10g.

View 2 Replies View Related

Forms :: Hierarchical Tree In Oracle - Parent Child Relationship

May 14, 2010

I have data in a table which don't have parent child relation ship. Can I display the data in that table in hierarchical tree item in a form.

Is it a must that data should have parent child relationship to display them in hierarchical tree item in a form.

View 1 Replies View Related

XML DB :: How To Select Parent / Child Related Data From XMLTYPE Table

Jan 9, 2013

select * from V$VERSION

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.

Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.

how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.

My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.

-- WHat I would like to determine from a select statement:

LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202

(The data has been simpliied and masked, but is true to the content and is queryable).

select * from V$VERSION

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

SQL & PL/SQL :: How To Get All The Data From Child Tables

Jun 2, 2011

I have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.

I want to build a query such that it will give all the data from child tables.

create table main_tab ( tab_name varchar(10));

insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');

Now I have got the query to combine all the table data using

select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab

but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.

I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?

View 12 Replies View Related

SQL & PL/SQL :: Batch ID From Child Status

Mar 27, 2013

Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'. In this query im expecting a query which returns batch 2,3 and 4.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);

[Code]...

View 9 Replies View Related

Parent Key Indexing

Jan 30, 2012

I have, for example, two tables: COMPANIES and EMPLOYEES. COMPANIES has a primary key on column COMP_ID. EMPLOYEES has a foreign key EMP_COMP_ID. I always do query COMPANIES with WHERE clause on COMP_ID and COMP_ADDRESS. What indexes on COMPANIES should I create in this case? First unique on COMP_ID, second composite on COMP_ID and COMP_ADDRESS? Or one unique index on COMP_ID and COMP_ADDRESS will be enough?

View 5 Replies View Related

SQL & PL/SQL :: Identifying Parent ID

Oct 22, 2011

I want to insert data from table A and dump into table B.The data in Table A looks like this:

Level_IDCode Type
ETY_1A......... 100000116
ETY_1B......... 100000116
ETY_1C......... 100000116
ETY_2AA........ 100000117
ETY_2AB........ 100000117
ETY_2AC........ 100000117
ETY_2BA........ 100000117
ETY_2BB........ 100000117
ETY_2BC........ 100000117
ETY_3AAA....... 100000118
ETY_3AAG....... 100000118
ETY_3AAI....... 100000118
ETY_3AAX....... 100000118
[code]....

And I want to dump the data into Table B which will look like this:

Level_ID Code Type Parent_cd
ETY_1A.........100000116null
ETY_1B.........100000116null
ETY_1C.........100000116null
ETY_2AA........100000117A
ETY_2AB........100000117A
ETY_2AC........100000117A
ETY_2BA........100000117B
ETY_2BB........100000117B
ETY_2BC........100000117B
ETY_3AAA.......100000118AA
ETY_3AAG.......100000118AA
ETY_3AAI.......100000118AA
[code]....

I need to populate a column called Parent Cd with the values that sits at the base level of the hierarchy. So, if the code A sits at the base level then its parent is NULL and the parent for code AA, AB, and AC will be A and parent for AAA, AAG will be AA and so on.The level_IDs are upto 10 and there are close to 400,000 records.

View 5 Replies View Related

SQL & PL/SQL :: Extract Correct Value For Child Records

Mar 22, 2011

Test case: drop table test;

create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))

select * from test;

insert into test values (143,'frank','kadel,watson','j2098,k09876');
insert into test values (143,'steve','kadel,watson','j0987i,kuy765');
[code]....

The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.

For example:- Where id = 143, the resultset should be like this.

ID LAST_NAME FIRST_NAME EMPNO
---------- --------------- --------------- ---------------
143 frank kadel j2098
143 frank watson k09876
143 steve kadel j0987i
143 steve watson kuy765

sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.

SQL> select id, last_name, EXTRACTVALUE(x.COLUMN_VALUE, 'e') first_name, empno
2 from test,
3 TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(first_name, ',', '</e><e>') || '</e></e>')
4 .EXTRACT('e/e'))) x
5 ;
[code]...

View 8 Replies View Related

XML DB :: Un-nest XML With Single Child Element

May 20, 2013

I'm trying to write a generic function for un-nesting child nodes where nesting is deemed unnecessary. Typically when the element only has one child element.

For example, given the following source....
<ROOT>
  <ITEM>
    <DESCRIPTION>TEST1</DESCRIPTION>
  </ITEM>
  <ITEM>
    <DESCRIPTION>TEST2</DESCRIPTION>
  </ITEM>
</ROOT>I actually want.....
<ROOT>
  <DESCRIPTION>TEST1</DESCRIPTION>
  <DESCRIPTION>TEST2</DESCRIPTION>
</ROOT>

because we think ITEM isn't really required before we deliver XML data.I've been trying to achieve this with a function, where I pass in the XPath to the node I want flattening, something like

function UnNest(pXMLData XMLType, pXPath varchar2) return XMLType...Called like the following...
declare
begin
  vXMLData := UnNest(SomeXMLData, '/ROOT/ITEM')
end;I tried using XQuery Update (11gR2), as follows,
[code]....

My destination platform is 11gR2 64 bit but going forward I may need a solution for 10gR2 too. Perhaps there is another way without using XQuery Update?

View 2 Replies View Related

SQL & PL/SQL :: Loading Automated CHILD Table

Nov 16, 2011

The manual work around on populating child tables for testing purpose are taking long time and its very painful work. So I am trying for a tool that takes parent table name and child table name as input and produce insert statements for child table with foreign keys as output.

View 5 Replies View Related

11g Child Table Update Locking?

May 26, 2013

I've a table TXN1 transaction and has FKs to 3 different tables Account, customer, country and currency. ALL FKs are indexed (bitmap). I am updating TXN1 of amount column about 10,000 rows. (SID 1) As expected, it has taken lock type 3 SX on TXN1. But it has taken on lock type 4 (share) on Account, customer and country. Committing every 10k rows.

At the same time sid 2 is inserting into another TXN2 table which has FK to the same dimensions account, customer and currency. Only FK on ac_id is bitmap indexed. The inserts have taken SX lock(type 3) on tXN2 table (expected). But it is trying to take SX type 3 lock on account, customer, currency tables. typ3 lock taken on CCY but waiting on CST. But It is blocked by sid 1. It has resulted into Enque-TM contention and resulted into anywhere 60-300 secs wait time..

I understand update/delete in parent table results into locking of SX of child tables and need the FKs to be indexed to avoid etc.

1. Why is SID1 taking shared lock on the parent tables Account,customer,country and currency tables? The update statement is not updating any of those FK columns nor referring them in where clause(if it matters!). Is it to ensure that the parent rows are not deleted?

2. Why is SID2 taking SX lock on the dimension tables? Why is it not taking RS lock type 2 on parent rows? Why is SID1 taking shared lock type 4, but not 2?

View 13 Replies View Related







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