'Java Spring JPA Query Error: '(',<expression>,FUNCTION or identifier expected, got '('

I'm asking the question cuz all the related posts i found are not answered

Error at line 21 Screenshot Hello guys I need some help with this Query, at line 21 I got an error that says " '(',, FUNCTION or identifier expected, got '(' " but I can't seem to find anything wrong here, when I was doing in on MySQL WorkBench scripts was working fine.. but when I transfer it to the Query it gives me that error, I've counted all the brackets they are equal so I don't really know what's going on here. I would be happy if someone manages to tell me what's wrong here ty.

Script tested at MySQL Workbench: The script actually works perfectly at MySQL but when I transfer it to Intellij it drops error :/

SELECT * FROM rooms r
WHERE (r.people >= 10 AND r.id NOT IN(SELECT rr.room_id FROM reservations rr))
OR (r.people >= 10 AND r.id IN (SELECT rr.room_id FROM reservations rr 
WHERE (rr.start_date NOT BETWEEN '2022-03-15' AND '2022-03-31' ) 
AND (rr.end_date NOT BETWEEN '2022-03-15' AND '2022-03-31')))
OR(r.people >= 10 AND r.id IN(SELECT COUNT(*) FROM reservations rr
WHERE (rr.start_date BETWEEN '2022-03-15' AND '2022-03-31') AND (rr.end_date BETWEEN '2022-03-15' AND '2022-03-31')
GROUP BY rr.room_id
HAVING COUNT(rr.room_id) < r.`count`
ORDER BY r.id))

Query From Intellij

@Query(value = "SELECT r FROM Room r " +
        "WHERE (r.people >= ?3 AND r.id NOT IN (SELECT rr.room_id FROM Reservation rr))" +
        "OR (r.people >= ?3 AND r.id IN (SELECT rr.room_id FROM Reservation rr " +
        "WHERE (rr.start_date NOT BETWEEN ?1 AND ?2) AND (rr.end_date NOT BETWEEN ?1 AND ?2))) " +
        "OR (r.people >= ?3 AND r.id IN (SELECT COUNT (rr.room_id) FROM Reservation rr" +
        "WHERE (rr.start_date BETWEEN ?1 AND ?2) AND (rr.end_date BETWEEN ?1 AND ?2)" +
        "GROUP BY rr.room_id" +
        "HAVING COUNT(rr.room_id) < r.count" +
        "ORDER BY r.id))")
javamysqlspringjpa


Solution 1:[1]

You must add a space before each end quote in the query string so it can be parsed properly,try it this way:

  @Query(value = "SELECT r FROM Room r " +
    "WHERE (r.people >= ?3 AND r.id NOT IN (SELECT rr.room_id FROM Reservation rr)) " +
    "OR (r.people >= ?3 AND r.id IN (SELECT rr.room_id FROM Reservation rr " +
    "WHERE (rr.start_date NOT BETWEEN ?1 AND ?2) AND (rr.end_date NOT BETWEEN ?1 AND ?2))) " +
    "OR (r.people >= ?3 AND r.id IN (SELECT COUNT (rr.room_id) FROM Reservation rr " +
    "WHERE (rr.start_date BETWEEN ?1 AND ?2) AND (rr.end_date BETWEEN ?1 AND ?2) " +
    "GROUP BY rr.room_id " +
    "HAVING COUNT(rr.room_id) < r.count " +
    "ORDER BY r.id))")

and maybe reconsider the parenthesis ,Hope this helps !

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 karim farhouti

Related Questions