wood burning stoves*
The moose likes Oracle/OAS and the fly likes urgent help reqd Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "urgent help reqd" Watch "urgent help reqd" New topic
Author

urgent help reqd

Dez Max
Ranch Hand

Joined: Oct 01, 2002
Posts: 41
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Dez Max
Ranch Hand

Joined: Oct 01, 2002
Posts: 41
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: urgent help reqd
 
Similar Threads
Statement execute method fails
Run dynamic sql in stored procedure problem
What is wrong with this procedure?
Closing cursor in a stored procedure when invoked from a callable statement
Adding blockID into select box from database by getting locationID from another select box