Repository navigation
Expand file tree
/
Copy path12.sql
More file actions
94 lines (90 loc) · 1.87 KB
/
Copy path12.sql
File metadata and controls
94 lines (90 loc) · 1.87 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- $ID$
-- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
-- Functional Query Definition
-- Approved February 1998
:x
:o
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in (':1', ':2')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date ':3'
and l_receiptdate < date ':3' + interval '1' year
group by
l_shipmode
order by
l_shipmode;
:n -1
GetColumnRowsByFTSCompare
TCN = 1 (orders.o_orderkey)
SCN = 1
BOP = nil
Arg32 = nil
COP32 = nil
RRI = 1
SendStackToArg32
SCN = 1
RSPwRID = PREV-0
GetColumnRowsByIndexCompare
TCN = 2 (lineitem.l_orderkey)
SCN = 1
BOP = OR
Arg32 = PREV-0
COP32 = EQ
RRI = 1
FilterResultStacksByColumn (o_orderkey = l_orderkey)
RSP1 = PREV-2
RSP2 = PREV-0
// This is not right...we need a new macro that takes an RSP and a filter
GetColumnRowsByFTSCompare
TCN = 3 (lineitem.l_shipmode)
SCN = 1
BOP = OR
Arg32 = ':1', ':2'
COP32 = EQ, EQ
RRI = 1
FilterResultStacksByColumn ((o_orderkey = l_orderkey) and (l_shipmode in (':1', ':2')))
RSP1 = PREV-1
RSP2 = PREV-0
GetColumnRowsByFTSCompare
TCN = 4 (lineitem.l_commitdate)
SCN = 1
BOP = nil
Arg32 = nil
COP32 = nil
RRI = 1
SendStackToArg32
SCN = 1
RSPwRID = PREV-0
GetColumnRowsByIndexCompare
TCN = 5 (lineitem.l_recieptdate)
SCN = 1
BOP = OR
Arg32 = PREV-0
COP32 = LT
RRI = 1
FilterResultStacksByColumn (l_commitdate < l_recieptdate)
RSP1 = PREV-2
RSP2 = PREV-0
FilterResultStacksByColumn ((o_orderkey = l_orderkey) and (l_shipmode in (':1', ':2')) and (l_commitdate < l_recieptdate))
RSP1 = PREV-4
RSP2 = PREV-0
// The rest of the filters are the same...