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
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/http://www.cwi.nl/%7Emanegold/ | | 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