What is the equivalent in Interbase for Isnull in SQL server?

[Tentacle]

Hi all

I think an example will explain my problem the best.

I have the following SQL select statement:

select (sum(sold) - sum(paid)) as duty
from clients
join sold on sold.client_id=clients.id
join paid on paid.client_id=clients.id
where clients.id=<whatever>

The obvous problem is that if a certain client never paid anything, sum(paid) returns null, so sum(sold)-sum(paid) returns null instead of sum(sold).

What I want is a function like NVL or ISNULL equivalent in Interbase or if someone knows a workaround to make my select return a 0 if the value is null. In SQL Server it would look as follows:

select (sum(sold) - isnull(sum(paid), 0)) as duty
from clients
join sold on sold.client_id=clients.id
join paid on paid.client_id=clients.id
where clients.id=<whatever>

Hope this makes sence and someone can help me out there.

Thank you everybody.

 

[Oppenhiemer]

This is not a complete solution, but you should be able to get an idea of a possible routine here...
============================================
SET TERM !! ;
CREATE PROCEDURE test_null
RETURNS (total NUMERIC(15, 2))
AS
DECLARE VARIABLE tot DECIMAL(12, 2);
BEGIN
tot = 0;
SELECT SUM(Test)
FROM test
INTO :tot;
IF (tot IS NULL) THEN
BEGIN
Total = 0;
SUSPEND;
END
ELSE
BEGIN
total = tot;
SUSPEND;
END
END;!!
============================================

Opp.

 

[saloufeng]

Use UDF !
I test this in Delphi,that's working!

function udf_isnull(Achar;Bchar)Char;cdecl;
begin
if strpas(A)='' then result:=B else result:=A;
end;

DECLARE EXTERNAL FUNCTION udf_isnull
CSTRING(256) CHARACTER SET NONE, CSTRING(256) CHARACTER SET NONE
RETURNS CSTRING(256) CHARACTER SET NONE FREE_IT
ENTRY_POINT 'udf_isnull' MODULE_NAME 'test.dll';