Variable in Mysql


#1

Hi guys!
When I try to use variables in a dashboard of a MYSQL database I get the error below:

java.sql.SQLSyntaxErrorException: (conn=6288616) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where ass.TS_MOVIMENTACAO = CAST(convert_tz(‘2019-02-20T03:00:00.000’, ‘+00:00’,’ at line 5

//
SELECT cli.nome_orgao, COUNT(*) FROM assinaturas ass
INNER JOIN cliente cli ON cli.id = ass.id_cliente
GROUP BY cli.nome_orgao
where ass.TS_MOVIMENTACAO = {{periodo_movimentacao}}
//

How to solve it?


#2

@LOURIVAL
Because your GROUP BY is positioned before WHERE.

SELECT cli.nome_orgao, COUNT(*) FROM assinaturas ass
INNER JOIN cliente cli ON cli.id = ass.id_cliente
WHERE ass.TS_MOVIMENTACAO = {{periodo_movimentacao}}
GROUP BY cli.nome_orgao

#3

Hi @flamber, muito obrigado!
Fui testando as variaveis e esqueci de ajustar o codigo sql.