5 Replies Latest reply on Mar 12, 2012 4:37 PM by luizcf14

    Help in Query

    luizcf14

      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
          markaddleman

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

          1 of 1 people found this helpful
          • 2. Re: Help in Query
            luizcf14

            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

              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

                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

                  Thanks Steven, perfect!