Home » SQL & PL/SQL » SQL & PL/SQL » Instead of Connect by prior
Instead of Connect by prior [message #395000] Tue, 31 March 2009 00:28 Go to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
Hi.

let us imagine the commonly used emp table which contains

empno,ename,mgr_no, sal etc...

to obtain manager-emp relationship we can use connect by prior.

but instead of that is there any way we can obtain the same?
for example can we use subquery or join like anything or rank() to obtain the same result?

Re: Instead of Connect by prior [message #395001 is a reply to message #395000] Tue, 31 March 2009 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
HUH?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Instead of Connect by prior [message #395003 is a reply to message #395000] Tue, 31 March 2009 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
krithi86 wrote on Tue, 31 March 2009 07:28
but instead of that is there any way we can obtain the same?
for example can we use subquery or join like anything or rank() to obtain the same result?

The problem with hierarchical queries is that you don't know upfront how long the chain will be. This makes it impossible/undoable (*) to use a "normal" statement (without the connect by)
Why do you want such a thing? Maybe if you tell us your real problem we can help.


(*) ok, never say something is impossible on OraFAQ, or you'll be falsified within no time..
Re: Instead of Connect by prior [message #395008 is a reply to message #395003] Tue, 31 March 2009 00:52 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
actually, i know it is very easy that we can use connect by prior n do.

but tis is a ready made answer. am tryin to find some logic so that it can be achieved without connect by prior.
Re: Instead of Connect by prior [message #395023 is a reply to message #395008] Tue, 31 March 2009 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Connect by" is the only recursive operator to get hierarchical result. You can't achieve this without it in SQL, you have to use PL/SQL.

Regards
Michel

[Updated on: Tue, 31 March 2009 02:05]

Report message to a moderator

Re: Instead of Connect by prior [message #395040 is a reply to message #395023] Tue, 31 March 2009 02:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you know for certain that your hierarchy is only n levels deep, you could outer join n copies of the table together but CONNECT BY is the way to do this.

Why do you ask?
Re: Instead of Connect by prior [message #395071 is a reply to message #395040] Tue, 31 March 2009 04:22 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
hey i am supposed to use PL/SQL only. so please let me know how to achieve this.
Re: Instead of Connect by prior [message #395074 is a reply to message #395071] Tue, 31 March 2009 04:27 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Show us what you have tried. I'm sure that someone will jump in to assist if you show that you have made some attempt rather than just hoping that we would do your (home)work for you.
Re: Instead of Connect by prior [message #395082 is a reply to message #395071] Tue, 31 March 2009 04:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
krithi86 wrote on Tue, 31 March 2009 11:22
hey i am supposed to use PL/SQL only. so please let me know how to achieve this.

Wrap your query in an anonymous PL/SQL block and use dbms_output to print the lines. Then you did it in PL/SQL.
Re: Instead of Connect by prior [message #395094 is a reply to message #395074] Tue, 31 March 2009 05:05 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
Fine. here is what i tried. but it is showing redundent data and also drills only to 2 levels.

create or replace
FUNCTION  K_HIER(p_mgrno NUMBER) RETURN VARCHAR2 AS

v_abc number:=1;
v_a number;

CURSOR parent_cursor(p_cc number) IS 
    SELECT  empno FROM   emp where empno=p_cc;
CURSOR parent_child_hier(p_c_mgrno number) is 
    SELECT mgr ,empno,ename FROM emp WHERE  mgr = p_c_mgrno;
BEGIN
dbms_output.put_line('Mananger'||' '||p_mgrno);
dbms_output.put_line('mgr'||' '||'empno');

FOR i IN parent_cursor(p_mgrno) LOOP
v_a:=i.empno;

while(v_abc>0)loop
begin
     select count(*) into v_abc from emp where mgr=v_a;
  if v_abc>1 then
     FOR j IN parent_child_hier(i.empno) LOOP
     dbms_output.put_line(j.mgr||' '||j.ename);
     v_a:=j.empno;
end loop;
  end if;
end;
end loop;
end loop;
END K_HIER;

Edited to add code tags

[Updated on: Tue, 31 March 2009 05:07] by Moderator

Report message to a moderator

Re: Instead of Connect by prior [message #395114 is a reply to message #395094] Tue, 31 March 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How have you any answer?
SQL> create or replace
  2  FUNCTION  K_HIER(p_mgrno NUMBER) RETURN VARCHAR2 AS
  3  
  4  v_abc number:=1;
  5  v_a number;
  6  
  7  CURSOR parent_cursor(p_cc number) IS 
  8      SELECT  empno FROM   emp where empno=p_cc;
  9  CURSOR parent_child_hier(p_c_mgrno number) is 
 10      SELECT mgr ,empno,ename FROM emp WHERE  mgr = p_c_mgrno;
 11  BEGIN
 12  dbms_output.put_line('Mananger'||' '||p_mgrno);
 13  dbms_output.put_line('mgr'||' '||'empno');
 14  
 15  FOR i IN parent_cursor(p_mgrno) LOOP
 16  v_a:=i.empno;
 17  
 18  while(v_abc>0)loop
 19  begin
 20       select count(*) into v_abc from emp where mgr=v_a;
 21    if v_abc>1 then
 22       FOR j IN parent_child_hier(i.empno) LOOP
 23       dbms_output.put_line(j.mgr||' '||j.ename);
 24       v_a:=j.empno;
 25  end loop;
 26    end if;
 27  end;
 28  end loop;
 29  end loop;
 30  END K_HIER;
 31  /

Function created.

SQL> select K_HIER(null) from dual;
select K_HIER(null) from dual
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MICHEL.K_HIER", line 29


Mananger
mgr empno
Re: Instead of Connect by prior [message #395150 is a reply to message #395094] Tue, 31 March 2009 08:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You only go one level deep. You never fetch a manager's manager.
Re: Instead of Connect by prior [message #395152 is a reply to message #395150] Tue, 31 March 2009 08:42 Go to previous messageGo to next message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
Yes. i know that.

but i coundn't find how to alter the query to get manager's manager
Re: Instead of Connect by prior [message #395158 is a reply to message #395152] Tue, 31 March 2009 08:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
think about what you have to do:
Bottom up
1. Fetch a row.
2. get the manager id for that employee.
3. fetch the row where the employee id equals the manager id that you have just collected.
4. (2) get the manager id for that employee.
5. (3)fetch the row where the employee id equals the manager id that you have just collected.
6. (2) get the manager id for that employee.
7. (3)fetch the row where the employee id equals the manager id that you have just collected.
etc etc until you reach an employee where there is no manager id (i.e. the Big Boss)

Top Down
1. Fetch a row
2. Get the employee id
3. Fetch all employees who have that employee id as a manager id
then for each of those employees
4. (2) Get the employee id
5. (3)Fetch all employees who have that employee id as a manager id
then for each of those employees
...
Now, when you can do all of this using CONNECT BY, tell me why you want to do it without

[Updated on: Tue, 31 March 2009 08:55]

Report message to a moderator

Re: Instead of Connect by prior [message #395251 is a reply to message #395158] Tue, 31 March 2009 22:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Although using "connect by prior" is the only recursive pure sql method, you can use a recursive procedure. A recursive procedure is one that calls itself. I have provided a brief demo below showing first the usual sql method, then a method using a recursive pl/sql procedure that produces the same results.

-- the usual simple way using sql:
SCOTT@orcl_11g> COLUMN ename FORMAT A15
SCOTT@orcl_11g> SELECT empno,
  2  	    RPAD (LPAD (' ', LEVEL * 2) || ename, 15) AS name,
  3  	    mgr
  4  FROM   emp
  5  START  WITH mgr IS NULL
  6  CONNECT BY PRIOR empno = mgr
  7  ORDER  SIBLINGS BY ename
  8  /

     EMPNO NAME                   MGR
---------- --------------- ----------
      7839   KING
      7698     BLAKE             7839
      7499       ALLEN           7698
      7900       JAMES           7698
      7654       MARTIN          7698
      7844       TURNER          7698
      7521       WARD            7698
      7782     CLARK             7839
      7934       MILLER          7782
      7566     JONES             7839
      7902       FORD            7566
      7369         SMITH         7902
      7788       SCOTT           7566
      7876         ADAMS         7788

14 rows selected.


-- the unnecessarily harder way using pl/sql:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE k_hier
  2    (p_mgrno IN NUMBER,
  3  	p_level IN NUMBER DEFAULT 1)
  4  AS
  5  BEGIN
  6    FOR r IN
  7  	 (SELECT empno, ename
  8  	  FROM	 emp
  9  	  WHERE  NVL (mgr, 0) = NVL (p_mgrno, 0)
 10  	  ORDER  BY ename)
 11    LOOP
 12  	 DBMS_OUTPUT.PUT_LINE
 13  	   (LPAD (r.empno, 10) || ' ' ||
 14  	    RPAD (LPAD (' ', p_level * 2) || r.ename, 15) || ' ' ||
 15  	    LPAD (p_mgrno, 10));
 16  	 k_hier (r.empno, p_level + 1);
 17    END LOOP;
 18  END k_hier;
 19  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SET SERVEROUTPUT ON FORMAT WRAPPED
SCOTT@orcl_11g> EXEC k_hier (NULL)
      7839   KING
      7698     BLAKE             7839
      7499       ALLEN           7698
      7900       JAMES           7698
      7654       MARTIN          7698
      7844       TURNER          7698
      7521       WARD            7698
      7782     CLARK             7839
      7934       MILLER          7782
      7566     JONES             7839
      7902       FORD            7566
      7369         SMITH         7902
      7788       SCOTT           7566
      7876         ADAMS         7788

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>

Re: Instead of Connect by prior [message #395260 is a reply to message #395251] Tue, 31 March 2009 23:42 Go to previous message
krithi86
Messages: 19
Registered: June 2008
Location: India
Junior Member
Ohhh... Millions of Thanks
Previous Topic: DB Link Job error
Next Topic: ORA-30926: unable to get a stable set of rows in the source tables
Goto Forum:
  


Current Time: Wed Dec 07 12:42:50 CST 2016

Total time taken to generate the page: 0.06550 seconds