oracle8.1.6 select cf.label as folder_name, cf.description as folder_description, decode(acs_permission.permission_p(cf.folder_id, :user_id, 'admin'),'t',1,0) as admin_p, decode(acs_permission.permission_p(cf.folder_id, :user_id, 'pa_create_folder'),'t',1,0) as subfolder_p, decode(acs_permission.permission_p(cf.folder_id, :user_id, 'pa_create_album'),'t',1,0) as album_p, decode(acs_permission.permission_p(cf.folder_id, :user_id, 'write'),'t',1,0) as write_p, decode(acs_permission.permission_p(ci.parent_id, :user_id, 'write'),'t',1,0) as parent_folder_write_p, (select count(*) from dual where exists (select 1 from cr_items ci2 where ci2.parent_id = cf.folder_id)) as has_children_p, decode(acs_permission.permission_p(cf.folder_id, :user_id, 'delete'),'t',1,0) as folder_delete_p from cr_folders cf, cr_items ci where ci.item_id = cf.folder_id and ci.item_id = :folder_id select * from ( select i.item_id, r.title as name, r.description, 'Album' as type, 1 as ordering_key from cr_items i, cr_revisions r where i.content_type = 'pa_album' and i.parent_id = :folder_id and i.live_revision = r.revision_id UNION ALL select i.item_id, f.label as name, f.description, 'Folder', 0 from cr_items i, cr_folders f where i.parent_id = :folder_id and i.item_id = f.folder_id ) where acs_permission.permission_p(item_id, :user_id, 'read') = 't' order by ordering_key,name