1

I am using TPC-H Benchmark to do some studies, and i have been having some difficulties to understand and find the correct functions to use on sql server.

What is the meaning of :n -1 at the end of the file? What is the meaning of this c_mktsegment = ':1'. How to do this operation,

and o_orderdate < date ':2'

I am going to save a file just as example.

-- $ID$ -- TPC-H/TPC-R Shipping Priority Query (Q3) -- Functional Query Definition -- Approved February 1998

    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = ':1'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date ':2'
    and l_shipdate > date ':2'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate;
:n 10```

I did try to find some examples saying the conversion of the queries to sql server queries, but it seems its already sql server queries, but still, couldnt find the right conversion as if i try toi research some lines of code, i cant find anything.

when i run the code it shows me for example
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ':2'.
Márcio Carvalho
  • 11
  • 2
  • 1
    it looks like some sort of parameter substitution by TPC. :2 might mean `insert the value of 2nd parameter here`. You need to review the 'calling' code to understand what date is to be provided & how. – tinazmu Dec 13 '22 at 02:16
  • 1
    Normally, if o_orderdate is of type date/datetime, you can enter the `:2` criteria using ISO format, '2022-12-13' (in yyyy-mm-dd format), without the 'date' word in front of it. – tinazmu Dec 13 '22 at 02:28
  • That was it, TPC-H queries are sort of a template, and it's asking you to put data where it says :2 or :1 or :value – Márcio Carvalho Dec 13 '22 at 03:14
  • Did this help to resolve your problem? if not, explain a bit more: provide parameter values; show us where that `:n -1` appears (we can't see it in the query text) – tinazmu Dec 13 '22 at 03:32
  • The `:2` syntax is what Oracle uses for "bind variables" - which are parameter placeholders. Not sure what `:n 10` would be. Some indicator of number of executions maybe? – Martin Smith Dec 13 '22 at 12:57
  • That's right, but it helps me with all these answers! :) Thanks! – Márcio Carvalho Dec 19 '22 at 18:34

0 Answers0