Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Connect by - all parents of child in 1 row (oracle 9i (10g))
Oracle Connect by - all parents of child in 1 row [message #312715] Wed, 09 April 2008 11:42 Go to next message
san_gee
Messages: 5
Registered: April 2008
Junior Member
Hi,

I am having some trouble in displaying information. I have used an example below to explain.

Table values delimited by :
child:childname:parent
100:Films:null
101:Drama Films:100
102:Comedy Films:100
103:Reality:null
104:DailyReality:103
105:HourlyReality:104

What I want is 1 row per child with all its parents listed in one row
100 Films
101 Drama Films/Films
102 Comedy Films/Films
103 Reality
104 DailyReality/Reality
105 HourlyReality/DailyReality/Reality

I wrote the below query

select child, childname, parent,
SYS_CONNECT_BY_PATH (childname,'->') "Parents - Root to immediate" ,LEVEL
from Table
connect by prior child = parent

am getting similar result, just that I get many rows for each child which has many levels.
eg.
105 HourlyReality/ Level 1
105 HourlyReality/DailyReality/ Level 2
105 HourlyReality/DailyReality/Reality Level 3

I want to display the max(level) for each child and not have multiple rows.

Hope am clear.

Any pointers please?

Thanks
Sangeetha
Re: Oracle Connect by - all parents of child in 1 row [message #312721 is a reply to message #312715] Wed, 09 April 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to display the max(level) for each child and not have multiple rows.

Use MAX function in its analytic form and select only those where this max equals to the current level.

Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Always post your actual Oracle version (4 decimals), "Oracle 9i (10g)" is ambiguous, doesn't it?

Regards
Michel
Re: Oracle Connect by - all parents of child in 1 row [message #312729 is a reply to message #312721] Wed, 09 April 2008 12:10 Go to previous messageGo to next message
san_gee
Messages: 5
Registered: April 2008
Junior Member
am not sure how you can do this max with a connect by. can you please elaborate?
Thanks for the quick response.
Re: Oracle Connect by - all parents of child in 1 row [message #312735 is a reply to message #312729] Wed, 09 April 2008 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can.

Post a test case: create table and insert statements.
Also post the result you want with these data.

You still don't answer my question about your version.

Regards
Michel
Re: Oracle Connect by - all parents of child in 1 row [message #312742 is a reply to message #312735] Wed, 09 April 2008 12:46 Go to previous messageGo to next message
san_gee
Messages: 5
Registered: April 2008
Junior Member
Thanks. apologies for missing out the entire message. I will get all the insert scripts and update this thread soon. I am a BO reporting person and have only read-only access to DB and have no create/insert rights. It might take a couple of days to get a scheme to do these tests. So advance apologies, if my code has some syntax errors!

9i is what we are using and migrating to 10g. So a solution can be either 9i or 10g is also fine. Thats the reason for my ambiguous versioning!
Also, I have given the sample output in my thread, if that helps.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
is the Development version

[Updated on: Wed, 09 April 2008 12:53]

Report message to a moderator

Re: Oracle Connect by - all parents of child in 1 row [message #312750 is a reply to message #312715] Wed, 09 April 2008 13:03 Go to previous messageGo to next message
san_gee
Messages: 5
Registered: April 2008
Junior Member
The scripts as you had requested. Hope this is ok.

CREATE TABLE Table1
(child number ,
childname varchar2(25),
parent number)

INSERT INTO Table1
VALUES (100,
'Films',
null);

INSERT INTO Table1
VALUES (101,
'Drama Films',
100);

INSERT INTO Table1
VALUES (102,
'Comedy Films',
100);

INSERT INTO Table1
VALUES (103,
'Reality',
NULL);

INSERT INTO Table1
VALUES (104,
'Daily Reality',
103);

INSERT INTO Table1
VALUES (105,
'Hourly Reality',
104);


expected result:
----------------
child Parent Tree
100 Films
101 Drama Films->Films
102 Comedy Films->Films
103 Reality
104 Daily Reality->Reality
105 Hourly Reality->Daily Reality->Reality
Re: Oracle Connect by - all parents of child in 1 row [message #312755 is a reply to message #312750] Wed, 09 April 2008 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't have a database now but here's a way with standard emp table in scott schema.
select ename, path
from ( select ename, path, lvl,
              max(lvl) over (partition by ename) mx
       from ( select ename, sys_connect_by_path(ename,'/') path, level lvl
              from emp
              connect by prior mgr = empno
            )
     )
where lvl = mx
order by ename

Regards
Michel
Re: Oracle Connect by - all parents of child in 1 row [message #313033 is a reply to message #312715] Thu, 10 April 2008 07:53 Go to previous messageGo to next message
san_gee
Messages: 5
Registered: April 2008
Junior Member
awesome! it works great.

I did not know about this PARTITION BY clause, and its a very useful info I learnt today. Thanks so much for the help and I appreciate you quick response.

Have a good day!

Thanks
Sangeetha
Re: Oracle Connect by - all parents of child in 1 row [message #313078 is a reply to message #313033] Thu, 10 April 2008 10:40 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
Chapter 5 SQL Functions
Section Analytic Functions

Regards
Michel
Previous Topic: Find the Less Serial Numbers in a table
Next Topic: cast RAW to number
Goto Forum:
  


Current Time: Tue Dec 06 04:12:22 CST 2016

Total time taken to generate the page: 0.07770 seconds