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