Re: [MonetDB-users] how to write function like oracle nvl()?

13 Jul 2010

      Hi Mike,

here are a couple of alternatives (IMHO in plain standard SQL ;-)):

with 

========
create table t (a int, b string);
insert into t values (1,null);
insert into t values (null,'2 2');
select * from t;

select
  a,
  case
    when b is null then 'n.a.'
    else b
  end
from t;

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

create function nvl_string(e1 string, e2 string) returns string
begin
  case
    when e1 is null then return e2;
    else return e1;
  end case;
end;

create function nvl_int(e1 int, e2 int) returns int
begin
  if e1 is null then
    return e2;
  else
    return e1;
  end if;
end;

select a, nvl_string(b,'n.a.') from t;

select nvl_int(a,0), b from t;

drop table t;
drop function nvl_string;
drop function nvl_int;
========

I get

========
[ 1	]
[ 1	]
% sys.t,	sys.t # table_name
% a,	b # name
% int,	clob # type
% 1,	3 # length
[ 1,	NULL	]
[ NULL,	"2 2"	]
% sys.t,	sys. # table_name
% a,	ifthenelse_isnull_isnull_b # name
% int,	clob # type
% 1,	4 # length
[ 1,	"n.a."	]
[ NULL,	"2 2"	]
% sys.,	sys.t # table_name
% ifthenelse_isnull_isnull_a,	b # name
% int,	clob # type
% 1,	3 # length
[ 1,	NULL	]
[ 0,	"2 2"	]
% sys.t,	sys. # table_name
% a,	nvl_string_b # name
% int,	clob # type
% 1,	4 # length
[ 1,	"n.a."	]
[ NULL,	"2 2"	]
% sys.,	sys.t # table_name
% nvl_int_a,	b # name
% int,	clob # type
% 1,	3 # length
[ 1,	NULL	]
[ 0,	"2 2"	]
========

Hope that's what you need ...

Stefan

On Tue, Jul 13, 2010 at 01:37:00PM +0800, Mike Jason wrote:
...
Hi, I need some help on how to write some special functions that monetdb
currently doesn't have.
E.g., I want to use the oralce funtion nvl(), which is described as follows:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105....
Then, how can I write a function that has the some ability as the oralce's?
By the way, now I use MonetDB-Feb2010.
...
------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
...
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- 
| Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
| CWI,  P.O.Box 94079 | http://www.cwi.nl/~manegold/  |
| 1090 GB Amsterdam   | Tel.: +31 (20) 592-4212       |
| The Netherlands     | Fax : +31 (20) 592-4199       |