Re: alias name in a group by does not work

Christian Förster wrote:
> Like the topic said:
>
> select
> col1,
> case when col1 > col3 then 'YES' else 'NO' end as STATUS,
> col2
> from testtable
> group by col1, STATUS;
>
> does not work!?
> It results in: ORA-00904 unknown column
>
> The only way to do it:
>
> select
> col1,
> case when col1 > col3 then 'YES' else 'NO' end as STATUS,
> col2
> from testtable
> group by col1, case when col1 > col3 then 'YES' else 'NO' end;
>
> Is there a way to use the alias name in the group by part anyway?
> (In other db, i.e. sybase this is no problem ;-) )
>
> Cheers
> Christian

not the ONLY way:
select
  col1,
  case when col1 > col3 then 'YES' else 'NO' end as STATUS,   col2
from testtable
group by col1, 2;

But what version of ORACLE are you using?

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost