CODEWITHSUNDEEP
sqldb2
J. Doe
J. Doe

Reputation: 13033

Use table in order by case when does not work

I am running this query but I get an error

select *
from dog
order by case
             when exists(
                     select 1 from dogfood where dog.dogid = dogfood.dogid)
                 then '1'
             else '0' end;

So 2 tables, dog and dogfood which both have a dogid column. I get this error:

[42703][-206] "DOG.DOGID" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.26.14 [56098][-727] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", > SQLSTATE "42703" and message tokens "DOG.DOGID".. > SQLCODE=-727, SQLSTATE=56098, DRIVER=4.26.14

I just want to order the dog by if it has a row in dogfood. A solution would be querying the result in the select clause and refer to it in the order by clause, but I want it in the order by clause for my application. I am curious why this query isn't working, I double checked for syntax errors but I could not find any. Am I missing something obvious? I would expect I could refer to a table in the order by which I queried in the select/from clauses.

Upvotes: 1

Views: 116

Answers (1)

nfgl
nfgl

Reputation: 3202

See documentation

sort-key-expression An expression that is not simply a column name or an unsigned integer constant. The query to which ordering is applied must be a subselect to use this form of sort-key. The sort-key-expression cannot include a correlated scalar fullselect (SQLSTATE 42703) or a function with an external action (SQLSTATE 42845).

But since it is not correlated you can use IN with a fullselect

select *
from dog
order by 
  case when dog.dogid in (select dogid from dogfood)
      then '1' else '0' end;

Upvotes: 3

Related Questions