By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

SQL - Unexpected Token "date/interval"

P: n/a
Hello All,

While doing some TPC benchmark testing on DB2-UDB 8.2, I face the
following problem when running this query:

select *
from lineitem
where
l_shipdate <= date '1998-12-01' - interval '69' day (3)
SQL0104N An unexpected token "'1998-12-01'" was found following "e
l_shipdate
<= date". Expected tokens may include: "<space>". SQLSTATE=42601
I tried to remove the "date" and I got the following error:
SQL0104N An unexpected token "'69' day" was found following "98-12-01'
-
interval". Expected tokens may include: "<space>". SQLSTATE=42601

I have setup the TPC Query Generator to generate "DB2 Queries". Yet, I
face the above problem.

Any help would be appreciated.

Thank you.

Best Regards,
Salem

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
UnixSlaxer wrote:
Hello All,

While doing some TPC benchmark testing on DB2-UDB 8.2, I face the
following problem when running this query:

select *
from lineitem
where
l_shipdate <= date '1998-12-01' - interval '69' day (3)
SQL0104N An unexpected token "'1998-12-01'" was found following "e
l_shipdate
<= date". Expected tokens may include: "<space>". SQLSTATE=42601
I tried to remove the "date" and I got the following error:
SQL0104N An unexpected token "'69' day" was found following "98-12-01'
-
interval". Expected tokens may include: "<space>". SQLSTATE=42601

I have setup the TPC Query Generator to generate "DB2 Queries". Yet, I
face the above problem.

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
Nov 12 '05 #2

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3g************@individual.net...
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


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.
Nov 12 '05 #3

P: n/a
Thank you all for your responses.

Best Regards,
Salem

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.