Helen, Pavel,

>select Nr1, Nr2,
> case when (Nr1 is not null) and (Nr1 >= 0) then Nr1 else 0 end +
> case when (Nr2 is not null) and (Nr2 >= 0) then Nr2 else 0 end
> as NrTotal from ATable

Thank you both for replying ... this works fine.

It never occurred to me to use a CASE construct here, if looked much more
like an IF-statement, so I tried that (but that didn't work).

Is there any reason why the IF doesn't work, or is it just a design decision.

Thanks again,
Erik

BTW Helen, are you planning to update your FIREBIRD book to the latest
version ?


At 23:20 11/03/2006, you wrote:
Quite so, I missed the main point, that he wanted to count negatives as zero.

Sorry.

Helen

At 23:20 11/03/2006, you wrote:

>Hello Helen,
>
>HB> If I understand the question, this will do the job just fine:
>HB> select Nr1, Nr2, coalesce(Nr1, 0) + coalesce(Nr2, 0) as NrTotal from
>ATable
>
>The author of the question wants to get a sum of non-negative and values
>only. So, I suppose he may use CASE statement here (almost as he
>suggested in his post):
>-----
>select Nr1, Nr2,
> case when (Nr1 is not null) and (Nr1 >= 0) then Nr1 else 0 end +
> case when (Nr2 is not null) and (Nr2 >= 0) then Nr2 else 0 end
> as NrTotal from ATable
>-----
>(Erik, please refer to release notes for the CASE syntax).

----------
E.De.L.Com bvba - De Softwarebrug naar de gebruiker!
Professioneel maatwerk is goedkoper dan u denkt !

----------
Ontwikkelaars van Sitestepper - ondehoud zelf uw website : www.sitestepper.com

----------
Lambrechtshoekenlaan 211 - 2170 Merksem ( Antwerpen ) - Belgium
tel: (32)3-541 77 24 - fax: (32)3-542 49 69 - mobile: (32)475-32 99 47 -
(32)475-81 67 83
web: www.edelcom.be - www.edelcom.com - email: info@...

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