with mis_idiomas
as(
select * from table (
select 'es', 1 from
sysibm.sysdummy1 union all
select 'fr', 2 from
sysibm.sysdummy1 union all
select 'en', 3 from
sysibm.sysdummy1 union all
select 'he', 4 from
sysibm.sysdummy1
) as t (idioma,
ord)
),
T2 as(
select tor.mi_clave_busqueda,
min(ord) as ord from tabla_origen tor inner join mis_idiomas
ml on tor.cod_idioma=ml.idioma group by tor.mi_clave_busqueda
)
select tor.mis_valores_buscados
from T2 inner join mis_idiomas ml on T2.ord=ml.ord inner join tabla_origen
tor on T2.mi_clave_busqueda=tor.mi_clave_busqueda and ml.idioma=tor.idioma