This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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
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
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
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