A+B when B is null?

[mike777]

Hello all.
Is there any way in Interbase to add two numbers when one of the numbers is null?
In MS Access, I would say:

A+nz(B,0)

.
Any suggestions?
Many thanks.
-Mike

 

[mike777]

Let me clarify. I'm trying to do this in Select statement, so in MS Access, I would say:

select A+nz(B,0) from my table

.
Thanks.

 

[MartijnTonies]

No, InterBase doesn't have something like that.



Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[pfournier]

You could use/create a UDF for that purpose. FreeUDFLibC has a ROUND UDF (let's call it F_ROUND) that could do the job if you want NULLs to be treated as 0.

SELECT a+F_ROUND(b) FROM tablename

If you don't want/can't use UDFs, you could always use a small stored proc and use a more complicated query:

SELECT t.a+(SELECT result FROM nz(t.b, 0)) FROM tablename t

or even:

SELECT (SELECT result FROM nz(t.a+t.b, t.a)) FROM tablename t

WHERE nz is something like:

CREATE PROCEDURE NZ (
    VAL INTEGER,
    DEFVAL INTEGER)
RETURNS (
    RESULT INTEGER)
AS
BEGIN
  IF (val IS NULL) THEN
    result = defVal;
  ELSE
    result = val;
  SUSPEND;
END