SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

connecting department code with employee table

850050Mar 12 2012 — edited Mar 12 2012

hi all,
i am using db10g.
i have

EMPLOYEE_MASTER
EMP_CODE
EMP_NAME
EMP_DEPT
 primary key EMP_CODE and foreign key EMP_DEPT references DEPARTMENT_MASTER (primary key) DPT_CODE
DEPARTMENT_MASTER
DPT_CODE
DPT_NAME
DPT_LOC

now my question is first i have to get deparmtne name and corresponding employees.
dept A
x
y
z
dept b
a
b
c
etc..
somthing like connect by clause which i am not that much aware of?
i have written the below

SELECT 1, LEVEL, EMP_NAME, NULL, TO_CHAR(EMP_CODE)
FROM DEPARTMENT_MASTER,EMPLOYEE_MASTER
WHERE DPT_CODE = EMP_DEPT
CONNECT BY PRIOR EMP_DEPT = DPT_CODE

but i am getting 
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected

Thanks..

Edited by: GD on Mar 12, 2012 1:20 AM

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 9 2012

Added on Mar 12 2012

5 comments

583 views