Skip to content
Advertisement

Case condition in hql

What I’m trying to achieve with this query is: always do the “mother” conditional, but if there isn’t mother, then do the “dad” conditional, is this posible?

"select name from Person where idParents = :idParents and"
                        + " CASE " + 
                        "       WHEN idMother !=NULL THEN idMother = :idMother " + 
                        "       ELSE idFather = :idFather " + 
                        "   END";

Advertisement

Answer

This query:

select name 
from Person 
where idParents = :idParents 
and 1 = case
  when idMother = :idMother then 1
  when idFather = :iidFather then 1 
end

will try to join first by matching the column idMother to :idMother.
If this is not possible then a match of idFather to :idFather will be used, if it exists.

This query:

select name 
from Person 
where idParents = :idParents 
and 1 = case
  when idMother = :idMother then 1
  when idMother is null and idFather = :idFather then 1 
end

will try to join first by matching the column idMother to :idMother.
If this is not possible then, only if idMother is null, a match of idFather to :idFather will be used, if it exists.

Or maybe you want this:

select p.name 
from Person p
where p.idParents = :idParents 
and (
  p.idMother = :idMother
  or (
    p.idFather = :idFather
    and not exists (select 1 from Person where idParents = p.idParents and idMother = :idMother)
  )
)
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement