Oracle SQL- Writing case if inside the where clause

Question

I am writing a query to fetch Transfer outs for employees. That means the row just prior to a row wich the action 'Transfer'. Some employees have a Transfer action and another action on the same date. But my query fetches the date that is < the Transfer action date. I want to write some thing like this:-

select * 
from per_all_assignments_m paam 
where paam.effective_start_date= 
      (case if (paam.effective_start_date=
                (select max(paam1.effective_start_date) 
                 from per_all_assignments_m paam1 
                 where paam1.person_id=paam.person_id and
                       paam.assignment_id=paam1.assignment_id and
                       paam1.action_code <> 'TRANSFER' and
                       paa1.effective_latest_change='N' )
                ) 
            then paam.effective_start_date    
            else (paam.effective_start_date=
                  (select max(paam1.effective_stat_date) 
                   from per_all_assignments_m paam1 
                   where paam.person_id=paam1.person_id and
                         paam.assignment_id=paam1.assignment_id and
                         paam1.effective_start_date < 
                         (select max(paam2.effective_start_date) 
                          from per_all_assignments_m paam2 
                          where paam2.action_code='TRANSFER' and
                                paam1.person_id = paam2.person_id and
                                paam1.assignment_id=paam2.assignment_id)
                  )
                 )
      )

Is it possible in Oracle?

Answer 1

Yes, it is possible

SELECT
  *
FROM
  per_all_assignments_m paam
WHERE
  paam.effective_start_date =
    (
      CASE
        WHEN
          paam.effective_start_date =
            (
              SELECT
                MAX(paam1.effective_start_date)
              FROM
                per_all_assignments_m paam1
              WHERE
                paam1.person_id = paam.person_id AND
                paam.assignment_id = paam1.assignment_id AND
                paam1.action_code <> 'TRANSFER' AND
                paa1.effective_latest_change = 'N'
            )
        THEN paam.effective_start_date
        ELSE
          (
            SELECT
              MAX(paam1.effective_stat_date)
            FROM
              per_all_assignments_m paam1
            WHERE
              paam.person_id = paam1.person_id AND
              paam.assignment_id = paam1.assignment_id AND
              paam1.effective_start_date <
              (
                SELECT
                  MAX(paam2.effective_start_date)
                FROM
                  per_all_assignments_m paam2
                WHERE
                  paam2.action_code = 'TRANSFER' AND
                  paam1.person_id = paam2.person_id AND
                  paam1.assignment_id = paam2.assignment_id
              )
          )
       END -- closing END for case when..
    )

Answer 2

There should be END before last ) in the previous select request