How to implement function nvl and decode

[Daniel Lanz]

I want to implement 2 functions "nvl" and "decode" in Interbase
Both are standard functions in Oracle.

has anybody allready implemented one or both off these functions?

Definition for Decode :
http://www.univ-reunion.fr/~pretet/sql/Decoded-Expression.html
Definition for nvl : http://www.univ-runion.fr/~pretet/sql/NVL.html

Daniel

[Peter Ölund]

Hi Daniel
I have the same problems as you, this solution i found for nvl.
I still doesn't know how to solve the problems with decode.

translate NULLs into empty strings
asked in the InterBase Q&A Forum <q-and-a.tcl?topic=InterBase>
> Is there a function in interbase that will translate NULLS to empty
> string...
> Example in oracle I can do the following statement :
> SELECT EMPNO, NVL(NAME,'') FROM MYTABLE
> in this case, if the name is null it would return a empty string.
Answers
Define a stored procedure which would return 2nd argument if 1st IS NULL,
otherwise returns 1st:
SET TERM !! ;
CREATE PROCEDURE NVL_PROC (arg1 VARCHAR(200), arg2 VARCHAR(200))
RETURNS (nvl VARCHAR(200))
AS
BEGIN
IF (:arg1 IS NULL) THEN
nvl = :arg2;
ELSE
nvl = :arg1;
SUSPEND;
END !!
SET TERM ; !!
To use this procedure as SQL function you have to use LEFT JOIN, so this
Oracle syntax:
SELECT EMPNO, NVL(NAME,'') FROM MYTABLE
... should be translated to InterBase:
SELECT EMPNO, NVL FROM MYTABLE LEFT JOIN NVL_PROC(name,'') ON 1=1
This sounds tricky, but works.
uses function from UDF (trim, ...)
UDFs can't handle NULLs
AFAIK, it won't work because you cannot pass NULL to UDF (you can try, but
it won't be recognized as NULL in UDF's code) and UDFs cannot return NULLs
as the result.
second possibility:
SELECT EMPNO, (select NVL from NVL_PROC(name)) NVL FROM MYTABLE

Peter

Daniel Lanz wrote in message <8in0up$c3...@bornews.borland.com>...

[Daniel Lanz]

Thanks for your sugestion, but this can't be a solution

these nvl function should work without using Joins
an should also work with all kind of datatypes (exept blob)

Do you know any good sites on the net where I can get more Tips
on creating functions and UDFs

"Peter Ölund" <peter....@prodacapo.com> schrieb im Newsbeitrag
news:8io1mm$p7...@bornews.borland.com...

[Claudio Valderrama C.]

Any UDF returning the same parameter it has received will work as NVL.
Generic NVL and DECODE only can be implemented in the engine. UDFs have
fixed parameter number and types.

C.
--
---------
Claudio Valderrama C.
IT Engineer - Independent consultant - CHILE
Owner of the Interbase WebRing
http://members.tripod.com/cvalde
(No support questions by e-mail, please. Thank you.)