testrun/hsqldb/TestSelfCasewhen.txt

-- TESTS FOR CASE AND SIMILAR OPERATIONS
--
drop table testcase if exists;
create table testcase(id integer, data varchar(10), filler varchar(10));
insert into testcase values(100,'xxxx',null);
insert into testcase values(200,'yyyy',null);
insert into testcase values(300,null,null);
/*c2*/select id from testcase where data is not null;
/*r300*/select id from testcase where data is null;
/*rNULLVALUE*/select ifnull(data,'NULLVALUE') from testcase where data is null;
/*rNULLVALUE*/select case data when 'xxxx' then 'X' else 'NULLVALUE' end from testcase where data is null;
SELECT CASE data WHEN 'xxxx' THEN 'X' ELSE (CASE data WHEN 'yyyy' THEN 'Y' ELSE (CASE data WHEN 'zzzz' THEN 'Z' ELSE 'NOTFOUND' END) END) END FROM testcase
SELECT CASE data WHEN 'xxxx' THEN 'X' WHEN 'yyyy' THEN 'Y' WHEN 'zzzz' THEN 'Z' ELSE 'NOTFOUND' END FROM testcase;
/*rALLNULL*/SELECT COALESCE (filler, data, 'ALLNULL') FROM testcase WHERE id = 300;
/*r600.0E0*/select cast (sum(id) as double) from testcase;
/*r600*/select coalesce(sum(id), 0) from testcase;
/*r600*/select abs(coalesce(sum(id), 0)) from testcase;
/*r1*/select case when 1 <0 then sum(10) else 1 end as label from testcase;
/*r30*/select case when 1 < 0 then 1 else sum(10) end as label from testcase;
drop table testcase2 if exists;
create table testcase2(id integer, data varchar(10), filler varchar(10), datecol date);
/*rNULL*/select cast (sum(id) as double) from testcase2;
/*r0*/select coalesce(sum(id), 0) from testcase2;
/*r0*/select abs(coalesce(sum(id), 0)) from testcase2;
-- should result in DATE type
/*r2005-10-25*/select coalesce(max(datecol), DATE'2005-10-25') from testcase2;
-- error as date type is not comparable to character
/*e*/select coalesce(max(datecol), '2005-10-25') from testcase2;
drop table test if exists;
create table test (sel int, name1 varchar(3), name2 varchar(3));
insert into test (sel, name1, name2) values (0, 'foo', 'bar')
insert into test (sel, name1, name2) values (1, 'baz', 'foo')
insert into test (sel, name1, name2) values (1, 'foo', 'qux')
select coalesce(a.name1, a.name2) as name,count(a.sel) as counter from test a
 group by coalesce(a.name1, a.name2)
select case when a.sel=1 then a.name2 else a.name1 end as name,
 count(a.name1) as counter from test a group by case when a.sel=1
 then a.name2 else a.name1 end
 -- nested expressions
create table single (c char(1));
insert into single values('X');
/*r1*/select case c when 'X' then 1 else 2 end from single;
insert into single values(null);
/*r
 X
 Y
*/select ifnull(c,'Y') from single
/*r
 X
 Y
*/select coalesce(c,'Y') from single
/*e*/select coalesce() from single
/*e*/select coalesce(c) from single
/*e*/select ifnull() from single
/*e*/select ifnull(c) from single
insert into single values('Y')
insert into single values('Z')
drop table single;
create table single (c int);
/*rAVG*/select case avg(c) when max(c) then 'MAX' when min(c) then 'MIN' else 'AVG' end from single;
insert into single values(1);
/*r1*/select case avg(c) when max(c) then max(c) when min(c) then min(c) else avg(c) end from single;
insert into single values(2);
insert into single values(3);
/*r2*/select case avg(c) when max(c) then max(c) when min(c) then min(c) else avg(c) end from single;
/*r
 1
 2
 3
*/select * from single order by 1
-- multi-element case when
/*r
 one-or-two
 one-or-two
 three-----
*/select case c when in(0,1,2) then 'one-or-two' when in(0,3,30) then 'three-----' end from single order by 1
/*r
 NULL
 NULL
 :OK   :
*/select ':' || case c when in(0,10,10) then 'WRONG' when in(0,3,34)
 then 'OK' end || ':' from single order by 1

CREATE TABLE BAB (SALES DOUBLE, COST DOUBLE);
SELECT (SUM(BAB.SALES)-SUM(BAB.COST))/(CASE WHEN (ABS (SUM(BAB.SALES))) = 0
 THEN NULL ELSE (ABS (SUM(BAB.SALES))) END) AS PROFITMARGIN FROM BAB