oracle8.1.6
select count(*)
from dual
where exists (select 1
from dotlrn_users
where dotlrn_users.type = :type
and upper(substr(dotlrn_users.last_name, 1, 1)) = upper(:dimension))
select count(*)
from dual
where exists (select 1
from persons
where not exists (select 1
from dotlrn_users
where dotlrn_users.user_id = persons.person_id)
and upper(substr(persons.last_name, 1, 1)) = upper(:dimension))
select dotlrn_users.user_id,
dotlrn_users.first_names,
dotlrn_users.last_name,
dotlrn_users.email,
nvl((select 'f'
from dotlrn_full_user_rels
where dotlrn_full_user_rels.rel_id = dotlrn_users.rel_id),
't') as limited_access_p,
acs_permission.permission_p(:dotlrn_package_id, dotlrn_users.user_id, 'read_private_data') as read_private_data_p,
acs_permission.permission_p(:root_object_id, dotlrn_users.user_id, 'admin') as site_wide_admin_p
from dotlrn_users
where dotlrn_users.type = :type
and upper(substr(dotlrn_users.last_name, 1, 1)) = upper(:section)
order by dotlrn_users.last_name
select persons.person_id,
persons.first_names,
persons.last_name,
parties.email,
't',
'f',
acs_permission.permission_p(:root_object_id, persons.person_id, 'admin') as site_wide_admin_p
from persons,
parties
where persons.person_id = parties.party_id
and not exists (select 1
from dotlrn_users
where dotlrn_users.user_id = persons.person_id)
and upper(substr(persons.last_name, 1, 1)) = upper(:section)
order by persons.last_name