Search code examples
selectoracle-sqldeveloper

Oracle Sql - Discarding outer select if inner select returns null, and avoiding multiple rows

Pre-Info: In our company a person is marked * if he is actively working. And there are people who changed their departments.

For a report I use 2 tables named COMPANY_PERSON_ALL and trifm_izinler4, joining person_id field as below.

I want to discard (don't list) the row, if the first inner select returns null. And I want to prevent the second inner select returning multiple Departments.

select izn.person_id, izn.adi_soyadi, izn.company_id,
    (select a.employee_status from COMPANY_PERSON_ALL a where a.employee_status = '*' and a.person_id = izn.person_id) as Status,
    (select a.org_code from COMPANY_PERSON_ALL a where a.person_id = izn.person_id) as Department,
    izn.hizmet_suresi, izn.kalan_izin
from trifm_izinler4 izn
where  trunc(rapor_tarihi) = trunc(SYSDATE)

Can you help me how to overcome these 2 problems of inner select statements?

Solution

  • Assuming you only want to see the department from the active person record, you can just join the two tables instead of using subquery expressions, and filter on that status:

    select izn.person_id, izn.adi_soyadi, izn.company_id,
    a.employee_status as status, a.org_code as department
    izn.hizmet_suresi, izn.kalan_izin
from trifm_izinler4 izn
join company_person_all a on a.person_id = izn.person_id
where rapor_tarihi >= trunc(SYSDATE)
-- and rapor_tarihi < trunc(SYSDATE) + 1 -- probably not needed
and a.employee_status = '*'

    I've also changed the date comparison; if you compare using trunc(rapor_tarihi) then a normal index on that column can't be used, so it's generally better to compare the original value against a range. Since you're comparing against today's date you probably only need to look for values greater than midnight today, but if that column can have future dates then you can put an upper bound on the range of midnight tomorrow - which I've included but commented out.

    If a person can be active in more than one department at a time then this will show all of those, but your wording suggests people are only active in one at a time. If you want to see a department for all active users, but not necessarily the one that has the active flag (or if there can be more than one active), then it's a bit more complicated, and you need to explain how you would want to choose which to show.