NVL function implementation

[Hardie82 <th...@yahoo.de>]

Hi,

I want to implement the NVL-function for my derby-db to use sql-statement
once written for an informix-db. Therefor I began to write a function class
with methods like the following code:

  public static String nvl(String arg1, String arg2)
  {
    if(arg1 == null)
      return arg2;
    return arg1;
  }

I also create a function in sql:

CREATE FUNCTION NVL(arg1 VARCHAR(100), arg2 VARCCHAR(100)) returns
VARCHAR(100) language java 
external name 'mypackage.testdb.DerbyCustomFunctions.nvl' parameter style 
java no sql;

That works fine for statements with varchar-argument and length 100. But
what about other data-types like INTEGER or DATE? Have I to implement
methods for all data-type combination or is there a better way? Something
like a data-type converter for derby? I thought to get informations for
implementation from the nullif-function but can't find it in the derby-code.
-- 
View this message in context: http://old.nabble.com/NVL-function-implementation-tp27837153p27837153.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: NVL function implementation

[Rick Hillegas <Ri...@Sun.COM>]

Hello,

If you want to do this with a user-defined function, then you will need 
to declare a separate function for each datatype you need to handle.

You might be able to use a CASE expression for this problem. It's a 
little more verbose than using a function. Here is an example:

connect 'jdbc:derby:memory:derby;create=true';

create table t( a int, b int, c date, d date );
insert into t( b, d ) values ( 1, date('1994-02-23') );

select
   case when a is null then b else a end,
   case when c is null then d else c end
from t;

Hope this helps,
-Rick


Hardie82 wrote:
> Hi,
>
> I want to implement the NVL-function for my derby-db to use sql-statement
> once written for an informix-db. Therefor I began to write a function class
> with methods like the following code:
>
>   public static String nvl(String arg1, String arg2)
>   {
>     if(arg1 == null)
>       return arg2;
>     return arg1;
>   }
>
> I also create a function in sql:
>
> CREATE FUNCTION NVL(arg1 VARCHAR(100), arg2 VARCCHAR(100)) returns
> VARCHAR(100) language java 
> external name 'mypackage.testdb.DerbyCustomFunctions.nvl' parameter style 
> java no sql;
>
> That works fine for statements with varchar-argument and length 100. But
> what about other data-types like INTEGER or DATE? Have I to implement
> methods for all data-type combination or is there a better way? Something
> like a data-type converter for derby? I thought to get informations for
> implementation from the nullif-function but can't find it in the derby-code.
>   

Re: NVL function implementation

[Knut Anders Hatlen <Kn...@Sun.COM>]

Hardie82 <th...@yahoo.de> writes:

> Hi,
>
> I want to implement the NVL-function for my derby-db to use sql-statement
> once written for an informix-db. Therefor I began to write a function class
> with methods like the following code:
>
>   public static String nvl(String arg1, String arg2)
>   {
>     if(arg1 == null)
>       return arg2;
>     return arg1;
>   }
>
> I also create a function in sql:
>
> CREATE FUNCTION NVL(arg1 VARCHAR(100), arg2 VARCCHAR(100)) returns
> VARCHAR(100) language java 
> external name 'mypackage.testdb.DerbyCustomFunctions.nvl' parameter style 
> java no sql;
>
> That works fine for statements with varchar-argument and length 100. But
> what about other data-types like INTEGER or DATE? Have I to implement
> methods for all data-type combination or is there a better way? Something
> like a data-type converter for derby? I thought to get informations for
> implementation from the nullif-function but can't find it in the derby-code.

You could use the COALESCE function defined in the SQL standard. It does
more or less the same thing as NVL. See here:
http://db.apache.org/derby/docs/dev/ref/rreffunccoalesce.html

-- 
Knut Anders