| -- Additional CASE-WHEN tests |
| -- |
| -- CASE ... WHEN IS NOT DISTINCT FROM ... |
| -- |
| DROP TABLE IF EXISTS mytable CASCADE; |
| NOTICE: table "mytable" does not exist, skipping |
| CREATE TABLE mytable (a int, b int, c varchar(1)); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO mytable values (1,2,'t'), |
| (2,3,'e'), |
| (3,4,'o'), |
| (4,5,'o'), |
| (4,4,'o'), |
| (5,5,'t'), |
| (6,6,'t'), |
| (7,6,'a'), |
| (8,7,'t'), |
| (9,8,'a'); |
| DROP VIEW IF EXISTS notdisview; |
| NOTICE: view "notdisview" does not exist, skipping |
| CREATE OR REPLACE VIEW notdisview AS |
| SELECT |
| CASE 'a'::text = 'test'::text |
| WHEN 'test' IS NOT DISTINCT FROM ''::text THEN 'A'::text |
| ELSE 'B'::text |
| END AS t; |
| select pg_get_viewdef('notdisview',true); |
| pg_get_viewdef |
| ---------------------------------------------------------------------------- |
| SELECT + |
| CASE 'a'::text = 'test'::text + |
| WHEN 'test'::text IS NOT DISTINCT FROM ''::text THEN 'A'::text+ |
| ELSE 'B'::text + |
| END AS t; |
| (1 row) |
| |
| DROP VIEW IF EXISTS notdisview2; |
| NOTICE: view "notdisview2" does not exist, skipping |
| CREATE OR REPLACE VIEW notdisview2 AS |
| SELECT |
| CASE |
| WHEN c::text IS NOT DISTINCT FROM ''::text THEN 'A'::text |
| ELSE 'B'::text |
| END AS t |
| FROM mytable; |
| select pg_get_viewdef('notdisview2',true); |
| pg_get_viewdef |
| ------------------------------------------------------------------------------- |
| SELECT + |
| CASE + |
| WHEN mytable.c::text IS NOT DISTINCT FROM ''::text THEN 'A'::text+ |
| ELSE 'B'::text + |
| END AS t + |
| FROM mytable; |
| (1 row) |
| |
| CREATE TABLE mytable2 ( |
| key character varying(20) NOT NULL, |
| key_value character varying(50) |
| ) DISTRIBUTED BY (key); |
| DROP VIEW IF EXISTS notdisview3; |
| NOTICE: view "notdisview3" does not exist, skipping |
| CREATE OR REPLACE VIEW notdisview3 AS |
| SELECT |
| CASE mytable2.key_value |
| WHEN IS NOT DISTINCT FROM 'NULL'::text THEN 'now'::text::date |
| ELSE to_date(mytable2.key_value::text, 'YYYYMM'::text) |
| END AS t |
| FROM mytable2; |
| select pg_get_viewdef('notdisview3',false); |
| pg_get_viewdef |
| ----------------------------------------------------------------------------- |
| SELECT + |
| CASE mytable2.key_value + |
| WHEN IS NOT DISTINCT FROM 'NULL'::text THEN ('now'::text)::date+ |
| ELSE to_date((mytable2.key_value)::text, 'YYYYMM'::text) + |
| END AS t + |
| FROM mytable2; |
| (1 row) |
| |
| CREATE OR REPLACE FUNCTION negate(int) RETURNS int |
| AS 'SELECT $1 * (-1)' |
| LANGUAGE sql CONTAINS SQL |
| IMMUTABLE |
| RETURNS null ON null input; |
| DROP VIEW IF EXISTS myview; |
| NOTICE: view "myview" does not exist, skipping |
| CREATE VIEW myview AS |
| SELECT a,b, CASE a WHEN IS NOT DISTINCT FROM b THEN b*10 |
| WHEN IS NOT DISTINCT FROM b+1 THEN b*100 |
| WHEN b-1 THEN b*1000 |
| WHEN b*10 THEN b*10000 |
| WHEN negate(b) THEN b*(-1.0) |
| ELSE b END AS newb |
| FROM mytable; |
| SELECT * FROM myview ORDER BY a,b; |
| a | b | newb |
| ---+---+------ |
| 1 | 2 | 2000 |
| 2 | 3 | 3000 |
| 3 | 4 | 4000 |
| 4 | 4 | 40 |
| 4 | 5 | 5000 |
| 5 | 5 | 50 |
| 6 | 6 | 60 |
| 7 | 6 | 600 |
| 8 | 7 | 700 |
| 9 | 8 | 800 |
| (10 rows) |
| |
| -- Test deparse |
| select pg_get_viewdef('myview',true); |
| pg_get_viewdef |
| ------------------------------------------------------------------------------------- |
| SELECT mytable.a, + |
| mytable.b, + |
| CASE mytable.a + |
| WHEN IS NOT DISTINCT FROM mytable.b THEN (mytable.b * 10)::numeric + |
| WHEN IS NOT DISTINCT FROM mytable.b + 1 THEN (mytable.b * 100)::numeric+ |
| WHEN mytable.b - 1 THEN (mytable.b * 1000)::numeric + |
| WHEN mytable.b * 10 THEN (mytable.b * 10000)::numeric + |
| WHEN negate(mytable.b) THEN mytable.b::numeric * '-1.0'::numeric + |
| ELSE mytable.b::numeric + |
| END AS newb + |
| FROM mytable; |
| (1 row) |
| |
| DROP TABLE IF EXISTS products CASCADE; |
| NOTICE: table "products" does not exist, skipping |
| CREATE TABLE products (id serial, name text, price numeric); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO products (name, price) values |
| ('keyboard', 124.99), |
| ('monitor', 299.99), |
| ('mouse', 45.59); |
| SELECT id,name,price as old_price, |
| CASE name WHEN IS NOT DISTINCT FROM 'keyboard' THEN products.price*1.5 |
| WHEN IS NOT DISTINCT FROM 'monitor' THEN price*1.2 |
| WHEN 'keyboard tray' THEN price*.9 |
| END AS new_price |
| FROM products; |
| id | name | old_price | new_price |
| ----+----------+-----------+----------- |
| 1 | keyboard | 124.99 | 187.485 |
| 2 | monitor | 299.99 | 359.988 |
| 3 | mouse | 45.59 | |
| (3 rows) |
| |
| |
| -- testexpr should be evaluated only once |
| DROP FUNCTION IF EXISTS blip(int); |
| NOTICE: function blip(pg_catalog.int4) does not exist, skipping |
| DROP TABLE IF EXISTS calls_to_blip; |
| NOTICE: table "calls_to_blip" does not exist, skipping |
| CREATE TABLE calls_to_blip (n serial, v int) DISTRIBUTED RANDOMLY; |
| CREATE OR REPLACE FUNCTION blip(int) RETURNS int |
| LANGUAGE plpgsql MODIFIES SQL DATA |
| VOLATILE |
| AS $$ |
| DECLARE |
| x alias for $1; |
| BEGIN |
| INSERT INTO calls_to_blip(v) VALUES (x); |
| RETURN x; |
| END; |
| $$; |
| SELECT CASE blip(1) |
| WHEN IS NOT DISTINCT FROM blip(2) THEN blip(20) |
| WHEN IS NOT DISTINCT FROM blip(3) THEN blip(30) |
| WHEN IS NOT DISTINCT FROM blip(4) THEN blip(40) |
| ELSE blip(666) |
| END AS answer; |
| answer |
| -------- |
| 666 |
| (1 row) |
| |
| SELECT * FROM calls_to_blip ORDER BY 1; |
| n | v |
| ---+----- |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 666 |
| (5 rows) |
| |
| -- Negative test |
| -- 1. wrong syntax |
| -- 2. type mismatches |
| SELECT a,b,CASE WHEN IS NOT DISTINCT FROM b THEN b*100 ELSE b*1000 END FROM mytable; |
| ERROR: syntax error at or near "NOT" |
| LINE 1: SELECT a,b,CASE WHEN IS NOT DISTINCT FROM b THEN b*100 ELSE ... |
| ^ |
| HINT: Missing <operand> for "CASE <operand> WHEN IS NOT DISTINCT FROM ..." |
| SELECT a,b,c,CASE c WHEN IS NOT DISTINCT FROM b THEN a |
| WHEN IS NOT DISTINCT FROM b+1 THEN a*100 |
| ELSE c END |
| FROM mytable; |
| ERROR: operator does not exist: character varying = integer |
| LINE 1: SELECT a,b,c,CASE c WHEN IS NOT DISTINCT FROM b THEN a |
| ^ |
| HINT: No operator matches the given name and argument types. You might need to add explicit type casts. |
| -- |
| -- DECODE(): Oracle compatibility |
| -- |
| SELECT decode(null,null,true,false); |
| case |
| ------ |
| t |
| (1 row) |
| |
| SELECT decode(NULL::integer, 1, 100, NULL, 200, 300); |
| case |
| ------ |
| 200 |
| (1 row) |
| |
| SELECT decode('1'::text, '1', 100, '2', 200); |
| case |
| ------ |
| 100 |
| (1 row) |
| |
| SELECT decode(2, 1, 'ABC', 2, 'DEF'); |
| case |
| ------ |
| DEF |
| (1 row) |
| |
| SELECT decode('2009-02-05'::date, '2009-02-05', 'ok'); |
| case |
| ------ |
| ok |
| (1 row) |
| |
| SELECT decode('2009-02-05 01:02:03'::timestamp, '2009-02-05 01:02:03', 'ok'); |
| case |
| ------ |
| ok |
| (1 row) |
| |
| SELECT b,c,decode(c,'a',b*10,'e',b*100,'o',b*1000,'u',b*10000,'i',b*100000) as newb from mytable; |
| b | c | newb |
| ---+---+------ |
| 2 | t | |
| 3 | e | 300 |
| 4 | o | 4000 |
| 5 | o | 5000 |
| 4 | o | 4000 |
| 5 | t | |
| 6 | t | |
| 6 | a | 60 |
| 7 | t | |
| 8 | a | 80 |
| (10 rows) |
| |
| SELECT b,c,decode(c,'a',ARRAY[1,2],'e',ARRAY[3,4],'o',ARRAY[5,6],'u',ARRAY[7,8],'i',ARRAY[9,10],ARRAY[0]) as newb from mytable; |
| b | c | newb |
| ---+---+------- |
| 2 | t | {0} |
| 3 | e | {3,4} |
| 4 | o | {5,6} |
| 5 | o | {5,6} |
| 4 | o | {5,6} |
| 5 | t | {0} |
| 6 | t | {0} |
| 6 | a | {1,2} |
| 7 | t | {0} |
| 8 | a | {1,2} |
| (10 rows) |
| |
| DROP VIEW IF EXISTS myview; |
| CREATE VIEW myview as |
| SELECT id, name, price, DECODE(id, 1, 'Southlake', |
| 2, 'San Francisco', |
| 3, 'New Jersey', |
| 4, 'Seattle', |
| 5, 'Portland', |
| 6, 'San Francisco', |
| 7, 'Portland', |
| 'Non domestic') Location |
| FROM products |
| WHERE id < 100; |
| SELECT * FROM myview ORDER BY id, location; |
| id | name | price | location |
| ----+----------+--------+--------------- |
| 1 | keyboard | 124.99 | Southlake |
| 2 | monitor | 299.99 | San Francisco |
| 3 | mouse | 45.59 | New Jersey |
| (3 rows) |
| |
| -- Test deparse |
| select pg_get_viewdef('myview',true); |
| pg_get_viewdef |
| -------------------------------------------------------------------- |
| SELECT products.id, + |
| products.name, + |
| products.price, + |
| CASE products.id + |
| WHEN IS NOT DISTINCT FROM 1 THEN 'Southlake'::text + |
| WHEN IS NOT DISTINCT FROM 2 THEN 'San Francisco'::text+ |
| WHEN IS NOT DISTINCT FROM 3 THEN 'New Jersey'::text + |
| WHEN IS NOT DISTINCT FROM 4 THEN 'Seattle'::text + |
| WHEN IS NOT DISTINCT FROM 5 THEN 'Portland'::text + |
| WHEN IS NOT DISTINCT FROM 6 THEN 'San Francisco'::text+ |
| WHEN IS NOT DISTINCT FROM 7 THEN 'Portland'::text + |
| ELSE 'Non domestic'::text + |
| END AS location + |
| FROM products + |
| WHERE products.id < 100; |
| (1 row) |
| |
| -- User-defined DECODE function |
| CREATE OR REPLACE FUNCTION "decode"(int, int, int) RETURNS int |
| AS 'select $1 * $2 - $3;' |
| LANGUAGE sql CONTAINS SQL |
| IMMUTABLE |
| RETURNS null ON null input; |
| SELECT decode(11,8,11); |
| case |
| ------ |
| |
| (1 row) |
| |
| SELECT "decode"(11,8,11); |
| decode |
| -------- |
| 77 |
| (1 row) |
| |
| SELECT public.decode(11,8,11); |
| decode |
| -------- |
| 77 |
| (1 row) |
| |
| -- Test CASE x WHEN IS NOT DISTINCT FROM y with DECODE |
| SELECT a,b,decode(a,1,1), |
| CASE decode(a,1,1) WHEN IS NOT DISTINCT FROM 1 THEN b*100 |
| WHEN IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb |
| FROM mytable ORDER BY a,b; |
| a | b | case | newb |
| ---+---+------+------ |
| 1 | 2 | 1 | 200 |
| 2 | 3 | | 3 |
| 3 | 4 | | 4 |
| 4 | 4 | | 4 |
| 4 | 5 | | 5 |
| 5 | 5 | | 5 |
| 6 | 6 | | 6 |
| 7 | 6 | | 6 |
| 8 | 7 | | 7 |
| 9 | 8 | | 8 |
| (10 rows) |
| |
| -- Test CASE WHEN x IS NOT DISTINCT FROM y with DECODE |
| SELECT a,b,decode(a,1,1), |
| CASE WHEN decode(a,1,1) IS NOT DISTINCT FROM 1 THEN b*100 |
| WHEN decode(a,1,1) IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb |
| FROM mytable ORDER BY a,b; |
| a | b | case | newb |
| ---+---+------+------ |
| 1 | 2 | 1 | 200 |
| 2 | 3 | | 3 |
| 3 | 4 | | 4 |
| 4 | 4 | | 4 |
| 4 | 5 | | 5 |
| 5 | 5 | | 5 |
| 6 | 6 | | 6 |
| 7 | 6 | | 6 |
| 8 | 7 | | 7 |
| 9 | 8 | | 8 |
| (10 rows) |
| |
| SELECT a,b,"decode"(a,1,1), |
| CASE WHEN "decode"(a,1,1) IS NOT DISTINCT FROM 1 THEN b*100 |
| WHEN "decode"(a,1,1) IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb |
| FROM mytable ORDER BY a,b; |
| a | b | decode | newb |
| ---+---+--------+------ |
| 1 | 2 | 0 | 2 |
| 2 | 3 | 1 | 300 |
| 3 | 4 | 2 | 4 |
| 4 | 4 | 3 | 4 |
| 4 | 5 | 3 | 5 |
| 5 | 5 | 4 | 5000 |
| 6 | 6 | 5 | 6 |
| 7 | 6 | 6 | 6 |
| 8 | 7 | 7 | 7 |
| 9 | 8 | 8 | 8 |
| (10 rows) |
| |
| -- Negative test: type mismatches |
| SELECT b,c,decode(c,'a',ARRAY[1,2],'e',ARRAY[3,4],'o',ARRAY[5,6],'u',ARRAY[7,8],'i',ARRAY[9,10],0) as newb from mytable; |
| ERROR: CASE types integer and integer[] cannot be matched |
| LINE 1: SELECT b,c,decode(c,'a',ARRAY[1,2],'e',ARRAY[3,4],'o',ARRAY[... |
| ^ |
| -- |
| -- Clean up |
| -- |
| DROP TABLE mytable CASCADE; |
| NOTICE: drop cascades to view notdisview2 |
| DROP TABLE products CASCADE; |
| NOTICE: drop cascades to view myview |
| DROP TABLE calls_to_blip; |
| DROP FUNCTION negate(int); |
| DROP FUNCTION "decode"(int, int, int); |
| DROP FUNCTION blip(int); |
| select CASE 'M' |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| END; |
| case |
| ------ |
| Male |
| (1 row) |
| |
| select CASE 'F' |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| END; |
| case |
| -------- |
| Female |
| (1 row) |
| |
| select CASE '' |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| END; |
| case |
| --------------- |
| Not Specified |
| (1 row) |
| |
| select CASE null |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| END; |
| case |
| --------------- |
| Not Specified |
| (1 row) |
| |
| create table case_genders (gid integer, gender char(1)) distributed by (gid); |
| insert into case_genders(gid, gender) values |
| (1, 'F'), |
| (2, 'M'), |
| (3, 'Z'), |
| (4, ''), |
| (5, null), |
| (6, 'G'); |
| select gender, CASE gender |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| END |
| from case_genders |
| order by gid; |
| gender | case |
| --------+--------------- |
| F | Female |
| M | Male |
| Z | |
| | Not Specified |
| | Not Specified |
| G | |
| (6 rows) |
| |
| select CASE 'M' |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| ELSE 'Other' END; |
| case |
| ------ |
| Male |
| (1 row) |
| |
| select CASE null |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| ELSE 'Other' END; |
| case |
| ------- |
| Other |
| (1 row) |
| |
| select gender, CASE gender |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'Male' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'Female' |
| WHEN IS NOT DISTINCT FROM '' THEN 'Not Specified' |
| WHEN IS NOT DISTINCT FROM null THEN 'Not Specified' |
| ELSE 'Other' END |
| from case_genders |
| order by gid; |
| gender | case |
| --------+--------------- |
| F | Female |
| M | Male |
| Z | Other |
| | Not Specified |
| | Not Specified |
| G | Other |
| (6 rows) |
| |
| select 'a' as lhs, CASE 'a' |
| WHEN 'f' THEN 'WHEN: f' |
| WHEN IS NOT DISTINCT FROM 'f' THEN 'WHEN NEW: f' |
| WHEN 'e' THEN 'WHEN: e' |
| WHEN IS NOT DISTINCT FROM 'e' THEN 'WHEN NEW: e' |
| WHEN 'd' THEN 'WHEN: d' |
| WHEN IS NOT DISTINCT FROM 'd' THEN 'WHEN NEW: d' |
| WHEN 'c' THEN 'WHEN: c' |
| WHEN IS NOT DISTINCT FROM 'c' THEN 'WHEN NEW: c' |
| WHEN 'b' THEN 'WHEN: b' |
| WHEN IS NOT DISTINCT FROM 'b' THEN 'WHEN NEW: b' |
| WHEN 'a' THEN 'WHEN: a' |
| WHEN IS NOT DISTINCT FROM 'a' THEN 'WHEN NEW: a' |
| ELSE 'NO MATCH' END as match; |
| lhs | match |
| -----+--------- |
| a | WHEN: a |
| (1 row) |
| |
| select 1 as lhs, CASE 1 |
| WHEN 4 THEN 'WHEN: 4' |
| WHEN IS NOT DISTINCT FROM 4 THEN 'WHEN NEW: 4' |
| WHEN 3 THEN 'WHEN: 3' |
| WHEN IS NOT DISTINCT FROM 3 THEN 'WHEN NEW: 3' |
| WHEN 2 THEN 'WHEN: 2' |
| WHEN IS NOT DISTINCT FROM 2 THEN 'WHEN NEW: 2' |
| WHEN 11 THEN 'WHEN: 11' |
| WHEN IS NOT DISTINCT FROM 1 THEN 'WHEN NEW: 1' |
| ELSE 'NO MATCH' END as match; |
| lhs | match |
| -----+------------- |
| 1 | WHEN NEW: 1 |
| (1 row) |
| |
| select '2011-05-27'::date as lsh, CASE '2011-05-27'::date |
| WHEN '2011-07-25'::date THEN 'WHEN: 2011-07-25' |
| WHEN IS NOT DISTINCT FROM '2011-05-27'::date THEN 'WHEN NEW: 2011-05-27' |
| END as match; |
| lsh | match |
| ------------+---------------------- |
| 05-27-2011 | WHEN NEW: 2011-05-27 |
| (1 row) |
| |
| -- start_ignore |
| drop table if exists nomatch_case; |
| NOTICE: table "nomatch_case" does not exist, skipping |
| -- end_ignore |
| create table nomatch_case |
| ( |
| sid integer, |
| gender char(1) default 'F', |
| name text, |
| start_dt date |
| ) distributed by (sid); |
| insert into nomatch_case(sid, gender, name, start_dt) values |
| (1000, 'F', 'Jane Doe', '2011-01-15'::date), |
| (2000, 'M', 'Ryan Goesling', '2011-02-01'::date), |
| (3000, 'M', 'Tim Tebow', '2011-01-15'::date), |
| (4000, 'F', 'Katy Perry', '2011-03-01'::date), |
| (5000, 'F', 'Michael Scott', '2011-02-01'::date); |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE upper(gender) |
| WHEN 'MALE' THEN 'M' |
| WHEN IS NOT DISTINCT FROM 'FEMALE' THEN 'F' |
| WHEN trim('MALE ') THEN 'M' |
| ELSE 'NO MATCH' END as case_gender |
| from nomatch_case |
| order by sid, name; |
| sid | name | gender | start_dt | case_gender |
| ------+---------------+--------+------------+------------- |
| 1000 | Jane Doe | F | 01-15-2011 | NO MATCH |
| 2000 | Ryan Goesling | M | 02-01-2011 | NO MATCH |
| 3000 | Tim Tebow | M | 01-15-2011 | NO MATCH |
| 4000 | Katy Perry | F | 03-01-2011 | NO MATCH |
| 5000 | Michael Scott | F | 02-01-2011 | NO MATCH |
| (5 rows) |
| |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE start_dt |
| WHEN IS NOT DISTINCT FROM '2009-01-01'::date THEN 2009 |
| WHEN '2008-01-01'::date THEN 2008 |
| WHEN IS NOT DISTINCT FROM '2010-01-01'::date then 2010 |
| WHEN 2007 THEN 2007 |
| WHEN IS NOT DISTINCT FROM 2007 THEN 2007 |
| WHEN '2006-01-01'::date then 2006 |
| END as case_start_dt |
| from nomatch_case |
| order by sid, name; |
| ERROR: operator does not exist: date = integer |
| LINE 9: WHEN 2007 THEN 2007 |
| ^ |
| HINT: No operator matches the given name and argument types. You might need to add explicit type casts. |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE sid |
| WHEN 100 THEN 'Dept 10' |
| WHEN 200 THEN 'Dept 20' |
| WHEN IS NOT DISTINCT FROM 300 then 'Dept 30' |
| WHEN 400 THEN 'Dept 40' |
| WHEN 500 THEN 'Dept 50' |
| WHEN IS NOT DISTINCT FROM 600 then 'Dept 60' |
| WHEN IS NOT DISTINCT FROM 700 then 'Dept 70' |
| END as case_sid |
| from nomatch_case |
| order by sid, name; |
| sid | name | gender | start_dt | case_sid |
| ------+---------------+--------+------------+---------- |
| 1000 | Jane Doe | F | 01-15-2011 | |
| 2000 | Ryan Goesling | M | 02-01-2011 | |
| 3000 | Tim Tebow | M | 01-15-2011 | |
| 4000 | Katy Perry | F | 03-01-2011 | |
| 5000 | Michael Scott | F | 02-01-2011 | |
| (5 rows) |
| |
| create table combined_when |
| ( |
| sid integer, |
| gender varchar(10) default 'F', |
| name text, |
| start_dt date |
| ) distributed by (sid); |
| insert into combined_when(sid, gender, name, start_dt) values |
| (1000, 'F', 'Jane Doe', '2011-01-15'::date), |
| (2000, 'M', 'Ryan Goesling', '2011-02-01'::date), |
| (3000, 'm', 'Tim Tebow', '2007-01-15'::date), |
| (4000, 'F', 'Katy Perry', '2011-03-01'::date), |
| (5000, 'f', 'Michael Scott', '2011-02-01'::date), |
| (6000, 'Female ', 'Mila Kunis', '2011-02-01'::date), |
| (7000, ' Male ', 'Tom Brady', '2011-03-01'::date), |
| (8000, ' ', 'Lady Gaga', '2008-01-15'::date), |
| (9000, null, 'George Michael', '2011-01-15'::date), |
| (10000, 'Male ', 'Michael Jordan', null); |
| select case_yr_start_dt, count(sid) |
| from (select sid, |
| name, |
| gender, |
| start_dt, |
| CASE extract(year from start_dt) |
| WHEN IS NOT DISTINCT FROM 2009 THEN 2009 |
| WHEN abs(2009-1) THEN 2008 |
| WHEN IS NOT DISTINCT FROM extract(year from '2010-01-01'::date) then 2010 |
| WHEN round(2007.05, 0) THEN 2007 |
| WHEN IS NOT DISTINCT FROM 2007 THEN 2007 |
| WHEN extract(year from '2006-01-01'::date) then 2006 |
| WHEN extract(year from '2011-01-01'::date) then 2011 |
| END as case_yr_start_dt |
| from combined_when |
| order by sid, name) a |
| group by case_yr_start_dt |
| order by 2 desc, 1; |
| case_yr_start_dt | count |
| ------------------+------- |
| 2011 | 7 |
| 2007 | 1 |
| 2008 | 1 |
| | 1 |
| (4 rows) |
| |
| create table case_expr |
| ( |
| sid integer, |
| gender char(1) default 'F', |
| name text, |
| start_dt date |
| ) distributed by (sid); |
| insert into case_expr(sid, gender, name, start_dt) values |
| (1000, 'F', 'Jane Doe', '2011-01-15'::date), |
| (2000, 'M', 'Ryan Goesling', '2011-02-01'::date), |
| (3000, 'M', 'Tim Tebow', '2011-01-15'::date), |
| (4000, 'F', 'Katy Perry', '2011-03-01'::date), |
| (5000, 'F', 'Michael Scott', '2011-02-01'::date); |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE (gender is not null) |
| WHEN (gender = 'MALE') THEN 'M' |
| WHEN IS NOT DISTINCT FROM (gender = 'FEMALE') THEN 'F' |
| WHEN (gender = trim('MALE ')) THEN 'M' |
| WHEN IS NOT DISTINCT FROM (gender = 'M') THEN 'M' |
| WHEN (gender = 'F') THEN 'F' |
| ELSE 'NO MATCH' END as case_gender |
| from case_expr |
| order by sid, name; |
| sid | name | gender | start_dt | case_gender |
| ------+---------------+--------+------------+------------- |
| 1000 | Jane Doe | F | 01-15-2011 | F |
| 2000 | Ryan Goesling | M | 02-01-2011 | M |
| 3000 | Tim Tebow | M | 01-15-2011 | M |
| 4000 | Katy Perry | F | 03-01-2011 | F |
| 5000 | Michael Scott | F | 02-01-2011 | F |
| (5 rows) |
| |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE (extract(year from start_dt) = 2011) |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 1) THEN 'January' |
| WHEN (extract(month from start_dt) = 2) THEN 'February' |
| WHEN (extract(month from start_dt) = 3) THEN 'March' |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 4) THEN 'April' |
| WHEN (extract(month from start_dt) = 5) THEN 'May' |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 6) THEN 'June' |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 7) THEN 'July' |
| WHEN (extract(month from start_dt) = 8) THEN 'August' |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 9) THEN 'September' |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 10) THEN 'October' |
| WHEN IS NOT DISTINCT FROM (extract(month from start_dt) = 11) THEN 'November' |
| WHEN (extract(month from start_dt) = 12) THEN 'December' |
| END as case_start_month |
| from case_expr |
| order by sid, name; |
| sid | name | gender | start_dt | case_start_month |
| ------+---------------+--------+------------+------------------ |
| 1000 | Jane Doe | F | 01-15-2011 | January |
| 2000 | Ryan Goesling | M | 02-01-2011 | February |
| 3000 | Tim Tebow | M | 01-15-2011 | January |
| 4000 | Katy Perry | F | 03-01-2011 | March |
| 5000 | Michael Scott | F | 02-01-2011 | February |
| (5 rows) |
| |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE (sid > 100) |
| WHEN (sid = 1000) THEN 'Dept 10' |
| WHEN (sid > 1000 and sid <= 2000) THEN 'Dept 20' |
| WHEN IS NOT DISTINCT FROM (sid = 3000) then 'Dept 30' |
| WHEN (sid = 4000) THEN 'Dept 40' |
| WHEN (sid = 5000) THEN 'Dept 50' |
| WHEN IS NOT DISTINCT FROM (sid > 5000 and sid <= 6000) then 'Dept 60' |
| WHEN IS NOT DISTINCT FROM (sid = 7000) then 'Dept 70' |
| END as case_sid |
| from case_expr |
| order by sid, name; |
| sid | name | gender | start_dt | case_sid |
| ------+---------------+--------+------------+---------- |
| 1000 | Jane Doe | F | 01-15-2011 | Dept 10 |
| 2000 | Ryan Goesling | M | 02-01-2011 | Dept 20 |
| 3000 | Tim Tebow | M | 01-15-2011 | Dept 30 |
| 4000 | Katy Perry | F | 03-01-2011 | Dept 40 |
| 5000 | Michael Scott | F | 02-01-2011 | Dept 50 |
| (5 rows) |
| |
| drop table if exists combined_when; |
| create table combined_when |
| ( |
| sid integer, |
| gender varchar(10) default 'F', |
| name text, |
| start_dt date |
| ) distributed by (sid); |
| insert into combined_when(sid, gender, name, start_dt) values |
| (1000, 'F', 'Jane Doe', '2011-01-15'::date), |
| (2000, 'M', 'Ryan Goesling', '2011-02-01'::date), |
| (3000, 'm', 'Tim Tebow', '2007-01-15'::date), |
| (4000, 'F', 'Katy Perry', '2011-03-01'::date), |
| (5000, 'f', 'Michael Scott', '2011-02-01'::date), |
| (6000, 'Female ', 'Mila Kunis', '2011-02-01'::date), |
| (7000, ' Male ', 'Tom Brady', '2011-03-01'::date), |
| (8000, ' ', 'Lady Gaga', '2008-01-15'::date), |
| (9000, null, 'George Michael', '2011-01-15'::date), |
| (10000, 'Male ', 'Michael Jordan', null); |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE upper(trim(gender)) |
| WHEN 'MALE' THEN 'M' |
| WHEN IS NOT DISTINCT FROM 'FEMALE' THEN 'F' |
| WHEN trim('MALE ') THEN 'M' |
| WHEN trim(' FEMALE ') THEN 'F' |
| WHEN IS NOT DISTINCT FROM 'M' THEN 'M' |
| WHEN IS NOT DISTINCT FROM 'F' THEN 'F' |
| ELSE 'NO MATCH' END as case_gender |
| from combined_when |
| order by sid, name; |
| sid | name | gender | start_dt | case_gender |
| -------+----------------+----------+------------+------------- |
| 1000 | Jane Doe | F | 01-15-2011 | F |
| 2000 | Ryan Goesling | M | 02-01-2011 | M |
| 3000 | Tim Tebow | m | 01-15-2007 | M |
| 4000 | Katy Perry | F | 03-01-2011 | F |
| 5000 | Michael Scott | f | 02-01-2011 | F |
| 6000 | Mila Kunis | Female | 02-01-2011 | F |
| 7000 | Tom Brady | Male | 03-01-2011 | M |
| 8000 | Lady Gaga | | 01-15-2008 | NO MATCH |
| 9000 | George Michael | | 01-15-2011 | NO MATCH |
| 10000 | Michael Jordan | Male | | M |
| (10 rows) |
| |
| select sid, |
| name, |
| gender, |
| start_dt, |
| CASE extract(year from start_dt) |
| WHEN IS NOT DISTINCT FROM 2009 THEN 2009 |
| WHEN abs(2009-1) THEN 2008 |
| WHEN IS NOT DISTINCT FROM extract(year from '2010-01-01'::date) then 2010 |
| WHEN round(2007.05, 0) THEN 2007 |
| WHEN IS NOT DISTINCT FROM 2007 THEN 2007 |
| WHEN extract(year from '2006-01-01'::date) then 2006 |
| WHEN extract(year from '2011-01-01'::date) then 2011 |
| END as case_yr_start_dt |
| from combined_when |
| order by sid, name; |
| sid | name | gender | start_dt | case_yr_start_dt |
| -------+----------------+----------+------------+------------------ |
| 1000 | Jane Doe | F | 01-15-2011 | 2011 |
| 2000 | Ryan Goesling | M | 02-01-2011 | 2011 |
| 3000 | Tim Tebow | m | 01-15-2007 | 2007 |
| 4000 | Katy Perry | F | 03-01-2011 | 2011 |
| 5000 | Michael Scott | f | 02-01-2011 | 2011 |
| 6000 | Mila Kunis | Female | 02-01-2011 | 2011 |
| 7000 | Tom Brady | Male | 03-01-2011 | 2011 |
| 8000 | Lady Gaga | | 01-15-2008 | 2008 |
| 9000 | George Michael | | 01-15-2011 | 2011 |
| 10000 | Michael Jordan | Male | | |
| (10 rows) |
| |
| select case_yr_start_dt, count(sid) |
| from (select sid, |
| name, |
| gender, |
| start_dt, |
| CASE extract(year from start_dt) |
| WHEN IS NOT DISTINCT FROM 2009 THEN 2009 |
| WHEN abs(2009-1) THEN 2008 |
| WHEN IS NOT DISTINCT FROM extract(year from '2010-01-01'::date) then 2010 |
| WHEN round(2007.05, 0) THEN 2007 |
| WHEN IS NOT DISTINCT FROM 2007 THEN 2007 |
| WHEN extract(year from '2006-01-01'::date) then 2006 |
| WHEN extract(year from '2011-01-01'::date) then 2011 |
| END as case_yr_start_dt |
| from combined_when |
| order by sid, name) a |
| group by case_yr_start_dt |
| order by 2 desc, 1; |
| case_yr_start_dt | count |
| ------------------+------- |
| 2011 | 7 |
| 2007 | 1 |
| 2008 | 1 |
| | 1 |
| (4 rows) |
| |
| select CASE 'a' |
| WHEN IS NOT DISTINCT FROM 'b' THEN 'a=b' |
| WHEN NOT DISTINCT FROM 'a' THEN 'a=a' |
| END; |
| ERROR: syntax error at or near "DISTINCT" |
| LINE 3: WHEN NOT DISTINCT FROM 'a' THEN 'a=a' |
| ^ |
| select CASE 'a' |
| WHEN IS NOT DISTINCT 'b' THEN 'a=b' |
| WHEN IS NOT DISTINCT FROM 'a' THEN 'a=a' |
| END; |
| ERROR: syntax error at or near "'b'" |
| LINE 2: WHEN IS NOT DISTINCT 'b' THEN 'a=b' |
| ^ |
| select CASE 'a' |
| WHEN IS NOT DISTINCT FROM 'b' THEN 'a=b' |
| WHEN IS NOT DISTINCT FROM 'a' IS 'a=a' |
| END; |
| ERROR: syntax error at or near "'a=a'" |
| LINE 3: WHEN IS NOT DISTINCT FROM 'a' IS 'a=a' |
| ^ |
| select CASE 'a' |
| WHEN IS NOT DISTINCT FROM 'b' IS 'a=b' |
| WHEN IS NOT DISTINCT FROM 'a' THEN 'a=a' |
| END; |
| ERROR: syntax error at or near "'a=b'" |
| LINE 2: WHEN IS NOT DISTINCT FROM 'b' IS 'a=b' |
| ^ |
| select CASE 1 |
| WHEN IS NOT DISTINCT FROM 2 THEN '1=2' |
| WHEN IS DISTINCT FROM 2 THEN '1<>2' |
| WHEN IS NOT DISTINCT FROM 1 THEN '1=1' |
| END; |
| ERROR: syntax error at or near "DISTINCT" |
| LINE 3: WHEN IS DISTINCT FROM 2 THEN '1<>2' |
| ^ |
| -- |
| -- Case expression in group by |
| -- |
| SELECT |
| CASE t.field1 |
| WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying |
| ELSE t.field1 |
| END AS field1 |
| FROM ( SELECT 'test value'::text AS field1) t |
| GROUP BY |
| CASE t.field1 |
| WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying |
| ELSE t.field1 |
| END; |
| field1 |
| ------------ |
| test value |
| (1 row) |
| |
| |
| -- |
| -- Variant of case expression in group by |
| -- |
| SELECT |
| CASE t.field1 |
| WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying |
| ELSE t.field1 |
| END AS field1 |
| FROM ( SELECT 'test value'::text AS field1) t |
| GROUP BY 1; |
| field1 |
| ------------ |
| test value |
| (1 row) |
| |
| |
| -- |
| -- decode in group by |
| -- |
| SELECT |
| decode(t.field1, ''::text, 'Undefined'::character varying, t.field1) as field1 |
| FROM ( SELECT 'test value'::text AS field1) t |
| GROUP BY |
| decode(t.field1, ''::text, 'Undefined'::character varying, t.field1); |
| field1 |
| ------------ |
| test value |
| (1 row) |
| |
| |
| -- |
| -- variant of decode in group by |
| -- |
| SELECT |
| decode(t.field1, ''::text, 'Undefined'::character varying, t.field1) as field1 |
| FROM ( SELECT 'test value'::text AS field1) t |
| GROUP BY 1; |
| field1 |
| ------------ |
| test value |
| (1 row) |
| |
