Query throws 'java: annotation values must be of the form 'name=value' error-CodePudding

I'm not sure why I'm receiving this error. I'm trying to get data from two separate tables in this query.

@Query("SELECT * FROM ConnectionRequest c "  
        "INNER JOIN (SELECT firstName as name FROM USER u WHERE c.userId = u.id) "  
        "WHERE c.dateTimeCompleted IS NULL", nativeQuery = true)
List<ConnectionRequest> findAllByDateTimeCompletedIsNull();

CodePudding user response：

If you are providing only a single value for a parameter named value, you can simply put the value itself inside the parentheses. However, if you are providing multiple arguments, even if one is for a parameter named value you must specify value = "text".

By the way, you should try hard to avoid nativeQuery as it bypasses both the checks and optimizations that JPQL can provide for you. In this case, you can probably let Spring Data write the entire query for you based on a method name (like findAllByUserFirstNameAndDateTimeCompletedIsNull()).

CodePudding user response：

you can't use a conditional subselect in an inner join

so do it like

SELECT c.*,u.firstName as name FROM ConnectionRequest c 
    INNER JOIN USER u ON c.userId = u.id
    WHERE c.dateTimeCompleted IS NULL

OR as subslect

    SELECT *,(SELECT firstName  FROM USER u WHERE c.userId = u.id)  as name FROM ConnectionRequest c 
    WHERE c.dateTimeCompleted IS NULL