| Selecting data from nested tables [message #365986] |
Tue, 22 August 2000 08:59  |
Jayan P
Messages: 1
Registered: August 2000
|
Junior Member |
|
|
Hi,
I have a problem in selecting data from nested tables. I have to select from nested tables in different rows of a relational table.
I'll explain with an example. Consider a table like this
Emp_Det
Comp_Name varchar2(5),
emp_id varchar2(10),
empname varchar2(30),
empdet empdet_nt
the PKey of the table is comp_name and emp_id.
the empdet_nt is a nested table of type empdet_ty
structure of empdet_ty is as follows
empaddr varchar2(40),
empphno varchar2(10)
I will store different addr and phone nos of the employee in the nested table.
Now if the user wants to see all the phone nos of all the employess in the company 'IBS', what should be the query for the same. I tried the following but failed
select A.empphno from the(select empdet from Emp_Det where comp_name = 'IBS')A;
the query failed because the sub query returns more than one rows.
Please help me in sorting out the problem. Since I have to use the same for a report writer, I cannot use pl/sql block and cursors.
I am working on Oracle 8i in Unix (AIX4.3)
Thanks in advance
Jayan
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
