First of all, thanks for answering ..

but I can't use the Where clause since this will
filter out records (which I don't want).

Each number can be either
-2 = this number is not used here, use 0 as value and don't add 1 to the count
-1 = value shoud have been assigned, use 0 as value and add 1 to the count
or 0 to 10 = a value is assigned, add it to the total and add 1 to the count

So the records with values:

8 , 9 : will give a total of (8 + 9 / 2) = 85%
8 , -1 : will give a total of ( 8 + 0) /2 = 40%
8 , -2 : will give a total of (8) / 1 = 80%
(since on this record an indication is given that
the second value should not be entered, and
therefore does not contribute to the total

The real records have 10 numbers of course - on
each record a different number of these numbers
can be assigned the value -2 (no need to enter a value here)

Thanks again,
Erik

*** Reply: ***

Subject: RE: [firebird-support] NULLS in VIEW when adding values
From: nick@... ("Nick")
Newsgroups: egroups.ib-support

select Nr1, Nr2, coalesce(Nr1, 0) + coalesce(Nr2, 0) as NrTotal from ATable
where Nr1 >=3D 0 and Nr2 >=3D 0
--
Nick
-----We Solve your Computer Problems---
UNIX, C, Panther, Ingres, Interbase, Firebird - Available Shortly=20

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Erik De Laet
Sent: 11 March 2006 08:57
To: firebird-support-yahoogroups.com
Subject: [firebird-support] NULLS in VIEW when adding values


*** Original post: ***
Hi,

In a view like:

select Nr1, Nr2, Nr1 + Nr2 as NrTotal from ATable

I want to add only the numbers Nr1 and Nr2 if=20
they are not null and >=3D 0, is this possible ?

Something like:

select Nr1, Nr2,
if (Nr1 not is null) and (Nr1 >=3D 0) then Nr1 else 0 +
if( (Nr2 not is null) and (Nr2 >=3D 0) then Nr2 else 0
as NrTotal from ATable

If it is not possible in a view, is it possible in a stored procedure ?

Any help would be higly appreciated,

Thanks,
Erik



Erik De Laet - CEO E.De.L.Com bvba
Author of Sitestepper - the Web Maintenance
program. Visit the home of Sitestepper at www.sitestepper.com.
Programmer by choice and profession !

E.De.L.Com bvba - The Software bridge to the user !
Software-, hardware en internetconsultants and
developers in Delphi, Paradox and VBA (Word, Excel en Access).

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


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