Forum OpenACS Q&A: Re: Tricky query port (pg -> oracle)

Collapse
9: Re: Tricky query port (pg -> oracle) (response to 3)
Posted by Vinod Kurup on 05/15/03 03:29 AM
Weird... I have no idea why you can't use subqueries as columns in PL/SQL. If you still want to make it all 1 query and you don't mind making it *really* ugly, you can use this: 

select                                                                      
  'Revision ' ||
  max(content_revision.get_number(r1.revision_id)) || ' of ' || 
  count(r2.revision_id) || ' for item: ' ||
  max(content_item.get_title(r1.item_id))               
  into v_text                                                                 
from cr_revisions r1, cr_revisions r2                                       
where r1.revision_id = revision_name.revision_id 
  and r1.item_id = r2.item_id;
Collapse
10: Re: Tricky query port (pg -> oracle) (response to 9)
Posted by Sebastiano Pilla on 05/15/03 09:51 AM
Weird... I have no idea why you can't use subqueries as columns in PL/SQL.
AFAIK, in Oracle 8i PL/SQL and SQL use different parsers, and the PL/SQL parser lacks some of the features of the SQL one. The 2 parsers should be synchronized from Oracle 9i onwards.

For the original problem, maybe executing the problematic query with the 'execute immediate' construct may help: documentation for 'execute immediate' in Oracle 8i can be found here. Please let me know if it solves your problem.