Thanks for you reply, it helps in some cases, but I think there are more complicated cases, such as:

create table t2 (id int, name varchar(100));
insert into t2 values (1, 'abc');
insert into t2 values (2, 'xyz');
select nvl((select name from t2 where name='ab'), 'xxx');

I hope the result has one tuple:

'xxx'

but it returns 0 tuple.


On Tue, Jul 13, 2010 at 2:21 PM, Stefan Manegold <Stefan.Manegold@cwi.nl> wrote:
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.htm#i91798
>
> 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       |

------------------------------------------------------------------------------
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