CODEWITHSUNDEEP
oracle-databasetpc
Maltem
Maltem

Reputation: 29

TPC-H queries. Testing and how to run them?

So I have generated the TPC-H data. Now I am trying to run its queries on the database but I can't figure out if I should use some specific values or I should somehow generate them.

For example, let's consider

:x
:o
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
lineitem

where
l_shipdate <= date '1998-12-01' - interval ':1' day (3)

group by
l_returnflag,
l_linestatus

order by
l_returnflag,
l_linestatus;
:n-1

Here I don't understand what are the :x :o :n-1 values. When I try to run this code I am asked to input the values for each of them but I don't know what I should input. Or should I just enter a 90 instead of ':1' for example?

I am trying to do it in oracle.

Upvotes: 0

Views: 527

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

This is just an Oracle point of view.

As of this:

l_shipdate <= date '1998-12-01' - interval ':1' day (3)

and your question of entering some value for :1, yes - you should do that. For example (ran in SQL*Plus), it subtracts 90 days from sysdate (today's date):

SQL> select sysdate,
  2         sysdate - interval '&1' day (3)
  3  from dual;
Enter value for 1: 90
old   2:        sysdate - interval '&1' day (3)
new   2:        sysdate - interval '90' day (3)

SYSDATE    SYSDATE-IN
---------- ----------
03.01.2021 05.10.2020

SQL>

As of other values (x, o, n-1) - no idea. What happens if you remove those lines?

Upvotes: 0

Related Questions