Strange behavior in GROUP BY

From: vezerid <vezerid_at_act.edu>
Date: Thu, 29 Nov 2007 07:03:00 -0800 (PST)
Message-ID: <4eb5f508-2167-4f02-8a81-2d1477244076@e10g2000prf.googlegroups.com>

Hi all,

the following query attempts to produce the total salary per the number of 'A's in employee name (scott/tiger) and it works.

select length(ename)-length(replace(ename,'A','')), sum(sal) from emp
group by length(ename)-length(replace(ename,'A',''));

The next query tries to beautify output and it does not:

select 'number of A is ' || length(ename)- length(replace(ename,'A','')), sum(sal)
from emp
group by 'number of A is ' || length(ename)- length(replace(ename,'A',''));

I get an unexpected (for me) error, which I cannot figure out:

group by 'number of A is ' || length(ename)- length(replace(ename,'A',''))

                            *

ERROR at line 3:
ORA-01722: invalid number

(asterisk appears below the second bar of concat operator ||)

Something wrong with concatenation in GROUP BY? Something else?

TIA
Kostis Vezerides Received on Thu Nov 29 2007 - 09:03:00 CST