USE tpch1;
SELECT * FROM region WHERE CASE WHEN r_regionkey < 3 THEN (r_name = 'ASIA' OR r_name = 'AFRICA') ELSE r_name = 'EUROPE' END;
r_regionkey	r_name	r_comment
0	AFRICA	lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to 
2	ASIA	ges. thinly even pinto beans ca
3	EUROPE	ly final courts cajole furiously final excuse
SELECT * FROM region WHERE CASE WHEN 1=1 THEN (r_name = 'ASIA' OR r_name = 'unknown') END;
r_regionkey	r_name	r_comment
2	ASIA	ges. thinly even pinto beans ca
select
l_shipdate, l_shipmode, l_suppkey
from lineitem
where
l_shipdate between '1997-01-12' and '1997-01-14'
     and
l_tax = .02
and
l_shipmode = 'AIR'
     and
(case
when (COALESCE('0') = '0')  then
(case
when (COALESCE('all') = 'ALL') 
then 1=1
when (COALESCE('all') is not null) 
then l_shipmode in ('ABC')
else 0=1
end)
else (case
when (COALESCE('all') = 'ALL') 
then 0=1
when (COALESCE('all') is not null) 
then l_shipmode not in ('ABC')
else 1=1
end)
end)
and  
(case
when (COALESCE('0') = '0')  then
(case
when (COALESCE('all') = 'ALL') 
then 1=1
when (COALESCE('all') is not null) 
then l_suppkey in (42)
else 0=1
end)
else (case
when (COALESCE('all') = 'ALL') 
then 0=1
when (COALESCE('all') is not null) 
then l_suppkey not in (42)
else 1=1
end)
end)
order by l_shipdate, l_suppkey;
l_shipdate	l_shipmode	l_suppkey
1997-01-12	AIR	136
1997-01-12	AIR	177
1997-01-12	AIR	263
1997-01-12	AIR	329
1997-01-12	AIR	368
1997-01-12	AIR	887
1997-01-12	AIR	1166
1997-01-12	AIR	1280
1997-01-12	AIR	1339
1997-01-12	AIR	2818
1997-01-12	AIR	2907
1997-01-12	AIR	2986
1997-01-12	AIR	3106
1997-01-12	AIR	3441
1997-01-12	AIR	4457
1997-01-12	AIR	4486
1997-01-12	AIR	4619
1997-01-12	AIR	4936
1997-01-12	AIR	4941
1997-01-12	AIR	5155
1997-01-12	AIR	5517
1997-01-12	AIR	5895
1997-01-12	AIR	6098
1997-01-12	AIR	6205
1997-01-12	AIR	6225
1997-01-12	AIR	6458
1997-01-12	AIR	8094
1997-01-12	AIR	9033
1997-01-12	AIR	9071
1997-01-12	AIR	9441
1997-01-13	AIR	55
1997-01-13	AIR	321
1997-01-13	AIR	324
1997-01-13	AIR	770
1997-01-13	AIR	932
1997-01-13	AIR	1703
1997-01-13	AIR	1824
1997-01-13	AIR	1862
1997-01-13	AIR	2132
1997-01-13	AIR	2245
1997-01-13	AIR	2586
1997-01-13	AIR	2808
1997-01-13	AIR	2953
1997-01-13	AIR	3071
1997-01-13	AIR	3313
1997-01-13	AIR	3738
1997-01-13	AIR	3857
1997-01-13	AIR	3964
1997-01-13	AIR	4044
1997-01-13	AIR	4149
1997-01-13	AIR	4995
1997-01-13	AIR	5071
1997-01-13	AIR	5657
1997-01-13	AIR	6070
1997-01-13	AIR	6216
1997-01-13	AIR	6478
1997-01-13	AIR	6566
1997-01-13	AIR	6641
1997-01-13	AIR	6759
1997-01-13	AIR	6828
1997-01-13	AIR	7575
1997-01-13	AIR	8018
1997-01-13	AIR	8023
1997-01-13	AIR	8289
1997-01-13	AIR	8380
1997-01-13	AIR	8444
1997-01-13	AIR	8764
1997-01-13	AIR	8844
1997-01-14	AIR	513
1997-01-14	AIR	1677
1997-01-14	AIR	1800
1997-01-14	AIR	2000
1997-01-14	AIR	2670
1997-01-14	AIR	3094
1997-01-14	AIR	3799
1997-01-14	AIR	4049
1997-01-14	AIR	4158
1997-01-14	AIR	4211
1997-01-14	AIR	4250
1997-01-14	AIR	4260
1997-01-14	AIR	4553
1997-01-14	AIR	4575
1997-01-14	AIR	4650
1997-01-14	AIR	4756
1997-01-14	AIR	4773
1997-01-14	AIR	4990
1997-01-14	AIR	5228
1997-01-14	AIR	5885
1997-01-14	AIR	6130
1997-01-14	AIR	6379
1997-01-14	AIR	6550
1997-01-14	AIR	6594
1997-01-14	AIR	7096
1997-01-14	AIR	7299
1997-01-14	AIR	7417
1997-01-14	AIR	7567
1997-01-14	AIR	7633
1997-01-14	AIR	7813
1997-01-14	AIR	8088
1997-01-14	AIR	8314
1997-01-14	AIR	8991
1997-01-14	AIR	9146
1997-01-14	AIR	9270
1997-01-14	AIR	9280
1997-01-14	AIR	9875
select
l_shipdate, l_shipmode, l_suppkey, l_discount, l_returnflag, l_tax
from lineitem
where
l_shipdate between '1997-01-12' and '1997-01-14'
    and
