You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
45 lines
701 B
45 lines
701 B
3 years ago
|
--begin q20
|
||
|
|
-- $ID$
|
||
|
|
-- TPC-H/TPC-R Potential Part Promotion Query (Q20)
|
||
|
|
-- Function Query Definition
|
||
|
|
-- Approved February 1998
|
||
|
|
:x
|
||
|
|
:o
|
||
|
|
select
|
||
|
|
s_name,
|
||
|
|
s_address
|
||
|
|
from
|
||
|
|
tpch.supplier,
|
||
|
|
tpch.nation
|
||
|
|
where
|
||
|
|
s_suppkey in (
|
||
|
|
select
|
||
|
|
ps_suppkey
|
||
|
|
from
|
||
|
|
tpch.partsupp
|
||
|
|
where
|
||
|
|
ps_partkey in (
|
||
|
|
select
|
||
|
|
p_partkey
|
||
|
|
from
|
||
|
|
tpch.part
|
||
|
|
where
|
||
|
|
p_name like ':1%'
|
||
|
|
)
|
||
|
|
and ps_availqty > (
|
||
|
|
select
|
||
|
|
0.5 * sum(l_quantity)
|
||
|
|
from
|
||
|
|
tpch.lineitem
|
||
|
|
where
|
||
|
|
l_partkey = ps_partkey
|
||
|
|
and l_suppkey = ps_suppkey
|
||
|
|
and l_shipdate >= date ':2'
|
||
|
|
and l_shipdate < ADD_YEARS(date ':2', 1)
|
||
|
|
)
|
||
|
|
)
|
||
|
|
and s_nationkey = n_nationkey
|
||
|
|
and n_name = ':3'
|
||
|
|
order by
|
||
|
|
s_name;
|
||
|
|
--end q20
|