selecting with nvl function

Question

i have this query :

 select w.worker_id,   w.lastname,pd.invalid
                             from workers w,personal_data pd
                             where pd.worker_id (+) = w.worker_id
                             and nvl(pd.invalid,'N')='Y'

i have check box (y,n), purpose of the check box is to get list with invalids if its checked and when its not checked with normal workers including invalids. With this i always get invalid list.

And with this select :

  select w.worker_id,   w.lastname,pd.invalid
                                 from workers w,personal_data pd
                                 where pd.worker_id (+) = w.worker_id
                                 and pd.invalid =nvl(:p_invalid,pd_invalid)

if checkbox is Y i get invalid workers and when is not checked (n) i get just normal workers, i need to get when is N all workers ( invalid and normal ).

Answer 1

Stop using (+) syntax! Use proper, explicit outer joins.

However, I don't think you need an outer join -- your comparison to a constant undoes it anyway. I think this does what you want:

select w.worker_id, w.lastname, pd.invalid
from workers w join
     personal_data pd
    on pd.worker_id = w.worker_id
where (:p_invalid = 'N' or pd.invalid = :p_invalid);