Forum OpenACS Q&A: Help needed porting a PG query to Oracle
SELECT n.node_id, i1.item_id FROM cr_items i1, cr_items i2, pa_package_root_folder_map m, site_nodes n WHERE m.folder_id = i2.item_id and i1.item_id = coalesce((select item_id from cr_revisions where revision_id = :photo_id),:photo_id) and n.object_id = m.package_id and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey) limit 1To work with Oracle. I looked at some other similar queries for inspiration but my porting attempts were not successful (resulted in no rows, which is wrong). Any suggestions?
Here is the similar query for the file storage package which uses a root folder concept as well.
select f.package_id as package_id
from fs_root_folders f,
from cr_items i1, cr_items i2, cr_revisions r
where i1.item_id = r.item_id
and r.revision_id = :revision_id
and i2.tree_sortkey <= i1.tree_sortkey
and i1.tree_sortkey i2.tree_sortkey and tree_right(i2.tree_sortkey)) as i
where f.folder_id = i.parent_id
Here is a similar Oracle query from file-storage as well
where object_id = (select r.package_id,
r.folder_id as root_folder_id
from fs_root_folders r,
(select item_id as folder_id
connect by prior parent_id = item_id
start with item_id = :folder_id) t
where r.folder_id = t.folder_id)
The point of the query is to get the URL for a photo. I didn't write it, but the person who did only put it in for Postgres. Seems to be a common theme for me these days...