Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Strange behavior in GROUP BY
On Nov 29, 9:03 am, vezerid <veze..._at_act.edu> wrote:
> 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
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 Received on Thu Nov 29 2007 - 09:40:27 CST