"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:3gfbe2Fc4r95U1@individual.net...[color=blue]
> l_shipdate <= date('1998-12-01') - 69 days
>
> Someone must have done some really creative thinking with the generated
> query ;-)
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab[/color]
If anyone wants to see the all actual queries that IBM used for the TPC-H
benchmark they are documented teh following link (posted on the TPC
website):
http://www.tpc.org/results/FDR/tpch/...030810.fdr.pdf
The query in question appears to be Query 1 of the TPC-H benchmark which is
documented on page 92 of the above referenced document.
Here is the entire query #1:
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as
sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax))
as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
tpcd.lineitem
where
l_shipdate <= date ('1998-12-01') - 90 day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
On page 17 of the above linked document, the query modifications made by IBM
are disclosed in accordance with Clause 2.2.3 of the TPC-H benchmark rules.
Appendix C.1, "Qualification Queries," contains the output for each of the
queries. The functional query definitions and variants used in this
disclosure use the following minor query modifications:
- Table names and view names are fully qualified. For example, the nation
table is referred to as "TPCD.NATION."
- The standard IBM SQL date syntax is used for date arithmetic. For example,
DATE('1996-01-01') +3 MONTHS.
- The semicolon (;) is used as a command delimiter.
