-
1. Re: Help in Query
markaddleman Mar 10, 2012 2:23 PM (in response to luizcf14)1 of 1 people found this helpfulI've never used backquote (`) as a quoting character before. Try double-quotes (")
-
2. Re: Help in Query
luizcf14 Mar 10, 2012 2:33 PM (in response to markaddleman)Thanks Mark, i've try:
SELECT "q"."idParte", "t"."idQuestao", "o"."idOpcao", count(r.idRespostaOpcao) AS "count", "a"."idAvaliacao", "g"."idGrupo", "g"."nome" FROM "tt_questao_parte" AS "q" INNER JOIN "tt_questao" AS "t" INNER JOIN "tt_opcao" AS "o" INNER JOIN "tt_resposta_opcao" AS "r" INNER JOIN "tt_avaliacao" AS "a" INNER JOIN "tt_grupo" AS "g" WHERE (q.idParte = t.idParte) AND (t.idQuestao = o.idQuestao) AND (o.idOpcao = r.idOpcao) AND (r.idAvaliacao = a.idAvaliacao) AND (a.idCampanha = "1") AND (a.idGrupo = g.idGrupo) AND (g.idGrupo = "2") and (g.idPolo = "2") GROUP BY "t"."idQuestao", "o"."idOpcao";
Apear another error :
Remote org.teiid.api.exception.query.QueryResolverException: Unrelated order by column g.idGrupo cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
Elapsed Time: 0 hr, 0 min, 0 sec, 0 ms.
-
3. Re: Help in Query
markaddleman Mar 10, 2012 3:11 PM (in response to luizcf14)Each column selected in your column list must either by included in your GROUP BY clause or be aggregated in some way (apply an aggregate function like, COUNT, MAX, MIN, etc).
-
4. Re: Help in Query
shawkins Mar 12, 2012 10:13 AM (in response to markaddleman)Luiz,
Your query does not show it, but the error indicates that an ORDER BY clause has been used. To use an unrelated order by expression (one that does not appear in the SELECT clause), we expect the query to be simple (not a union/interscect/except without a SELECT DISTINCT or GROUP BY) which is probably stricter than mysql, but in-line with the spec. You can work around this with an inline view:
select <desured cols> FROM (SELECT ... GROUP BY col1, ...) AS V ORDER BY v.col1
Steve
-
5. Re: Help in Query
luizcf14 Mar 12, 2012 4:37 PM (in response to shawkins)Thanks Steven, perfect!