Update of /cvsroot/monetdb/sql/src/test
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv8669
Modified Files:
coalesce.sql
Log Message:
Improve the coalesce test to cover more functionality of this function.
The coalesce is now tested in the where clause.
Note: In the where clause it is not supported.
If the coalesce contains more than two arguments, I get an error complaining
about the
converstion to string of the third element which in this case is the string
'unknown';
Index: coalesce.sql
===================================================================
RCS file: /cvsroot/monetdb/sql/src/test/coalesce.sql,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -d -r1.2 -r1.3
--- coalesce.sql 20 Dec 2007 08:46:55 -0000 1.2
+++ coalesce.sql 2 Jan 2008 13:08:41 -0000 1.3
@@ -4,9 +4,42 @@
insert into test values (1,20,'a');
insert into test(id,age) values (2,26);
insert into test(id,name) values (3,'c');
+insert into test(id) values (3);
select avg(coalesce(age,38)) from test;
+
+--detect the NULL value for column name
select id, coalesce(name,'unknown') from test;
+
+--detect the NULL value for column age
+select id, coalesce(age,'unknown') from test;
+
+--detect the NULL value for column age and name
+select id, coalesce(name, age, 'unknown') from test;
+
+
+delete from test;
+insert into test(id) values (3);
+
+select id, coalesce(name, age, 'unknown') from test;
+
drop table test;
SELECT COALESCE(NULL,'x');
+
+---Coalesce in the where clause:
+create table test (id int,
+ age int,
+ name varchar(20));
+insert into test values (1,20,'a');
+insert into test(id,age) values (2,26);
+insert into test(id,name) values (3,'c');
+insert into test(id) values (3);
+
+--select only the id where the name is unknown
+select id, name, age from test where coalesce(name,'unknown') LIKE 'unknown';
+
+--select the name where the id > age
+select id, name, age from test where coalesce(id, 0) < coalesce(age, 1) and
coalesce(name,'unknown') LIKE 'unknown';
+
+drop table test;
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
