Home » SQL & PL/SQL » SQL & PL/SQL » Master Detail with Connect by (10.2.0.3)
Master Detail with Connect by [message #317354] Wed, 30 April 2008 16:47 Go to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
I have two tables Departments and Projects. I am creating a form with a tree.

Departments
id number primary key,
name varchar2(65) not null,
parent_id number references departments.id,
...

Projects
id number primary key,
name varchar2(65) not null,
dept_id number references departments.id
...

Now, I can use the connect by to get the department hierarchy easily. But for each department I would like to have the project assigned to the department at level+1.

Any idea on how to do this?

-Rob
Re: Master Detail with Connect by [message #317365 is a reply to message #317354] Wed, 30 April 2008 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> But for each department I would like to have the project assigned to the department at level+1.
>Any idea on how to do this?
by using SQL, perhaps?
Re: Master Detail with Connect by [message #317370 is a reply to message #317354] Wed, 30 April 2008 19:19 Go to previous messageGo to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
Ummmm, sarcasism. Not a good way to earn my respect.

sql with connect by on one table is easy. This is for two table master detail.

If you don't have something.intelegent to contribute then don't post.

Rob
Re: Master Detail with Connect by [message #317371 is a reply to message #317354] Wed, 30 April 2008 19:58 Go to previous messageGo to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
Here is a little more detail just to avoid confusion.

This is the type of output I am looking for.


DEPARTMENT1
|__DEPARTMENT2
| |__PROJECT1
| |__PROJECT2
|__DEPARTMENT3
|__DEPARTMENT4
. |__PROJECT3


To get the departments I would use this query. (actual query)

select 1, level, name, null, to_char(id)
from departments
start with parent_id is null
connect by prior id = parent_id;

-Rob

[Updated on: Wed, 30 April 2008 20:00]

Report message to a moderator

Re: Master Detail with Connect by [message #317372 is a reply to message #317354] Wed, 30 April 2008 20:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Ummmm, sarcasism. Not a good way to earn my respect.
As if I care about your opinion.

>But for each department I would like to have the project assigned to the department at level+1.

While you may know what you want, it is NOT clear whether you are having a problem with SELECT, INSERT, UPDATE, or DELETE.

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated above
Re: Master Detail with Connect by [message #317384 is a reply to message #317372] Wed, 30 April 2008 22:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Write a simple left-join-query to join the Department and Project - this will preserve the branch-Departements that have no Projects. Then add a CONNECT BY clause to that SQL.

Ross Leishman
Re: Master Detail with Connect by [message #317396 is a reply to message #317370] Thu, 01 May 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Ummmm, sarcasism. Not a good way to earn my respect.


Posting in Expert forum when you are not is not the good way to earn mine. As Ross said a simple join make the trick.

Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here.

Regards
Michel
Re: Master Detail with Connect by [message #317451 is a reply to message #317384] Thu, 01 May 2008 07:36 Go to previous messageGo to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
rleishman wrote on Wed, 30 April 2008 23:48
Write a simple left-join-query to join the Department and Project - this will preserve the branch-Departements that have no Projects. Then add a CONNECT BY clause to that SQL.

Ross Leishman


I tried that when I first started down this rabbit hole. Good try, but that does not solve the problem.

I found two metalink notes on this. One is a pl/sql solution. Note:104791.1 The other is using nested table Note:143731.1 Now because this is a production system, changing to nested table is not an option.

This reminded me why I stopped posting on list ten years ago. Anyone with a internet connection can declare them selfs and expert. But the noise to signal ratio around here sucks. I'm out of here, enjoy your circle jerk.

-Rob
Re: Master Detail with Connect by [message #317456 is a reply to message #317451] Thu, 01 May 2008 08:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe if you posted some code you could explain why this is not what you want.
SQL> create table departments
  2  (id number primary key
  3  , name varchar2(65) not null,
  4  parent_id number references departments (id));

Table created.

SQL> create table projects
  2  (id number primary key,
  3  name varchar2(65) not null,
  4  dpt_id number references departments (id));

Table created.

SQL> insert into departments ( id, name, parent_id) values (1, 'DEPARTMENT1', null);

1 row created.

SQL> insert into departments ( id, name, parent_id) values (2, 'DEPARTMENT2', 1);

1 row created.

SQL> insert into departments ( id, name, parent_id) values (3, 'DEPARTMENT3', 1);

1 row created.

SQL> insert into departments ( id, name, parent_id) values (4, 'DEPARTMENT4', 1);

1 row created.

SQL> insert into projects (id, name, dpt_id) values (1, 'PROJECT1', 2);

1 row created.

SQL> insert into projects (id, name, dpt_id) values (2, 'PROJECT2', 2);

1 row created.

SQL> insert into projects (id, name, dpt_id) values (3, 'PROJECT3', 4);

1 row created.

SQL> commit;

Commit complete.

SQL> select lpad(' ', (level - 1) * 4, ' ')||dpt.name||' '||pjt.name
  2  from   departments dpt
  3  ,      projects    pjt
  4  where  pjt.dpt_id(+) = dpt.id
  5  connect by dpt.parent_id = prior dpt.id
  6  start with dpt.parent_id is null
  7  order siblings by dpt.name
  8  ,      pjt.name;

LPAD('',(LEVEL-1)*4,'')||DPT.NAME||''||PJT.NAME
------------------------------------------------------------------------------------
DEPARTMENT1
    DEPARTMENT2 PROJECT1
    DEPARTMENT2 PROJECT2
    DEPARTMENT3
    DEPARTMENT4 PROJECT3

Re: Master Detail with Connect by [message #317457 is a reply to message #317456] Thu, 01 May 2008 08:21 Go to previous messageGo to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
Oh, yes. That is what I got when I started this. Thank you for the effort. There is one difference that I need. Project should be another row, not on the same row.

So, next time I'll post the code to show what is happening. Now if we can get project at level+1 on department.

ie.

. DEPARTMENT1
. |
. DEPARTMENT2
. |__PROJECT1
. DEPARTMENT2
. |__PROJECT2
. DEPARTMENT3
. |
. DEPARTMENT4
. |__PROJECT3


This is going into a Forms tree where the user can select a department or project. If the user selects a department, then they get one tab, if they select a project they get another tab.

Thanks again,
-Rob

Frank wrote on Thu, 01 May 2008 09:00
Maybe if you posted some code you could explain why this is not what you want.

SQL> select lpad(' ', (level - 1) * 4, ' ')||dpt.name||' '||pjt.name
2 from departments dpt
3 , projects pjt
4 where pjt.dpt_id(+) = dpt.id
5 connect by dpt.parent_id = prior dpt.id
6 start with dpt.parent_id is null
7 order siblings by dpt.name
8 , pjt.name;

LPAD('',(LEVEL-1)*4,'')||DPT.NAME||''||PJT.NAME
------------------------------------------------------------------------------------
DEPARTMENT1
DEPARTMENT2 PROJECT1
DEPARTMENT2 PROJECT2
DEPARTMENT3
DEPARTMENT4 PROJECT3[/code]


[Updated on: Thu, 01 May 2008 08:24]

Report message to a moderator

Re: Master Detail with Connect by [message #317459 is a reply to message #317457] Thu, 01 May 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

Regards
Michel
Re: Master Detail with Connect by [message #317466 is a reply to message #317459] Thu, 01 May 2008 08:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you either give an answer or just for once don't reply?
What's wrong with his post? The fact that he did return, even though he said he had enough of this belittling?
Re: Master Detail with Connect by [message #317472 is a reply to message #317466] Thu, 01 May 2008 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What's wrong with his post?

Er... format? No test case (from him)? Telling other to not post? I thought freedom of speech is guaranted by his constitution.

Quote:
Why don't you either give an answer?

I was thinking to do it as soon as I have a database by hand.

Regards
Michel
Re: Master Detail with Connect by [message #317475 is a reply to message #317472] Thu, 01 May 2008 09:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There was no need for him to post a testcase, since he clearly stated his desired output based on my testcase.
Nothing to format, since it contains neither code, nor a column-based output.
Re: Master Detail with Connect by [message #317480 is a reply to message #317475] Thu, 01 May 2008 09:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not sure if this covers 100% but it might get you back on track:
SQL> select level
  2  ,      name
  3  ,      lpad(' ', (level - 1) * 4, ' ')||name as result
  4  from   (select id
  5          ,      name
  6          ,      parent_id
  7          from   departments
  8          union all
  9          select null
 10          ,      name
 11          ,      dpt_id
 12          from   projects
 13         )
 14  connect by prior id = parent_id
 15  start   with parent_id is null;

     LEVEL NAME                 RESULT
---------- -------------------- --------------------
         1 DEPARTMENT1          DEPARTMENT1
         2 DEPARTMENT2              DEPARTMENT2
         3 PROJECT1                     PROJECT1
         3 PROJECT2                     PROJECT2
         2 DEPARTMENT3              DEPARTMENT3
         2 DEPARTMENT4              DEPARTMENT4
         3 PROJECT3                     PROJECT3


[Edit: added indented result to query]

[Updated on: Thu, 01 May 2008 09:24]

Report message to a moderator

Re: Master Detail with Connect by [message #317481 is a reply to message #317480] Thu, 01 May 2008 09:26 Go to previous messageGo to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
Frank,

By George I think you got it. I really appreciate the effort. I hate that feeling of being stuck. Your solution is simple and elegant.

And I have learned about the Ignore user button. Over time that should improve the noise to signal ratio.

-Rob
Re: Master Detail with Connect by [message #317482 is a reply to message #317481] Thu, 01 May 2008 09:30 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Cool!

I continued testing it and it also seems to work with deeper levels of nesting.
Not sure about the consistency of ordering though, but hey, we need to keep something for you to do Wink
Previous Topic: Running Executable file through dbms_scheduler job
Next Topic: Concat function
Goto Forum:
  


Current Time: Sun Dec 11 02:12:11 CST 2016

Total time taken to generate the page: 0.08666 seconds