5 Replies Latest reply: Mar 12, 2012 4:37 PM by Luiz Cortinhas RSS

Help in Query

Luiz Cortinhas Newbie

Hello to all, I'm banging this query, I believe that is correct because it worked in MYSQL:

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`;

 

Error Mensage:

 

Remote org.teiid.api.exception.query.QueryParserException: Lexical error: Lexical error at line 1, column 8.  Encountered: "`" (96), after : ""

 

 

Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.

 

Thanks to help from everyone.


  • 1. Re: Help in Query
    Mark Addleman Expert

    I've never used backquote (`) as a quoting character before.  Try double-quotes (")

  • 2. Re: Help in Query
    Luiz Cortinhas Newbie

    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
    Mark Addleman Expert

    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
    Steven Hawkins Master

    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
    Luiz Cortinhas Newbie

    Thanks Steven, perfect!