Forum OpenACS Q&A: Help needed porting a PG query to Oracle

I have not worked much with the tree/sortkey stuff we have for Postgres, which is a liability when trying to convert this query:
        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 1
To 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?
Posted by Dave Bauer on
What is this query trying to accomplish? I don't understand the tree sortkey part of this query if you already know which photo you want. I guess its trying to find the root folder that the photo is assoicated with?

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,
(select i2.parent_id
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

Posted by Dave Bauer on
Silly me! You need to port to Oracle. I should read, or not post so early.

Here is a similar Oracle query from file-storage as well

select site_node.url(node_id)
from site_nodes
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
from cr_items
connect by prior parent_id = item_id
start with item_id = :folder_id) t
where r.folder_id = t.folder_id)

Posted by Janine Ohmer on
Thanks, Dave, I'll see if I can sort it out from that. If not, I'll post again.

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...