At 09:59 29/01/2004 +1100, you wrote:

>...snip....
>
>No, what is happening with you is that you are getting an exception message
>from trying to pass a null to EXTRACT(). That was Claudio's fix. An
>exception is preferable to a crash. AFAIK, there is not yet any fix
>implemented to silently return null under these circumstances, but you
>should ask on fb-devel.

No Helen, sorry if I can't express my ideas clearly.... my english is bad...

I do not have problems on extract on null fields....

if I do:

commit;
drop table test1;
commit;
create table test1(id integer, adate date);
commit;
insert into test1 values(1, null);
select id, adate, extract(month from adate) from test1;

ID ADATE EXTRACT
============ =========== =======

1 <null> <null>

this WORKS OK !

if I create another table

create table test2(id integer);
insert into test2 values(2);
select
extract(month from test1.adate)
from
test2 left join
test1 on (test2.id = test1.id);

I got the error....

SQL> select
CON> extract(month from test1.adate)
CON> from
CON> test2 left join
CON> test1 on (test2.id = test1.id);

EXTRACT
=======
Statement failed, SQLCODE = -833

expression evaluation not supported
SQL>


The problem ocurrs when a field is null because of an outer join....

The reply Thomas sent, was EXACTLY what happens with me... I will go to
firebird-devel and asks Claudio...

> >I take another look on source forge bug tracker and did not found anithing
> >relevant...
> >
> >Did some googling with no sucess...
> >
> >But as you said the fellow developers knows what is happening... So I think
> >this will be fixed when they have time and oportunity... I think before 1.5
> >final release... I have considered it a bug, but post it here to see if
> >someone have knowledge about it... And know some workaround...
>
>I guess the only workarounds for Fb 1.0.3 would be to use a selectable SP,
>so that you can intercept the nulls and deal with them before calling
>EXTRACT()...or to use correlated subqueries instead of the left join.
>
>/heLen

I will try...

Thank you very much for spent your time with this, and sorry if I couldn't
make clear what is happening with me...



Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004


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