At 05:53 PM 20/09/2011, Kjell Rilbe wrote:

>Consider this SQL:
>
>select
> case when "RemovedAt" is null then 'Cur' else 'Hist' end "Category",
> count(*) "Count"
>from "Objects"
>where "ObjectKind" = 92
>group by case when "RemovedAt" is null then 'Cur' else 'Hist' end
>order by case when "RemovedAt" is null then 'Cur' else 'Hist' end
>
>Why would that SQL give me this error:
>
>SQL Message : -104
>Invalid token
>
>Engine Code : 335544569
>Engine Message :
>Dynamic SQL Error
>SQL error code = -104
>Invalid expression in the select list (not contained in either an
>aggregate function or the GROUP BY clause)
>
>I've copied and pasted the group by clause into both order by and
>select, so I think it should be alright.

It's wrong because grouping must be on an output field. You seem to have gotten yourself a little mixed up here: the field you want to group and order on is "Category" - not the values in it!

Just change it to
group by 1
order by 1

You will get the groups in order anyway, so you don't actually need the ORDER BY clause.

./heLen