Re: Fwd: [News] Group By Clause Giving Trouble - Help!

In comp.databases.oracle Mr.Badri wrote:

> select to_char(max(year)), ...
> from ...
> where ...
> group by ...
>
> It gives me an "Invalid Number error". I removed the group by
>and it works.
>...I tried selecting only the year - it has blanks, but would
 

>it not be taken care of by the format statement? I understand that there
>is a non-numeric to numeric conversion that is not working, but why would
>it work without the group by?

It is not work without the group by too! It work or not work with or without 'group by' dependly of table data.

create table A(C CHAR(4), N NUMBER);

select TO_CHAR(MAX(C))
from A
where N is not null
group by N /* with or without this line */ ;

It is NOT work if table data are:

insert into a values('22 4',1);
insert into a values(' 234',1);
insert into a values('  34',2);
insert into a values('12 4',2);

But it is work if table data are:

insert into a values('2234',1);
insert into a values('13 4',1);
insert into a values('1 56',2);
insert into a values('1234',2);

But can 'year' contain blanks between figures. And what means such blanks?
Is it '0' or something else?

With best wishes

Valery Yourinsky
Zelenograd, Moscow
Russia Received on Sat May 13 1995 - 00:00:00 CEST