What do you mean by “when the table is empty”? When no data has been
inserted into it right after it has been created for example? In that case,
you shouldn’t get any rows back, and certainly not a row where work hours is
null.



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of jvpgr
Sent: Friday, October 31, 2008 2:44 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: coalesce and case does not work in firebird
1.5 ??



--- In firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> , "Antonio Galicia"
<antonio.galicia@...> wrote:

>
> On Fri, Oct 31, 2008 at 2:27 PM, jvpgr <jvpgr@...> wrote:
>
> > I am using firebird 1.5 on an win 2003 server, it's not the first time
> > I am using coalesce and / or case and I did not notice such behaviour
> > till now
>
> I just try this:
>
> create database 'localhost:/tmp/test.fdb';
> create table resource_allocation (id integer, work_hours integer);
>
> insert into resource_allocation (id) values (1);
> insert into resource_allocation (id,work_hours) values (2,2);
> insert into resource_allocation (id) values (3);
> commit;
>
> select coalesce(work_hours, 0), work_hours,
> case when work_hours is null then 0 else work_hours end
> from resource_allocation;
>
> COALESCE WORK_HOURS CASE
> ============ ============ ============
> 0 <null> 0
> 2 2 2
> 0 <null> 0
>
>
>
> using FB 1.5
>
> SQL> show version;
> ISQL Version: LI-V1.5.1.4500 Firebird 1.5
>
> So, i don't get any strange
>
> --
> Saludos,
> PP
> -----------------------------------
> Soy Borracho, no alcohólico
> Los alcohólicos van a reuniones
>

Hi Antonio,

no, you don't get nothing strange, you get exactly what you should,
the problem is that I get only nulls in all fields when the table is
empty, to my opinion only the work_hours field should have been null
and not the coalesce & case ones too. The other fields value should
have been 0

regards,
Yannis





[Non-text portions of this message have been removed]