1 Reply Latest reply on Jul 24, 2013 12:48 PM by Steven Hawkins

    Error executing sql query with order by case when...

    skethire Newbie

      I am using a Teiid (version 8.3) VDB as a source for a Mondrian cube.  Modrian is issuing a query as below:

       

      select "V_DIM_JOB"."JOB_ID" as "c0"
      from "BIRTView"."V_DIM_JOB" as "V_DIM_JOB"
      group by "V_DIM_JOB"."JOB_ID"
      order by CASE WHEN "V_DIM_JOB"."JOB_ID" IS NULL THEN 1 ELSE 0 END, "V_DIM_JOB"."JOB_ID" ASC

       

      We are getting the following exception from Teiid:

       

      org.teiid.jdbc.TeiidSQLException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column CASE WHEN V_DIM_JOB.JOB_ID IS NULL THEN 1 ELSE 0 END cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY)

       

      What is wrong with the query?

       

      Thanks

        • 1. Re: Error executing sql query with order by case when...
          Steven Hawkins Master

          We are not allowing a unrelated expression (a ordering column that does not appear in the select clause) when grouping (or any non-simple select) is used.  I believe this comes from the specification although it is something that can obviously be relaxed but would require an enhancement.

           

          In this specific case we would prefer the use of an explicit null ordering:

           

          select "V_DIM_JOB"."JOB_ID" as "c0" from "BIRTView"."V_DIM_JOB" as "V_DIM_JOB" group by "V_DIM_JOB"."JOB_ID" order by "V_DIM_JOB"."JOB_ID" ASC NULLS FIRST

           

          In general you could also use inline views.

           

          Steve