Apply default filter if query returns 0 rows - Oracle Plsql

Question

Im using Oracle PLSQL. Im trying to make a join beetween two tables and make a filter from one parameter that can be 'W' (Woman) or 'M' (Man).

I have this table with a list of Jobs:

And this other one with every job translated to English or Spanish for each gender:

I want to get the job translated to both languages for the gender specified in the parameter. If there is no translation for Woman, then get translation for Man.

I tried to do this with NVL but it's not working:

select j.*, l.long_name, l.language, l.gender 
from job j
join job_lang l
on j.id = l.id_job
where j.short_name = 'Firefighter'
and nvl(l.gender, 'M') = 'M'; -- parameter

It works perfect when the parameter is 'M' but returns nothing when is 'W' because there is no translation for Woman.

I'm not sure if I can do this with a case, I tried but I couldn't find anything. Could you help me?

Thanks.

Answer 1

You could use the fetch clause with window functions for prioritization:

    select j.*, l.long_name, l.language, l.gender, 
    from job j
    join job_lang l on j.id = l.id_job
    where j.short_name = 'Firefighter'
    order by row_number() over(
        partition by j.id, l.language
        order by case when l.gender = 'M' then 0 else 1 end
    )                                  ^----- parameter
    fetch first row with ties

Answer 2

I would aggregate it before join:

select j.*, l.language, l.long_name_M, l.long_name_W
from job j
join (
      select 
         jl.id_job,jl.language
        ,max(decode(jl.gender,'M',long_name) long_name_M
        ,max(long_name)keep(dense_rank first order by decode(jl.gender,'W',1,'M',2)) long_name_W
      from job_lang jl
      group by jl.id_job,jl.language
      ) l
      on j.id = l.id_job
where j.short_name = 'Firefighter';

PS. Oracle can push predicates into group-by inline view