Home » SQL & PL/SQL » SQL & PL/SQL » Selecting data from nested tables
Selecting data from nested tables [message #365986] Tue, 22 August 2000 08:59 Go to next message
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
Re: Selecting data from nested tables [message #366056 is a reply to message #365986] Mon, 23 October 2000 05:15 Go to previous message
lakshmi
Messages: 22
Registered: July 2000
Junior Member
select n.empphno from
EMP_DET m,table(m.empdet)n
where
m.comp_name = 'IBS'

just try this out ...mail me at my yahoo id if this was useful to u...
Previous Topic: Case Sensitivity
Next Topic: Insert inside PL/SQL Procedure
Goto Forum:
  


Current Time: Thu Mar 28 20:35:38 CDT 2024