JPA Subquery fails

Does anyone know why it fails?

@Query("SELECT CASE WHEN COUNT(x) > 0 THEN true ELSE false END FROM City a WHERE x.parking= :parking AND x.section= :section AND x.district IN (SELECT uc FROM DistrictCity uc WHERE uc.zone = :zone AND :user IN uc.users)")
Boolean carsExist(String parking, Section section, String zone, User user);

City (class)

String parking
Section section
DistrictCity district

DistrictCity (class)

String Zone
List<User> users (Many to Many relation)

​x
 
@Query("SELECT CASE WHEN COUNT(x) > 0 THEN true ELSE false END FROM City a WHERE x.parking= :parking AND x.section= :section AND x.district IN (SELECT uc FROM DistrictCity uc WHERE uc.zone = :zone AND :user IN uc.users)")Boolean carsExist(String parking, Section section, String zone, User user);​City (class)​String parkingSection sectionDistrictCity district​DistrictCity (class)​String ZoneList<User> users (Many to Many relation)​

It does not give syntax problems but it gives me the error “missing expression (ORA-00936)” The problem seems to be in :user IN uc.users

Answer

I think the last bit is incorrect. This should work:

SELECT uc FROM DistrictCity uc JOIN uc.users u WHERE uc.zone = :zone AND u = :user

 
SELECT uc FROM DistrictCity uc JOIN uc.users u WHERE uc.zone = :zone AND u = :user​

So, the whole query becomes:

@Query("SELECT CASE WHEN COUNT(x) > 0 THEN true ELSE false END FROM City a WHERE x.parking= :parking AND x.section= :section AND x.district IN (SELECT uc FROM DistrictCity uc JOIN uc.users u WHERE uc.zone = :zone AND u = :user)")

 
@Query("SELECT CASE WHEN COUNT(x) > 0 THEN true ELSE false END FROM City a WHERE x.parking= :parking AND x.section= :section AND x.district IN (SELECT uc FROM DistrictCity uc JOIN uc.users u WHERE uc.zone = :zone AND u = :user)")​

Advertisement

Answer