l_tax = 0
and
l_shipmode = 'RAIL'
    and
(case
when (l_quantity = 14.0)  then
(case
when (l_discount = .02) 
then l_returnflag in ('N', 'A')
when (l_discount = .03)
then l_returnflag in ('N', 'R')
else l_returnflag in ('R', 'A')
end)
else (case
when (l_discount = .02) 
then l_returnflag in ('R', 'A')
when (l_discount = .03) 
then l_returnflag in ('N', 'R')
else l_returnflag in ('N', 'A')
end)
end)
and  
(case
when (l_quantity = 5.0)  then
(case
when (l_discount = .02) 
then l_returnflag in ('N', 'A')
when (l_discount = .03)
then l_returnflag in ('N', 'R')
else l_returnflag in ('R', 'A')
end)
else (case
when (l_discount = .02) 
then l_returnflag in ('R', 'A')
when (l_discount = .03) 
then l_returnflag in ('N', 'R')
else l_returnflag in ('N', 'A')
end)
end)
order by l_shipdate, l_suppkey, l_discount;
l_shipdate	l_shipmode	l_suppkey	l_discount	l_returnflag	l_tax
1997-01-12	RAIL	184	0.08	N	0.00
1997-01-12	RAIL	336	0.09	N	0.00
1997-01-12	RAIL	502	0.07	N	0.00
1997-01-12	RAIL	509	0.07	N	0.00
1997-01-12	RAIL	769	0.09	N	0.00
1997-01-12	RAIL	797	0.06	N	0.00
1997-01-12	RAIL	1099	0.03	N	0.00
1997-01-12	RAIL	1178	0.07	N	0.00
1997-01-12	RAIL	1492	0.10	N	0.00
1997-01-12	RAIL	1617	0.10	N	0.00
1997-01-12	RAIL	2198	0.05	N	0.00
1997-01-12	RAIL	2255	0.00	N	0.00
1997-01-12	RAIL	2348	0.06	N	0.00
1997-01-12	RAIL	3094	0.06	N	0.00
1997-01-12	RAIL	3789	0.05	N	0.00
1997-01-12	RAIL	3838	0.03	N	0.00
1997-01-12	RAIL	3839	0.01	N	0.00
1997-01-12	RAIL	4823	0.00	N	0.00
1997-01-12	RAIL	5062	0.10	N	0.00
1997-01-12	RAIL	5066	0.01	N	0.00
1997-01-12	RAIL	5434	0.04	N	0.00
1997-01-12	RAIL	5986	0.07	N	0.00
1997-01-12	RAIL	6027	0.00	N	0.00
1997-01-12	RAIL	6191	0.03	N	0.00
1997-01-12	RAIL	6217	0.10	N	0.00
1997-01-12	RAIL	6586	0.06	N	0.00
1997-01-12	RAIL	6615	0.01	N	0.00
1997-01-12	RAIL	6615	0.06	N	0.00
1997-01-12	RAIL	6991	0.10	N	0.00
1997-01-12	RAIL	7353	0.01	N	0.00
1997-01-12	RAIL	7908	0.08	N	0.00
1997-01-12	RAIL	7980	0.00	N	0.00
1997-01-12	RAIL	8113	0.05	N	0.00
1997-01-12	RAIL	8145	0.03	N	0.00
1997-01-12	RAIL	9174	0.00	N	0.00
1997-01-12	RAIL	9346	0.01	N	0.00
1997-01-12	RAIL	9541	0.06	N	0.00
1997-01-12	RAIL	9643	0.03	N	0.00
1997-01-12	RAIL	9657	0.08	N	0.00
1997-01-12	RAIL	9701	0.06	N	0.00
1997-01-13	RAIL	350	0.00	N	0.00
1997-01-13	RAIL	446	0.07	N	0.00
1997-01-13	RAIL	1104	0.00	N	0.00
1997-01-13	RAIL	1259	0.04	N	0.00
1997-01-13	RAIL	1549	0.00	N	0.00
1997-01-13	RAIL	1634	0.09	N	0.00
1997-01-13	RAIL	1653	0.04	N	0.00
1997-01-13	RAIL	1692	0.04	N	0.00
1997-01-13	RAIL	1725	0.09	N	0.00
1997-01-13	RAIL	1810	0.10	N	0.00
1997-01-13	RAIL	1850	0.07	N	0.00
1997-01-13	RAIL	2262	0.04	N	0.00
1997-01-13	RAIL	2807	0.07	N	0.00
1997-01-13	RAIL	2931	0.01	N	0.00
1997-01-13	RAIL	3114	0.03	N	0.00
1997-01-13	RAIL	3200	0.00	N	0.00
1997-01-13	RAIL	3419	0.05	N	0.00
1997-01-13	RAIL	3646	0.01	N	0.00
1997-01-13	RAIL	3796	0.04	N	0.00
1997-01-13	RAIL	3897	0.04	N	0.00
1997-01-13	RAIL	4114	0.01	N	0.00
1997-01-13	RAIL	4364	0.05	N	0.00
1997-01-13	RAIL	4870	0.06	N	0.00
1997-01-13	RAIL	5309	0.09	N	0.00
1997-01-13	RAIL	5405	0.04	N	0.00
1997-01-13	RAIL	5541	0.08	N	0.00
1997-01-13	RAIL	5595	0.06	N	0.00
1997-01-13	RAIL	5647	0.00	N	0.00
1997-01-13	RAIL	5716	0.08	N	0.00
1997-01-13	RAIL	5721	0.07	N	0.00
1997-01-13	RAIL	5793	0.06	N	0.00
1997-01-13	RAIL	6096	0.00	N	0.00
1997-01-13	RAIL	6453	0.05	N	0.00
1997-01-13	RAIL	7041	0.10	N	0.00
1997-01-13	RAIL	7163	0.07	N	0.00
1997-01-13	RAIL	7405	0.08	N	0.00
1997-01-13	RAIL	7433	0.01	N	0.00
1997-01-13	RAIL	7542	0.06	N	0.00
1997-01-13	RAIL	7550	0.05	N	0.00
1997-01-13	RAIL	7617	0.07	N	0.00
1997-01-13	RAIL	7968	0.03	N	0.00
1997-01-13	RAIL	8475	0.06	N	0.00
1997-01-13	RAIL	8563	0.07	N	0.00
1997-01-13	RAIL	8617	0.07	N	0.00
1997-01-13	RAIL	8753	0.04	N	0.00
1997-01-13	RAIL	8895	0.05	N	0.00
1997-01-13	RAIL	9386	0.09	N	0.00
1997-01-13	RAIL	9416	0.07	N	0.00
1997-01-13	RAIL	9441	0.04	N	0.00
1997-01-13	RAIL	9448	0.04	N	0.00
1997-01-14	RAIL	90	0.07	N	0.00
1997-01-14	RAIL	226	0.08	N	0.00
1997-01-14	RAIL	817	0.03	N	0.00
1997-01-14	RAIL	1368	0.09	N	0.00
1997-01-14	RAIL	2199	0.01	N	0.00
1997-01-14	RAIL	2700	0.03	N	0.00
1997-01-14	RAIL	2906	0.07	N	0.00
1997-01-14	RAIL	4045	0.07	N	0.00
1997-01-14	RAIL	4175	0.07	N	0.00
1997-01-14	RAIL	4740	0.00	N	0.00
1997-01-14	RAIL	4883	0.08	N	0.00
1997-01-14	RAIL	6021	0.00	N	0.00
1997-01-14	RAIL	6082	0.09	N	0.00
1997-01-14	RAIL	6458	0.03	N	0.00
1997-01-14	RAIL	6921	0.07	N	0.00
1997-01-14	RAIL	7189	0.03	N	0.00
1997-01-14	RAIL	7299	0.07	N	0.00
1997-01-14	RAIL	7382	0.00	N	0.00
1997-01-14	RAIL	7640	0.06	N	0.00
1997-01-14	RAIL	7645	0.03	N	0.00
1997-01-14	RAIL	8419	0.09	N	0.00
1997-01-14	RAIL	8978	0.04	N	0.00
1997-01-14	RAIL	9193	0.10	N	0.00
1997-01-14	RAIL	9358	0.05	N	0.00