urgent help reqd

[Dez Max]

I have a requirement
of selecting the records in which the column
itself has a select statement(example stated below) into a ref cursor.
is it possible by using execute immediate.
declare
type emp_cur is ref cursor;
begin
Open emp_cur for
Select CompanyId,
(select locationname from location
start with locationid = l1.locationid
connect by prior parentid = locationid) country
from company,location
where company.locationid = location.locationid
order by 1;
end;
When i execute it in pl/sql it is not recognizing
the select statement used as a column. if i use
execute immediate how to select the result into
ref cursor since my final destination is ref cursor
thnks
Dez

[Mark Spritzler]

Why don't you put your select that is in the first select into your From clause, then the where clause to link the table to the result, and then get your fields. as in
Your way:
Select fielda, (select fieldb from a_table whereclaused here), from table_b
The other way
Select fielda, fieldb
from table_b b, (select fieldb from a_table whereclause here) a
WHERE a.linked_field = b.linked_field;
Just a suggestion, not sure if it will work for you.
Mark

[Dez Max]

the actual problem is i want like the below query
Select fielda, fieldb
from table_b b, (select fieldb from a_table where a_table.linked_field = b.linked_field) a
and not here
WHERE a.linked_field = b.linked_field;
but the above condition is failing telling
invalid column name for b.linked_field.
regs
Dez

[Mark Spritzler]

That's because table_b is not in the from clause of the subquery.
like
FROM b_table, (select a.fielda from a_table a, b_table b where a_table.linked_field = b.linked_field) a
Think of a subquery as a query that runs first and is encapsulated, so the query in the () needs to be able to run on it's own, so it needs b_table.
However, in Update statements like
Update a_table a
set a_field = (SELECT fieldb FROM b_table b WHERE a.linked_field = b.linked_field)
In that case both a_table and b_table are available in the subquery.
Mark