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 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/ | | 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/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |

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

...

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

...

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

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