Home » SQL & PL/SQL » SQL & PL/SQL » Heirarichal Query to get metadata information. (Oracle 11g, Windows)
Heirarichal Query to get metadata information. [message #446005] Fri, 05 March 2010 02:15 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
hi all,
i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).
[b]table structure[/b]
DEPT
 |PK-DEPT_ID
 |
EMP
 |pk-EMP_ID
 |fK emp_DEPT_fK((FK column is dept_id references dept table dept_id column))
 |
 |
EMP_AUTHORIZATION
 |pk-EMP_ID,aUTHORIZTION_ID
 |fk-EMP_AUTH_FK(FK column is EMP_ID references emp table emp_id column)
 | 
 |
 |
EMP_AUTHRIZATION_CARD
 |pk-EMP_ID,aUTHORIZTION_ID and card_id
 |fk-EMP_card_FK(FK column is EMP_ID,aUTHORIZTION_ID references EMP_AUTHORIZATION table EMP_ID,aUTHORIZTION_ID column)
 | 
 |
 |
EMP_AUTHRIZATION_DUP
 |
 |fk-EMP_card_FK(FK column is EMP_ID,aUTHORIZTION_ID,card_id references  EMP_AUTHRIZATION_CARD table EMP_ID,aUTHORIZTION_ID and card_id column)
 
Expected output
...................
table_name                  path
DEPT                        DEPT
EMP                         DEPT/EMP
EMP_AUTHORIZATION           DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD       DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP        DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP

but by using below query i am getting complete heirarichy.
SELECT    LEVEL, 
           Table_Name,
           Constraint_Name,
           R_Constraint_Name ,
           SYS_CONNECT_BY_PATH(Table_Name, '/') Path
      FROM all_constraints
      Where Table_Name in ('DEPT','EMP','EMP_AUTHORIZATION','EMP_AUTHRIZATION_CARD','EMP_AUTHRIZATION_DUP') 
          owner = 'SCOTT'   
CONNECT BY PRIOR   Constraint_Name = R_Constraint_Name
 order by Path



could you please suggest where i'm going wrong in this.
Re: Heirarichal Query to get metadata information. [message #446009 is a reply to message #446005] Fri, 05 March 2010 02:34 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Sure you will get this
ERROR at line 8:
ORA-00933: SQL command not properly ended



SELECT    LEVEL, 
           Table_Name,
           Constraint_Name,
           R_Constraint_Name ,
           SYS_CONNECT_BY_PATH(Table_Name, '/') Path
      FROM all_constraints
      Where Table_Name in ('DEPT','EMP','EMP_AUTHORIZATION','EMP_AUTHRIZATION_CARD','EMP_AUTHRIZATION_DUP') 
/*missing "and" here*/          owner = 'SCOTT'   
CONNECT BY PRIOR   Constraint_Name = R_Constraint_Name
 order by Path




sriram Smile

[Updated on: Fri, 05 March 2010 02:38]

Report message to a moderator

Re: Heirarichal Query to get metadata information. [message #446014 is a reply to message #446009] Fri, 05 March 2010 02:55 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Thanks for your reply.
But still by using that query we dont get all the levels. we are getting only 2 levels after that its not coming.

SELECT    LEVEL, 
           Table_Name,
           Constraint_Name,
           R_Constraint_Name ,
           SYS_CONNECT_BY_PATH(Table_Name, '/') Path
      FROM all_constraints
      Where Table_Name in ('DEPT','EMP','EMP_AUTHORIZATION','EMP_AUTHRIZATION_CARD','EMP_AUTHRIZATION_DUP') 
AND         owner = 'SCOTT'   
CONNECT BY PRIOR   Constraint_Name = R_Constraint_Name
 order by Path


Could you please suggest how to get all the levels by heirarichal queries.

[Updated on: Fri, 05 March 2010 02:58]

Report message to a moderator

Re: Heirarichal Query to get metadata information. [message #446024 is a reply to message #446014] Fri, 05 March 2010 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please use SQLPlus and copy and paste what you have.
You have to understand that we have not your data and can't see what you do not post.


Regards
Michel
Re: Heirarichal Query to get metadata information. [message #446035 is a reply to message #446024] Fri, 05 March 2010 05:09 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Hi,
Thanks for your feedback and complete details are provided in the attached document.

Could you please suggest a way to acheive the output as given in the attachment?

Thanks in advance.

Re: Heirarichal Query to get metadata information. [message #446053 is a reply to message #446035] Fri, 05 March 2010 06:03 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:661009003696
Have a look at this Play with that...

Goodluck

sriram Smile
Re: Heirarichal Query to get metadata information. [message #446083 is a reply to message #446035] Fri, 05 March 2010 07:03 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table statements then we will work with your tables.

Regards
Michel

Previous Topic: compare two table data
Next Topic: how to initialize a pl/sql array (merged by CM)
Goto Forum:
  


Current Time: Mon Sep 26 14:33:02 CDT 2016

Total time taken to generate the page: 0.07470 seconds