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

[skethire]

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

[shawkins]

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