Postgre query error: ERROR: syntax error at or near "WHERE"

Question

Postgre query error: ERROR: syntax error at or near "WHERE"

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I was working with mysql on this project and the query worked normally. At the moment, I'm migrating to postgresql. However, the query stopped working and returns the following error:

ERROR: syntax error at or near "WHERE"

My code

@Query(value = "SELECT b.nome FROM escala a INNER JOIN medico b " 
                +"INNER JOIN ordem c ON c.medico_crm = b.crm AND a.id = c.escala_id " 
                +"WHERE month(a.data) = :mes AND year(a.data) = :ano AND "
                + "b.categoria = :categoria group by b.nome", nativeQuery=true)
Set<String> getNomeMedicos(@Param("mes") int mes, @Param("ano") int ano, @Param("categoria") String categoria);

sql postgresql

asked by anonymous 15.12.2018 / 12:36

2 answers

EFD-Reinf XML Signature - "Reference Element malformed." Empty List (Custom) - Android Studio

score 0 · Answer 1

The Month and Year commands are MySQL operators

Postgres has a command called

EXTRACT (what from FROM)

So, I think your code should look like this:

@Query(value = "SELECT b.nome FROM escala a INNER JOIN medico b " 
            +"INNER JOIN ordem c ON c.medico_crm = b.crm AND a.id = c.escala_id " 
            +"WHERE EXTRACT(MONTH FROM a.data) = :mes AND EXTRACT(YEAR FROM a.data) = :ano AND "
            + "b.categoria = :categoria group by b.nome", nativeQuery=true)
Set<String> getNomeMedicos(@Param("mes") int mes, @Param("ano") int ano, @Param("categoria") String categoria);

This command is responsible for getting date values where the first parameter is what you want to get and the second from where you want to get. Above I have illustrated the first parameter by calling it oQue and the second of deQuem .

score 0 · Answer 2

I managed to resolve. I'll explain to anyone who has the same problem. There was no syntax error, but the query structure was wrong. Above, note that the select is referring to the medical table b, however the from is of the table scale a. To get clearer, follow the corrected code:

SELECT a.name from doctor to INNER JOIN order b INNER JOIN scale c ON a.crm = b.medico_crm AND b.escala_id = c.id WHERE day (c.data) =: day AND month (c.data) =: month AND year (c.data) =: year AND c.turno =: shift;