hi all

I was wondering if there is a better way of doing the following
There is nothing wrong with the following query, the only thing is that it involves 2 nested subqueries which i suspect would be processor intensive.

The basic problem i suspect would be a common one.
The query is suppose to pull data from one of 2 sources shift_numbers_actual(sna) or sna_adjust(sna_a). if there exist data in the sna_a table then the latest entry out of that table will be pulled out for each of entry in the sna table, if not then simply return the entry in the sna table.

select sna.id, sna.sns_id, sna.sna_id, sna.ret_id, sna.shift_number, sna.variation,
nvl((select rostered from sna_adjust where id = (select max(id) from sna_adjust where sna_id = sna.id)),rostered),
nvl((select start_time_depot from sna_adjust where id = (select max(id) from sna_adjust where sna_id = sna.id)),start_time_depot),
nvl((select finish_time_depot from sna_adjust where id = (select max(id) from sna_adjust where sna_id = sna.id)),finish_time_depot),
rula.line_number
from
shift_numbers_actual sna,
roster_user_lines_actual rula,
roster_user_shifts_actual rusa,
roster_actual ra
where ra.id = iRosterActualId
and sna.ra_id = ra.id
and rusa.rula_id = rula.id
and rula.ra_id = ra.id
and rusa.sna_id = sna.id
and trim(to_char(sna.shift_date,'DAY')) = 'SUNDAY'
order by rula.line_number, sna.start_time_depot;

Cheers

James : )