oracle8.1.6 select l.list_id, l.name, nvl(su.user_count,0) as user_count, nvl(uu.users_need_confirmation,0) as users_need_confirmation, nvl(ue.users_need_email_confirmation,0) as users_need_email_confirmation, (case when l.expiration_date > sysdate then 'f' else 't' end) as expired_p, acs_permission.permission_p(l.list_id, :user_id, 'admin') as admin_p from ml_mailing_lists l, (select count(*) as user_count, m.list_id from ml_mailing_list_user_map m where m.subscribed_p = 't' group by m.list_id) su, (select count(*) as users_need_confirmation, m2.list_id from ml_mailing_list_user_map m2 where m2.confirmed_p = 'f' group by m2.list_id) uu, (select count(*) as users_need_email_confirmation, m2.list_id from ml_mailing_list_user_map m2, users u where m2.subscribed_p = 'f' and m2.user_id = u.user_id and u.email_verified_p = 'f' group by m2.list_id) ue where l.package_id = :package_id and su.list_id(+) = l.list_id and uu.list_id (+) = l.list_id and ue.list_id (+) = l.list_id and acs_permission.permission_p (l.list_id, :user_id, 'read') = 't' order by lower(l.name) select mail_class_id, name, acs_permission.permission_p(mail_class_id, :user_id, 'admin') as admin_p from ml_mail_classes where package_id = :package_id and acs_permission.permission_p (mail_class_id, :user_id, 'read') = 't' order by lower(name)