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

On Nov 29, 9:03 am, vezerid <veze... (AT) act (DOT) edu> wrote:
It's your 'formula' that's suspect. It should be:

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','')));


'NUMBEROFAIS'||(LENGTH(ENAME)-LENGTH(REPLACE(ENAME,'A', SUM(SAL)
------------------------------------------------------- ----------
number of A is 1 10350
number of A is 0 17575
number of A is 2 1100



David Fitzjarrell

Thanks David. I can't believe I messed up the paste so bad...

Kostis

On Nov 29, 5:40 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote: