[MonetDB-users] how to write function like oracle nvl()?
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.
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
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.nlwrote:
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
On Tue, Jul 13, 2010 at 03:26:37PM +0800, Mike Jason wrote:
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.
I'd expect 0 tuple (i.e., an empty result0 as the result of the subquery, and thus the first argument to nvl() is empty, and not NULL ...
Stefan
On Tue, Jul 13, 2010 at 2:21 PM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
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
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
But i expect one tuple, then what can i do?
On Tue, Jul 13, 2010 at 3:57 PM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
On Tue, Jul 13, 2010 at 03:26:37PM +0800, Mike Jason wrote:
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.
I'd expect 0 tuple (i.e., an empty result0 as the result of the subquery, and thus the first argument to nvl() is empty, and not NULL ...
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....
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/
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
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
On Tue, Jul 13, 2010 at 04:17:24PM +0800, Mike Jason wrote:
But i expect one tuple, then what can i do?
Does Oracle and it's nvl() function give you one tuple in that case? According to my understanding of standard SQL semantics and Oracle's description of their nvl() function it should not ...
... basically, with the sub query returning an empty result, the function (in this case nvl()) is IMHO not (supposed to be) called at all; hence, it cannot yield any result.
You'd probably rephrase your query such that is handles an empty result of the sub query explictly, e.g., using a CASE construct or similarly ...
Stefan
On Tue, Jul 13, 2010 at 3:57 PM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
On Tue, Jul 13, 2010 at 03:26:37PM +0800, Mike Jason wrote:
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.
I'd expect 0 tuple (i.e., an empty result0 as the result of the subquery, and thus the first argument to nvl() is empty, and not NULL ...
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....
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/
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
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
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
participants (2)
-
Mike Jason -
Stefan Manegold