Google Git
Sign in
apache / trafodion / 927b9384b9e1957e95487792fcbed6cac09ac22a / . / core / sql / regress / executor / EXPECTED063
blob: 07d3e5f6957f71769ea22dd1b11f45ac8eaa3630 [file] [log] [blame]
>>?section crtab
>>CREATE TABLE t063t1
+>(a smallint, b int, c int, d date, e char(8), g char(2));
--- SQL operation complete.
>>
>>insert into t063t1 values
+>( 1,10,110, DATE '1980-11-03','A STRING','G1'),
+>( 2,18,112, DATE '1980-12-03','B STRING','G2'),
+>( 3,27,116, DATE '1981-01-03','C STRING','G3'),
+>( 4,35,122, DATE '1981-02-03','D STRING','G4'),
+>( 5,45,130, DATE '1981-03-03','E STRING','G5'),
+>( 6,30,140, DATE '1981-04-03','F STRING','G1'),
+>( 7,35,152, DATE '1981-05-03','G STRING','G2'),
+>( 8,40,166, DATE '1981-06-03','H STRING','G3'),
+>( 9,45,182, DATE '1981-07-03','I STRING','G4'),
+>(10,22,110, DATE '1981-08-03','J STRING','G5'),
+>(11,26,112, DATE '1981-09-03','K STRING','G1'),
+>(12,30,116, DATE '1981-10-03','L STRING','G2'),
+>(13,35,122, DATE '1981-11-03','M STRING','G3'),
+>(14,38,130, DATE '1981-12-03','N STRING','G4'),
+>(15,45,140, DATE '1982-01-03','O STRING','G5'),
+>(16,23,152, DATE '1982-02-03','P STRING','G1'),
+>(17,25,166, DATE '1982-03-03','Q STRING','G2'),
+>(18,27,182, DATE '1982-04-03','R STRING','G3'),
+>(19,29,200, DATE '1982-05-03','S STRING','G4'),
+>(20,22,112, DATE '1982-06-03','T STRING','G5'),
+>(21,24,116, DATE '1982-07-03','U STRING','G1'),
+>(22,26,122, DATE '1982-08-03','V STRING','G2'),
+>(23,28,130, DATE '1982-09-03','W STRING','G3'),
+>(24,30,140, DATE '1982-10-03','X STRING','G4'),
+>(25,32,152, DATE '1982-11-03','Y STRING','G5');
--- 25 row(s) inserted.
>>
>>
>>?section seltab
>>SELECT RUNNINGCOUNT(*) FROM t063t1 SEQUENCE BY A;
(EXPR)
--------------------
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
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGCOUNT(*), (SELECT COUNT(*) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGCOUNT(D) FROM t063t1 SEQUENCE BY A;
(EXPR)
--------------------
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
--- 25 row(s) selected.
>>
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGCOUNT(D), (SELECT COUNT(D) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGMAX(D) FROM t063t1 SEQUENCE BY A;
(EXPR)
----------
1980-11-03
1980-12-03
1981-01-03
1981-02-03
1981-03-03
1981-04-03
1981-05-03
1981-06-03
1981-07-03
1981-08-03
1981-09-03
1981-10-03
1981-11-03
1981-12-03
1982-01-03
1982-02-03
1982-03-03
1982-04-03
1982-05-03
1982-06-03
1982-07-03
1982-08-03
1982-09-03
1982-10-03
1982-11-03
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGMAX(D), (SELECT MAX(D) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGMIN(D) FROM t063t1 SEQUENCE BY A;
(EXPR)
----------
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
1980-11-03
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGMIN(D), (SELECT MIN(D) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGSUM(C) FROM t063t1 SEQUENCE BY A;
(EXPR)
--------------------
110
222
338
460
590
730
882
1048
1230
1340
1452
1568
1690
1820
1960
2112
2278
2460
2660
2772
2888
3010
3140
3280
3432
--- 25 row(s) selected.
>>
>>--- Is semantically equivalent to:
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGSUM(C), (SELECT SUM(C) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGAVG(C) FROM t063t1 SEQUENCE BY A;
(EXPR)
--------------------
110
111
112
115
118
121
126
131
136
134
132
130
130
130
130
132
134
136
140
138
137
136
136
136
137
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGAVG(C), (SELECT AVG(C) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGVARIANCE(C) FROM t063t1 SEQUENCE BY A;
(EXPR)
-------------------------
0.00000000000000000E+000
2.00000000000000000E+000
9.33333333333212032E+000
2.80000000000000032E+001
6.60000000000000000E+001
1.33466666666665712E+002
2.42666666666666688E+002
4.08000000000000000E+002
6.46000000000000000E+002
6.45333333333333376E+002
6.24799999999999872E+002
5.89333333333335168E+002
5.46000000000000000E+002
5.04000000000000000E+002
4.74666666666667392E+002
4.71466666666666624E+002
5.10000000000000064E+002
6.08000000000000000E+002
7.85333333333333376E+002
7.83199999999999360E+002
7.68361904761905280E+002
7.42727272727274496E+002
7.10988142292491520E+002
6.80579710144929280E+002
6.61626666666665728E+002
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGVARIANCE(C), (SELECT VARIANCE(C) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGSTDDEV(C) FROM t063t1 SEQUENCE BY A;
(EXPR)
-------------------------
0.00000000000000000E+000
1.41421356237309520E+000
3.05505046330369472E+000
5.29150262212918144E+000
8.12403840463596032E+000
1.15527774438299328E+001
1.55777619273972320E+001
2.01990098767241568E+001
2.54165300542776672E+001
2.54034118443435360E+001
2.49959996799487872E+001
2.42761886080442048E+001
2.33666428910958464E+001
2.24499443206436480E+001
2.17868461844909376E+001
2.17132831848770976E+001
2.25831795812724288E+001
2.46576560118759072E+001
2.80237994093116064E+001
2.79857106395388800E+001
2.77193417086680736E+001
2.72530231850940640E+001
2.66643609016321920E+001
2.60879226874224192E+001
2.57221046313606656E+001
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGSTDDEV(C), (SELECT STDDEV(C) FROM t063t1 WHERE a <= T1.a)
+>FROM t063t1 T1
+>SEQUENCE BY A) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT RUNNINGRANK(C) FROM t063t1 SEQUENCE BY C;
(EXPR)
--------------------
1
1
3
3
3
6
6
6
9
9
9
12
12
12
15
15
15
18
18
18
21
21
23
23
25
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT RUNNINGRANK(C), (SELECT case when sum(cnt) is null
+> then 0
+> else sum(cnt) end + 1
+> FROM (select c, cast(count(*) as integer) cnt
+> from t063t1 WHERE c < T1.c group by c) G)
+>FROM t063t1 T1
+>SEQUENCE BY C) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>SELECT C, ROWS SINCE CHANGED (C) FROM t063t1 SEQUENCE BY C;
C (EXPR)
----------- --------------------
110 1
110 2
112 1
112 2
112 3
116 1
116 2
116 3
122 1
122 2
122 3
130 1
130 2
130 3
140 1
140 2
140 3
152 1
152 2
152 3
166 1
166 2
182 1
182 2
200 1
--- 25 row(s) selected.
>>SELECT C, D, ROWS SINCE CHANGED (C,D) FROM t063t1 SEQUENCE BY C,D;
C D (EXPR)
----------- ---------- --------------------
110 1980-11-03 1
110 1981-08-03 1
112 1980-12-03 1
112 1981-09-03 1
112 1982-06-03 1
116 1981-01-03 1
116 1981-10-03 1
116 1982-07-03 1
122 1981-02-03 1
122 1981-11-03 1
122 1982-08-03 1
130 1981-03-03 1
130 1981-12-03 1
130 1982-09-03 1
140 1981-04-03 1
140 1982-01-03 1
140 1982-10-03 1
152 1981-05-03 1
152 1982-02-03 1
152 1982-11-03 1
166 1981-06-03 1
166 1982-03-03 1
182 1981-07-03 1
182 1982-04-03 1
200 1982-05-03 1
--- 25 row(s) selected.
>>
>>SELECT status, count(*)
+>FROM
+>(SELECT CASE WHEN v = ev THEN 'PASS' ELSE 'FAIL' END
+>FROM
+>(SELECT ROWS SINCE CHANGED(C),
+> ROWS SINCE INCLUSIVE ((C <> offset(C,1)) OR (offset(C,1) is NULL)) + 1
+>FROM t063t1 T1
+>SEQUENCE BY C) T2(v,ev)) T3(status)
+>GROUP BY status;
STATUS (EXPR)
------ --------------------
PASS 25
--- 1 row(s) selected.
>>
>>-- Correlated Subquery
>>
>>SELECT a, RUNNINGMAX(b), (SELECT MAX(B) from t063t1 where (a <= S.a)) from t063t1 S SEQUENCE BY A;
A (EXPR) (EXPR)
------ ----------- -----------
1 10 10
2 18 18
4 35 35
9 45 45
10 45 45
12 45 45
13 45 45
14 45 45
17 45 45
19 45 45
23 45 45
3 27 27
5 45 45
6 45 45
7 45 45
8 45 45
11 45 45
15 45 45
16 45 45
18 45 45
20 45 45
21 45 45
22 45 45
24 45 45
25 45 45
--- 25 row(s) selected.
>>
>>SELECT a, (SELECT SUM(B) from t063t1 where (a <= S.a)), y
+>FROM t063t1 S, (SELECT a, RUNNINGSUM(B) from t063t1 sequence by a) AS T(x, y)
+>where a = x order by a;
A (EXPR) Y
------ -------------------- --------------------
1 10 10
2 28 28
3 55 55
4 90 90
5 135 135
6 165 165
7 200 200
8 240 240
9 285 285
10 307 307
11 333 333
12 363 363
13 398 398
14 436 436
15 481 481
16 504 504
17 529 529
18 556 556
19 585 585
20 607 607
21 631 631
22 657 657
23 685 685
24 715 715
25 747 747
--- 25 row(s) selected.
>>
>>-- MOVINGMIN/MOVINGMAX
>>
>>SELECT a, MOVINGMIN(b, 5), (SELECT MIN(B) from t063t1 where (a between S.a-4 and S.a)) from t063t1 S SEQUENCE BY A;
A (EXPR) (EXPR)
------ ----------- -----------
1 10 10
2 10 10
3 10 10
4 10 10
5 10 10
6 18 18
7 27 27
8 30 30
9 30 30
10 22 22
11 22 22
12 22 22
13 22 22
14 22 22
15 26 26
16 23 23
17 23 23
18 23 23
19 23 23
20 22 22
21 22 22
22 22 22
23 22 22
24 22 22
25 24 24
--- 25 row(s) selected.
>>SELECT a, MOVINGMAX(c, 5), (SELECT MAX(C) from t063t1 where (a between S.a-4 and S.a)) from t063t1 S SEQUENCE BY A;
A (EXPR) (EXPR)
------ ----------- -----------
1 110 110
2 112 112
3 116 116
4 122 122
5 130 130
6 140 140
7 152 152
8 166 166
9 182 182
10 182 182
11 182 182
12 182 182
13 182 182
14 130 130
15 140 140
16 152 152
17 166 166
18 182 182
19 200 200
20 200 200
21 200 200
22 200 200
23 200 200
24 140 140
25 152 152
--- 25 row(s) selected.
>>
>>-- Test semantic equivalences and DIFFs of Date-Time values
>>
>>SELECT DIFF1(B), B-OFFSET(B, 1) FROM t063t1 SEQUENCE BY A;
(EXPR) (EXPR)
-------------------- --------------------
? ?
8 8
9 9
8 8
10 10
-15 -15
5 5
5 5
5 5
-23 -23
4 4
4 4
5 5
3 3
7 7
-22 -22
2 2
2 2
2 2
-7 -7
2 2
2 2
2 2
2 2
2 2
--- 25 row(s) selected.
>>SELECT DIFF2(B), DIFF1(B)-OFFSET(DIFF1(B), 1) FROM t063t1 SEQUENCE BY A;
(EXPR) (EXPR)
-------------------- --------------------
? ?
? ?
1 1
-1 -1
2 2
-25 -25
20 20
0 0
0 0
-28 -28
27 27
0 0
1 1
-2 -2
4 4
-29 -29
24 24
0 0
0 0
-9 -9
9 9
0 0
0 0
0 0
0 0
--- 25 row(s) selected.
>>SELECT DIFF1(B,D) FROM t063t1 SEQUENCE BY A;
(EXPR)
---------------------
?
.2666666
.2903225
.2580645
.3571428
-.4838709
.1666666
.1612903
.1666666
-.7419354
.1290322
.1333333
.1612903
.1000000
.2258064
-.7096774
.0714285
.0645161
.0666666
-.2258064
.0666666
.0645161
.0645161
.0666666
.0645161
--- 25 row(s) selected.
>>SELECT DIFF1(B,D), DIFF1(B)/CAST(DIFF1(D) AS LARGEINT) FROM t063t1 SEQUENCE BY A;
(EXPR) (EXPR)
--------------------- ---------------------
? ?
.2666666 .2666666
.2903225 .2903225
.2580645 .2580645
.3571428 .3571428
-.4838709 -.4838709
.1666666 .1666666
.1612903 .1612903
.1666666 .1666666
-.7419354 -.7419354
.1290322 .1290322
.1333333 .1333333
.1612903 .1612903
.1000000 .1000000
.2258064 .2258064
-.7096774 -.7096774
.0714285 .0714285
.0645161 .0645161
.0666666 .0666666
-.2258064 -.2258064
.0666666 .0666666
.0645161 .0645161
.0645161 .0645161
.0666666 .0666666
.0645161 .0645161
--- 25 row(s) selected.
>>SELECT DIFF2(B,D), DIFF2(B)/CAST(DIFF1(D) AS LARGEINT) FROM t063t1 SEQUENCE BY A;
(EXPR) (EXPR)
--------------------- ---------------------
? ?
? ?
.032258 .032258
-.032258 -.032258
.071428 .071428
-.806451 -.806451
.666666 .666666
.000000 .000000
.000000 .000000
-.903225 -.903225
.870967 .870967
.000000 .000000
.032258 .032258
-.066666 -.066666
.129032 .129032
-.935483 -.935483
.857142 .857142
.000000 .000000
.000000 .000000
-.290322 -.290322
.300000 .300000
.000000 .000000
.000000 .000000
.000000 .000000
.000000 .000000
--- 25 row(s) selected.
>>
>>?section negtests
>>-- Negative Tests
>>-- should return error 4108
>>SELECT a, c, runningavg(c), rows since (this (c) > runningavg(this(c))) from t063t1 sequence by a ;
*** ERROR[4108] Inside a ROWS SINCE, another sequence function contained an invalid reference to the THIS function.
*** ERROR[8822] The statement was not prepared.
>>
>>-- should return error 4109
>>SELECT a, b, RUNNINGAVG(c) from t063t1 WHERE a > 5 and RUNNINGAVG(c) > 130 sequence by a;
*** ERROR[4109] Sequence functions are placed incorrectly: RUNNINGAVG(TRAFODION.SCH.T063T1.C).
*** ERROR[8822] The statement was not prepared.
>>
>>-- should return 4110
>>SELECT a, b, RUNNINGAVG(c) from t063t1 WHERE a > 5;
*** ERROR[4110] The query contains sequence functions but no SEQUENCE BY clause: RUNNINGAVG(TRAFODION.SCH.T063T1.C).
*** ERROR[8822] The statement was not prepared.
>>
>>-- should return 4111
>>select a, b, c from t063t1 sequence by a;
*** ERROR[4111] The query contains a SEQUENCE BY clause but no sequence functions.
*** ERROR[8822] The statement was not prepared.
>>
>>--
>>-- Invalid arguments:
>>--
>>-- first argument invalid
>>select runningsum(d) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select runningavg(d) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select movingsum(d, 5) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select movingavg(d, 5) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select runningsum(e) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select runningavg(e) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select movingsum(e, 5) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>select movingavg(e, 5) from t063t1 sequence by a;
*** ERROR[4038] The operand of an AVG or SUM function must be numeric or interval.
*** ERROR[8822] The statement was not prepared.
>>
>>-- second argument invalid
>>select movingsum(5, d) from t063t1 sequence by a;
*** ERROR[4052] The second operand of function MOVINGSUM must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select movingavg(5, d) from t063t1 sequence by a;
*** ERROR[4052] The second operand of function MOVINGAVG must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>select movingsum(5, e) from t063t1 sequence by a;
*** ERROR[4052] The second operand of function MOVINGSUM must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select movingavg(5, e) from t063t1 sequence by a;
*** ERROR[4052] The second operand of function MOVINGAVG must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>-- first argument invalid
>>select diff1(e) from t063t1 sequence by a;
*** ERROR[4059] The first operand of function DIFF1 must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select diff2(e) from t063t1 sequence by a;
*** ERROR[4059] The first operand of function DIFF2 must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select diff1(e,c) from t063t1 sequence by a;
*** ERROR[4059] The first operand of function DIFF1 must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select diff2(e,c) from t063t1 sequence by a;
*** ERROR[4059] The first operand of function DIFF2 must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>--second argument invalid
>>select diff1(c, e) from t063t1 sequence by a;
*** ERROR[4052] The second operand of function DIFF1 must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select diff2(c, e) from t063t1 sequence by a;
*** ERROR[4052] The second operand of function DIFF2 must be numeric.
*** ERROR[8822] The statement was not prepared.
>>
>>?section derived
>>-- Derived tables
>>
>>-- should get right answer
>>SELECT aa, bb, cc
+>FROM (SELECT a, b, RUNNINGAVG(c) from t063t1 WHERE a > 5 sequence by a) AS T(aa,bb,cc)
+>WHERE cc > 130;
AA BB CC
------ ----------- --------------------
6 30 140
7 35 146
8 40 152
9 45 160
10 22 150
11 26 143
12 30 139
13 35 137
14 38 136
15 45 137
16 23 138
17 25 140
18 27 143
19 29 147
20 22 145
21 24 143
22 26 142
23 28 141
24 30 141
25 32 142
--- 20 row(s) selected.
>>
>>SELECT g, AVG(c) FROM t063t1 GROUP BY g;
G (EXPR)
-- --------------------
G3 143
G1 126
G2 133
G5 128
G4 154
--- 5 row(s) selected.
>>
>>SELECT gg, MOVINGSUM (avgC,2) as MSUM
+>FROM (SELECT g, AVG(c) FROM t063t1 GROUP BY g) AS T2 (gg, avgC)
+>SEQUENCE BY gg;
GG MSUM
-- --------------------
G1 126
G2 259
G3 276
G4 298
G5 283
--- 5 row(s) selected.
>>
>>?section scalar_agg
>>--
>>-- The following all return NO rows from the sequence node
>>--
>>SELECT MAX(MMM)
+>FROM
+>(SELECT MOVINGSUM (avgC,2) as MSUM
+>FROM (SELECT g, AVG(c) FROM t063t1 GROUP BY g) AS T2 (gg, avgC)
+>SEQUENCE BY gg) as T3 (MMM);
(EXPR)
--------------------
298
--- 1 row(s) selected.
>>
>>SELECT COUNT(MMM)
+>FROM
+>(SELECT MOVINGSUM (avgC,2) as MSUM
+>FROM (SELECT g, AVG(c) FROM t063t1 GROUP BY g) AS T2 (gg, avgC)
+>SEQUENCE BY gg) as T3 (MMM);
(EXPR)
--------------------
5
--- 1 row(s) selected.
>>
>>SELECT COUNT(*)
+>FROM
+>(SELECT MOVINGSUM (avgC,2) as MSUM
+>FROM (SELECT g, AVG(c) FROM t063t1 GROUP BY g) AS T2 (gg, avgC)
+>SEQUENCE BY gg) as T3 (MMM);
(EXPR)
--------------------
5
--- 1 row(s) selected.
>>
>>SELECT COUNT(*)
+>FROM (SELECT g, AVG(c) FROM t063t1 GROUP BY g) AS T;
(EXPR)
--------------------
5
--- 1 row(s) selected.
>>
>>SELECT COUNT(MMM)
+>FROM
+>(SELECT MOVINGSUM (c,2) as MSUM FROM t063t1 sequence by a) as T (MMM);
(EXPR)
--------------------
25
--- 1 row(s) selected.
>>
>>SELECT AVG(MOVINGSUM (c,2)) ASUM FROM t063t1 sequence by a;
ASUM
--------------------
268
--- 1 row(s) selected.
>>
>>--
>>-- Derived Table
>>--
>>SELECT aa, cast(bb as numeric(7,2)), cast (cc as numeric (7,2)) FROM
+>(SELECT a, RUNNINGSUM(B), MOVINGAVG(C,5) FROM t063t1 sequence by a) AS T (aa,bb,cc);
AA (EXPR) (EXPR)
------ ------------ ------------
1 10.00 110.00
2 28.00 111.00
3 55.00 112.66
4 90.00 115.00
5 135.00 118.00
6 165.00 124.00
7 200.00 132.00
8 240.00 142.00
9 285.00 154.00
10 307.00 150.00
11 333.00 144.40
12 363.00 137.20
13 398.00 128.40
14 436.00 118.00
15 481.00 124.00
16 504.00 132.00
17 529.00 142.00
18 556.00 154.00
19 585.00 168.00
20 607.00 162.40
21 631.00 155.20
22 657.00 146.40
23 685.00 136.00
24 715.00 124.00
25 747.00 132.00
--- 25 row(s) selected.
>>
>>-- CREATE VIEW
>>#ifMX
>>CREATE VIEW t063v1 (x, y, z) as
+>SELECT a, CAST (RUNNINGSUM(B) AS NUMERIC(7,2)), CAST (MOVINGAVG(C,5) AS NUMERIC(7,2)) FROM t063t1 sequence by a;
--- SQL operation complete.
>>
>>SELECT * from t063v1;
X Y Z
------ ------------ ------------
1 10.00 110.00
2 28.00 111.00
3 55.00 112.66
4 90.00 115.00
5 135.00 118.00
6 165.00 124.00
7 200.00 132.00
8 240.00 142.00
9 285.00 154.00
10 307.00 150.00
11 333.00 144.40
12 363.00 137.20
13 398.00 128.40
14 436.00 118.00
15 481.00 124.00
16 504.00 132.00
17 529.00 142.00
18 556.00 154.00
19 585.00 168.00
20 607.00 162.40
21 631.00 155.20
22 657.00 146.40
23 685.00 136.00
24 715.00 124.00
25 747.00 132.00
--- 25 row(s) selected.
>>
>>--
>>-- Correctly avoids pushing predicate down
>>--
>>SELECT * from t063v1 where x = 10;
X Y Z
------ ------------ ------------
10 307.00 150.00
--- 1 row(s) selected.
>>
>>--
>>--
>>SELECT aa, x, cast(bb as numeric(7,2)), cast (cc as numeric (7,2)) FROM
+>(SELECT a, RUNNINGSUM(B), MOVINGAVG(C,5) FROM t063t1 sequence by a) AS T (aa,bb,cc), t063v1 where aa = x;
AA X (EXPR) (EXPR)
------ ------ ------------ ------------
1 1 10.00 110.00
2 2 28.00 111.00
3 3 55.00 112.66
4 4 90.00 115.00
5 5 135.00 118.00
6 6 165.00 124.00
7 7 200.00 132.00
8 8 240.00 142.00
9 9 285.00 154.00
10 10 307.00 150.00
11 11 333.00 144.40
12 12 363.00 137.20
13 13 398.00 128.40
14 14 436.00 118.00
15 15 481.00 124.00
16 16 504.00 132.00
17 17 529.00 142.00
18 18 556.00 154.00
19 19 585.00 168.00
20 20 607.00 162.40
21 21 631.00 155.20
22 22 657.00 146.40
23 23 685.00 136.00
24 24 715.00 124.00
25 25 747.00 132.00
--- 25 row(s) selected.
>>
>>--
>>--
>>SELECT aa, x, cast(bb as numeric(7,2)), y, cast (cc as numeric (7,2)) FROM
+>(SELECT a, RUNNINGSUM(B), MOVINGAVG(C,5) FROM t063t1 sequence by a) AS T (aa,bb,cc), t063v1 where aa = x;
AA X (EXPR) Y (EXPR)
------ ------ ------------ ------------ ------------
1 1 10.00 10.00 110.00
2 2 28.00 28.00 111.00
3 3 55.00 55.00 112.66
4 4 90.00 90.00 115.00
5 5 135.00 135.00 118.00
6 6 165.00 165.00 124.00
7 7 200.00 200.00 132.00
8 8 240.00 240.00 142.00
9 9 285.00 285.00 154.00
10 10 307.00 307.00 150.00
11 11 333.00 333.00 144.40
12 12 363.00 363.00 137.20
13 13 398.00 398.00 128.40
14 14 436.00 436.00 118.00
15 15 481.00 481.00 124.00
16 16 504.00 504.00 132.00
17 17 529.00 529.00 142.00
18 18 556.00 556.00 154.00
19 19 585.00 585.00 168.00
20 20 607.00 607.00 162.40
21 21 631.00 631.00 155.20
22 22 657.00 657.00 146.40
23 23 685.00 685.00 136.00
24 24 715.00 715.00 124.00
25 25 747.00 747.00 132.00
--- 25 row(s) selected.
>>
>>
>>SELECT aa, x, cast(bb as numeric(7,2)), cast (cc as numeric (7,2)), z FROM
+>(SELECT a, RUNNINGSUM(B), MOVINGAVG(C,5) FROM t063t1 sequence by a) AS T (aa,bb,cc), t063v1 where aa = x;
AA X (EXPR) (EXPR) Z
------ ------ ------------ ------------ ------------
1 1 10.00 110.00 110.00
2 2 28.00 111.00 111.00
3 3 55.00 112.66 112.66
4 4 90.00 115.00 115.00
5 5 135.00 118.00 118.00
6 6 165.00 124.00 124.00
7 7 200.00 132.00 132.00
8 8 240.00 142.00 142.00
9 9 285.00 154.00 154.00
10 10 307.00 150.00 150.00
11 11 333.00 144.40 144.40
12 12 363.00 137.20 137.20
13 13 398.00 128.40 128.40
14 14 436.00 118.00 118.00
15 15 481.00 124.00 124.00
16 16 504.00 132.00 132.00
17 17 529.00 142.00 142.00
18 18 556.00 154.00 154.00
19 19 585.00 168.00 168.00
20 20 607.00 162.40 162.40
21 21 631.00 155.20 155.20
22 22 657.00 146.40 146.40
23 23 685.00 136.00 136.00
24 24 715.00 124.00 124.00
25 25 747.00 132.00 132.00
--- 25 row(s) selected.
>>#ifMX
>>
>>--
>>?section rows_since
>>-- ROWS SINCE
>>
>>SELECT a, c, runningavg(c), rows since (this (c) > runningavg(c)) FROM t063t1 sequence by a;
A C (EXPR) (EXPR)
------ ----------- -------------------- -----------
1 110 110 ?
2 112 111 1
3 116 112 1
4 122 115 1
5 130 118 1
6 140 121 1
7 152 126 1
8 166 131 1
9 182 136 1
10 110 134 ?
11 112 132 9
12 116 130 8
13 122 130 7
14 130 130 7
15 140 130 1
16 152 132 1
17 166 134 1
18 182 136 1
19 200 140 1
20 112 138 18
21 116 137 17
22 122 136 16
23 130 136 16
24 140 136 1
25 152 137 1
--- 25 row(s) selected.
>>SELECT a, c, runningavg(c), rows since inclusive(this (c) > runningavg(c)) FROM t063t1 sequence by a;
A C (EXPR) (EXPR)
------ ----------- -------------------- -----------
1 110 110 ?
2 112 111 0
3 116 112 0
4 122 115 0
5 130 118 0
6 140 121 0
7 152 126 0
8 166 131 0
9 182 136 0
10 110 134 ?
11 112 132 9
12 116 130 8
13 122 130 7
14 130 130 7
15 140 130 0
16 152 132 0
17 166 134 0
18 182 136 0
19 200 140 0
20 112 138 18
21 116 137 17
22 122 136 16
23 130 136 16
24 140 136 0
25 152 137 0
--- 25 row(s) selected.
>>
>>select a, b, ROWS SINCE (a > b), ROWS SINCE INCLUSIVE (a > b) FROM
+>(select aa, bb from (values(1)) as T(a) transpose
+>(2 , 3),
+>(4 , 5),
+>(1 , 6),
+>(8 , 12),
+>(10, 12),
+>(12, 15) as (aa,bb)) as T (a, b) sequence by a;
A B (EXPR) (EXPR)
------ ------ ----------- -----------
1 6 ? ?
2 3 ? ?
4 5 ? ?
8 12 ? ?
10 12 ? ?
12 15 ? ?
--- 6 row(s) selected.
>>
>>select a, b, ROWS SINCE (a < b), ROWS SINCE INCLUSIVE (a < b) FROM
+>(select aa, bb from (values(1)) as T(a) transpose
+>(2 , 3),
+>(4 , 5),
+>(1 , 6),
+>(8 , 12),
+>(10, 12),
+>(12, 15) as (aa,bb)) as T (a, b) sequence by a;
A B (EXPR) (EXPR)
------ ------ ----------- -----------
1 6 ? 0
2 3 1 0
4 5 1 0
8 12 1 0
10 12 1 0
12 15 1 0
--- 6 row(s) selected.
>>
>>-- LAST NOT NULL
>>
>>select a, b, RUNNINGCOUNT(b) as RCOUNT, RUNNINGAVG (b) as RAVG, LASTNOTNULL(b) as LASTNN, RANK(a) RANK FROM
+>(select aa, bb from (values(1)) as T(a) transpose
+>(1, 3),
+>(2, 5),
+>(3, 6),
+>(4, NULL),
+>(5, NULL),
+>(6, 14),
+>(7, NULL),
+>(8, NULL),
+>(9, 15)
+>as (aa,bb)) as T (a, b) sequence by a;
A B RCOUNT RAVG LASTNN RANK
------ ------ -------------------- -------------------- ------ --------------------
1 3 1 3 3 1
2 5 2 4 5 2
3 6 3 4 6 3
4 ? 3 4 6 4
5 ? 3 4 6 5
6 14 4 7 14 6
7 ? 4 7 14 7
8 ? 4 7 14 8
9 15 5 8 15 9
--- 9 row(s) selected.
>>
>>select b, MOVINGCOUNT(*,3) as MCOUNTSTAR, MOVINGCOUNT(b,3) as MCOUNT, MOVINGAVG (b,3) as MAVG FROM
+>(select aa, bb from (values(1)) as T(a) transpose
+>(1, 3),
+>(2, 5),
+>(3, 6),
+>(4, NULL),
+>(5, NULL),
+>(6, 14),
+>(7, NULL),
+>(8, NULL),
+>(9, 15)
+>as (aa,bb)) as T (a, b) sequence by a;
B MCOUNTSTAR MCOUNT MAVG
------ -------------------- -------------------- --------------------
3 1 1 3
5 2 2 4
6 3 3 4
? 3 2 5
? 3 1 6
14 3 1 14
? 3 1 14
? 3 1 14
15 3 1 15
--- 9 row(s) selected.
>>
>>--
>>--
>>-- Test for correct scoping of GROUP BY and SEQUENCE BY in same query
>>--
>>
>>--
>>--
>>-- Should return 3 rows
>>--
>>select a, avg(b), avg (runningsum(a))
+>from
+>(values
+>(1234, 23.000, 11),
+>(1234, 24.000, 17),
+>(1234, 25.000, 14),
+>(1234, 26.000, 10),
+>(1234, 27.000, 19),
+>(1234, 28.000, 14),
+>(1234, 29.000, 18),
+>(1234, 30.000, 18),
+>(1234, 31.000, 12),
+>(2345, 23.000, 14),
+>(2345, 24.000, 15),
+>(2345, 25.000, 15),
+>(2345, 26.000, 11),
+>(2345, 27.000, 17),
+>(2345, 28.000, 11),
+>(2345, 29.000, 11),
+>(2345, 30.000, 15),
+>(2345, 31.000, 12),
+>(3456, 23.000, 17),
+>(3456, 24.000, 16),
+>(3456, 25.000, 11),
+>(3456, 26.000, 17),
+>(3456, 27.000, 14),
+>(3456, 28.000, 10),
+>(3456, 29.000, 19),
+>(3456, 30.000, 14),
+>(3456, 31.000, 18))
+>as T(a, b, c)
+>sequence by a, b
+>group by a;
A (EXPR) (EXPR)
------ --------------------- --------------------
1234 27.000 6170
2345 27.000 22831
3456 27.000 49491
--- 3 row(s) selected.
>>
>>--
>>-- Error 4109: Sequence function placed incorrectly
>>-- Genesis case 10-990823-0045
>>--
>>select a, avg(b), runningsum(a)
+>from
+>(values
+>(1234, 23, 11),
+>(1234, 24, 17),
+>(1234, 25, 14),
+>(1234, 26, 10),
+>(1234, 27, 19),
+>(1234, 28, 14),
+>(1234, 29, 18),
+>(1234, 30, 18),
+>(1234, 31, 12),
+>(2345, 23, 14),
+>(2345, 24, 15),
+>(2345, 25, 15),
+>(2345, 26, 11),
+>(2345, 27, 17),
+>(2345, 28, 11),
+>(2345, 29, 11),
+>(2345, 30, 15),
+>(2345, 31, 12),
+>(3456, 23, 17),
+>(3456, 24, 16),
+>(3456, 25, 11),
+>(3456, 26, 17),
+>(3456, 27, 14),
+>(3456, 28, 10),
+>(3456, 29, 19),
+>(3456, 30, 14),
+>(3456, 31, 18))
+>as T(a, b, c)
+>sequence by a, b
+>group by a;
*** ERROR[4109] Sequence functions are placed incorrectly: RUNNINGSUM(A).
*** ERROR[8822] The statement was not prepared.
>>
>>--
>>-- Tests valid ROWS SINCE nesting
>>-- Genesis case 10-990301-0576
>>--
>>select a, b, c, movingavg(c, 3, rows since (this (a) <> a))
+>from
+>(values
+>(1234, 23, 11.000),
+>(1234, 24, 17.000),
+>(2345, 23, 14.000),
+>(2345, 24, 15.000),
+>(2345, 25, 15.000),
+>(2345, 26, 11.000))
+>as T(a, b, c)
+>sequence by a, b;
A B C (EXPR)
------ ------ ------------ ---------------------
1234 23 11.000 11.000
1234 24 17.000 14.000
2345 23 14.000 14.000
2345 24 15.000 14.500
2345 25 15.000 14.666
2345 26 11.000 13.666
--- 6 row(s) selected.
>>
>>
>>--
>>-- Tests correct trapping for divide by zero
>>--
>>-- Genesis case 10-990219-0316
>>--
>>
>>select a, diff1(b, c)
+>from
+>(values
+>(1234, 23, 11),
+>(1234, 24, 12),
+>(1234, 25, 13),
+>(2345, 23, 14),
+>(2345, 24, 14),
+>(2345, 25, 15),
+>(2345, 26, 15),
+>(2345, 27, 16),
+>(2345, 27, 16),
+>(3452, 23, 17),
+>(3452, 24, 17),
+>(3452, 25, 17),
+>(3452, 26, 18),
+>(3452, 27, 18),
+>(3452, 28, 18),
+>(3452, 29, 19),
+>(3452, 30, 19),
+>(3452, 31, 19))
+>as T(a, b, c)
+>sequence by a, b;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>
>>--
>>--
>>-- Test for non-Pcode Evaluation
>>--
>>-- Genesis case 10-990729-3914
>>--
>>create table t063t2 (a int, b int, c smallint, d largeint);
--- SQL operation complete.
>>
>>insert into t063t2 values
+> (2, 74, 27648, 498921136),
+> (80, 24011, 22223, 859834),
+> (89, 28238, 28519, 285854226),
+> (51, 15294, 18450, 100322507),
+> (33, 9031, 32354, 1130723134),
+> (53, 15474, 5573, 476322978),
+> (50, 15250, 24665, 196331131),
+> (71, null, 27138, 37182688),
+> (76, 23010, 6585, 666815330),
+> (47, 13457, 32039, 186130057);
--- 10 row(s) inserted.
>>
>>control query default PCODE_OPT_LEVEL 'OFF';
--- SQL operation complete.
>>
>> select a, b,
+> runningcount(*) as rcount_star,
+> runningsum(b) as rsum_b,
+> rank(a) as rank
+> from t063t2
+> sequence by a;
A B RCOUNT_STAR RSUM_B RANK
----------- ----------- -------------------- -------------------- --------------------
2 74 1 74 1
33 9031 2 9105 2
47 13457 3 22562 3
50 15250 4 37812 4
51 15294 5 53106 5
53 15474 6 68580 6
71 ? 7 68580 7
76 23010 8 91590 8
80 24011 9 115601 9
89 28238 10 143839 10
--- 10 row(s) selected.
>>
>> select b, runningsum(b), runningcount(*), rank(a)
+> from t063t2
+> sequence by a;
B (EXPR) (EXPR) (EXPR)
----------- -------------------- -------------------- --------------------
74 74 1 1
9031 9105 2 2
13457 22562 3 3
15250 37812 4 4
15294 53106 5 5
15474 68580 6 6
? 68580 7 7
23010 91590 8 8
24011 115601 9 9
28238 143839 10 10
--- 10 row(s) selected.
>>
>>control query default PCODE_OPT_LEVEL '2';
--- SQL operation complete.
>>--
>>-- Test for nested sequence functions within derived tables
>>--
>>-- Genesis Case Number: 10-990301-0569
>>
>>create table t063t3 (
+> account_num int,
+> wk int,
+> wkly_ad_clicks int
+> );
--- SQL operation complete.
>>
>>insert into t063t3 values
+> (1234, 23, 11),
+> (1234, 24, 17),
+> (2345, 23, 14),
+> (2345, 24, 15),
+> (2345, 25, 15),
+> (2345, 26, 11);
--- 6 row(s) inserted.
>>
>>select account_num, wk, diff1 (ad_clicks_mov_avg)
+> from (
+> select account_num, wk,
+> movingavg (wkly_ad_clicks, 3,
+> rows since (account_num <> OFFSET (account_num,1)))
+> from t063t3
+> sequence by account_num, wk
+> ) as T1 ( account_num, wk, ad_clicks_mov_avg)
+> sequence by account_num, wk;
ACCOUNT_NUM WK (EXPR)
----------- ----------- --------------------
1234 23 ?
1234 24 3
2345 23 0
2345 24 1
2345 25 0
2345 26 -1
--- 6 row(s) selected.
>>
>>-- Tests for protective sequence function transformations to avoid
>>-- short-circuiting. The generator define GEN_DISABLE_SF_PROTECT disables the
>>-- protective sequence function transformation. At this point, all of
>>-- these tests should fail if the generator define GEN_DISABLE_SF_PROTECT
>>-- is set.
>>--
>>-- Test the ITM_SCALAR_MIN transformation.
>>--
>>-- The second and third arguments of offset are combined using the scalar
>>-- min function. If short-circuiting is allowed, the rows since is not
>>-- computed on each rows and the offset returns the incorrect value.
>>--
>>?section SCBUG_Q1
>>select b,c,offset (c, (rows since inclusive (b = 30)), 99)
+>from t063t1 sequence by a order by a;
B C (EXPR)
----------- ----------- -----------
10 110 ?
18 112 ?
27 116 ?
35 122 ?
45 130 ?
30 140 140
35 152 140
40 166 140
45 182 140
22 110 140
26 112 140
30 116 116
35 122 116
38 130 116
45 140 116
23 152 116
25 166 116
27 182 116
29 200 116
22 112 116
24 116 116
26 122 116
28 130 116
30 140 140
32 152 140
--- 25 row(s) selected.
>>
>>-- Test the ITM_CASE transformation.
>>--
>>?section SCBUG_Q2
>>select offset(runningsum(a),1),
+> offset(case when a < 10 then runningsum(a) else runningsum(a) end, 1)
+>from t063t1 sequence by a order by a;
(EXPR) (EXPR)
-------------------- --------------------
? ?
1 1
3 3
6 6
10 10
15 15
21 21
28 28
36 36
45 45
55 55
66 66
78 78
91 91
105 105
120 120
136 136
153 153
171 171
190 190
210 210
231 231
253 253
276 276
300 300
--- 25 row(s) selected.
>>
>>
>>-- Test the ITM_OR transformation.
>>--
>>-- Each of the rows since expressions in the following query should
>>-- return the same result. However, when short-circuiting is allowed, the
>>-- second rows since gives incorrect results because the sum on the right
>>-- side of the OR is skipped for the first rows because the left side is
>>-- TRUE.
>>--
>>?section SCBUG_Q3
>>select rows since inclusive (runningsum(b) < 300),
+> rows since inclusive (runningsum(b) < 300 OR runningsum(b) < 300)
+>from t063t1 sequence by a order by a;
(EXPR) (EXPR)
----------- -----------
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
--- 25 row(s) selected.
>>
>>-- Test the ITM_AND transformation.
>>--
>>-- Both of the rows since expressions in the following query should
>>-- return the same result. However, when short-circuiting is allowed, the
>>-- second rows since gives incorrect results because the sum on the right
>>-- side of the AND is skipped for the first rows because the left side is
>>-- FALSE.
>>--
>>?section SCBUG_Q4
>>select rows since inclusive (runningsum(b) > 300),
+> rows since inclusive (runningsum(b) > 300 AND runningsum(b) > 300)
+>from t063t1 sequence by a order by a;
(EXPR) (EXPR)
----------- -----------
? ?
? ?
? ?
? ?
? ?
? ?
? ?
? ?
? ?
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
--- 25 row(s) selected.
>>
>>-- Test nested case
>>--
>>?section SCBUG_Q5
>>select offset(runningsum(a),1),
+> offset(case when a < 10 then
+> case when a < 5 then runningsum(a) else runningsum(a) end
+> else runningsum(a) end, 1)
+>from t063t1 sequence by a order by a;
(EXPR) (EXPR)
-------------------- --------------------
? ?
1 1
3 3
6 6
10 10
15 15
21 21
28 28
36 36
45 45
55 55
66 66
78 78
91 91
105 105
120 120
136 136
153 153
171 171
190 190
210 210
231 231
253 253
276 276
300 300
--- 25 row(s) selected.
>>
>>-- Test nested sequence functions
>>--
>>?section SCBUG_Q6
>>select offset(runningsum(a),1),
+> offset(case when a < 10 then runningsum(a) else runningsum(a) end, 1)
+>from
+> (select offset(runningsum(a),1),
+> offset(case when a < 10 then runningsum(a) else runningsum(a) end, 1)
+> from t063t1 sequence by a) T(a,b)
+>sequence by a order by a;
(EXPR) (EXPR)
-------------------- --------------------
? ?
1 1
4 4
10 10
20 20
35 35
56 56
84 84
120 120
165 165
220 220
286 286
364 364
455 455
560 560
680 680
816 816
969 969
1140 1140
1330 1330
1540 1540
1771 1771
2024 2024
2300 2300
2600 2600
--- 25 row(s) selected.
>>
>>--
>>--
>>-- Test for movingsum with all nulls in moving window
>>--
>>-- Genesis Case Number: 10-020725-5529
>>
>>-- The following shows an example of MOVINGSUM with a moving window size of 3 where all
>>-- values (b) are NULL. The second expression shows the correct equivalent MOVINGSUM semantics:
>>
>>select a, b, MOVINGSUM(b,3) as MSUM1, CASE MOVINGCOUNT(b,3) WHEN 0 THEN NULL ELSE MOVINGSUM(b,3) END as MSUM2 FROM
+>(values
+>(8, 1),
+>(9, NULL),
+>(11, NULL),
+>(17, NULL),
+>(33, 6),
+>(34, NULL),
+>(35, NULL),
+>(36, NULL),
+>(37, 7))
+>as T(a,b) sequence by a;
A B MSUM1 MSUM2
------ ------ -------------------- --------------------
8 1 1 1
9 ? 1 1
11 ? 1 1
17 ? ? ?
33 6 6 6
34 ? 6 6
35 ? 6 6
36 ? ? ?
37 7 7 7
--- 9 row(s) selected.
>>
>>--
>>-- The following shows an example of MOVINGAVG with a moving window size of 3 where all
>>-- values (b) are NULL. The second expression shows the correct equivalent MOVINGAVG semantics:
>>
>>select a, b, MOVINGAVG(b,3) as MAVG1, CASE MOVINGCOUNT(b,3) WHEN 0 THEN NULL ELSE MOVINGSUM(b,3)/MOVINGCOUNT(b,3) END as MAVG2 FROM
+>(values
+>(8, 1),
+>(9, NULL),
+>(11, NULL),
+>(17, NULL),
+>(33, 6),
+>(34, NULL),
+>(35, NULL),
+>(36, NULL),
+>(37, 7))
+>as T(a,b) sequence by a;
A B MAVG1 MAVG2
------ ------ -------------------- --------------------
8 1 1 1
9 ? 1 1
11 ? 1 1
17 ? ? ?
33 6 6 6
34 ? 6 6
35 ? 6 6
36 ? ? ?
37 7 7 7
--- 9 row(s) selected.
>>
>>--
>>--
>>-- Test for movingsum with window size of 0
>>--
>>-- The following show examples of MOVINGSUM and MOVINGAVG with a moving window size of 0
>>--
>>
>>select a, b, MOVINGSUM(b,0) as MSUM, MOVINGAVG(b,0) as MAVG FROM
+>(values
+>(8, 1),
+>(9, 2),
+>(11, NULL),
+>(33, 6),
+>(37, 7))
+>as T(a,b) sequence by a;
A B MSUM MAVG
------ ------ -------------------- --------------------
8 1 ? ?
9 2 ? ?
11 ? ? ?
33 6 ? ?
37 7 ? ?
--- 5 row(s) selected.
>>
>>select a, b, c, MOVINGSUM(c,b) as MSUM, MOVINGAVG(c,b) as MAVG FROM
+>(values
+>(1,1, 1),
+>(2,0, 2),
+>(3,2, NULL),
+>(4,0, 6),
+>(5,3, 7))
+>as T(a,b,c) sequence by a;
A B C MSUM MAVG
------ ------ ------ -------------------- --------------------
1 1 1 1 1
2 0 2 ? ?
3 2 ? 2 2
4 0 6 ? ?
5 3 7 13 6
--- 5 row(s) selected.
>>
>>--
>>-- Test for Case-10-021213-4041: MOVINGMIN/MAX do not treat negative window
>>-- sizes properly.
>>--
>>
>>select a, b,
+>movingmax(b,a,2000) as MMAX_ORG,
+>CASE WHEN (a < 0 OR a IS NULL) THEN runningmax(b) ELSE movingmax(b,a) END AS MMAX_CORRECT
+>from (values
+>(2, 1, 1),
+>(2, 2, 2),
+>(2, 3, 3),
+>(2, 16, 4),
+>(-2, 1, 5),
+>(2, 2, 6),
+>(-2, 3, 7),
+>(2, 5, 8),
+>(NULL, 1, 9),
+>(2, 2, 10),
+>(NULL, 3, 11),
+>(2, 2, 12),
+>(2, 3, 13),
+>(2, 26, 14),
+>(-2, 1, 15),
+>(2, 2, 16),
+>(-2, 3, 17),
+>(2, 5, 18),
+>(NULL, 1, 19),
+>(2, 2, 20),
+>(NULL, 3, 21),
+>(2, 2, 22),
+>(2, 3, 23),
+>(2, 36, 24),
+>(-2, 1, 25),
+>(2, 2, 26),
+>(-2, 3, 27),
+>(2, 5, 28),
+>(NULL, 1, 29),
+>(2, 2, 30),
+>(NULL, 3, 31),
+>(2, 2, 32),
+>(2, 3, 33),
+>(2, 46, 34),
+>(-2, 1, 35),
+>(2, 2, 36),
+>(-2, 3, 37),
+>(2, 5, 38),
+>(NULL, 1, 39),
+>(2, 2, 40),
+>(NULL, 3, 41),
+>(2, 2, 42),
+>(2, 3, 43),
+>(2, 56, 44),
+>(-2, 1, 45),
+>(2, 2, 46),
+>(-2, 3, 47),
+>(2, 5, 48),
+>(NULL, 1, 49),
+>(2, 2, 50),
+>(NULL, 3, 51),
+>(2, 2, 52),
+>(2, 3, 53),
+>(2, 6, 54),
+>(-2, 1, 55),
+>(2, 2, 56),
+>(-2, 3, 57),
+>(2, 5, 58),
+>(NULL, 1, 59),
+>(2, 2, 60),
+>(NULL, 3, 61),
+>(2, 2, 62),
+>(2, 3, 63),
+>(2, 6, 64),
+>(-2, 1, 65),
+>(2, 2, 66),
+>(-2, 3, 67),
+>(2, 5, 68),
+>(NULL, 1, 69),
+>(2, 4, 120))
+>as T(a,b,c) SEQUENCE BY c;
A B MMAX_ORG MMAX_CORRECT
------ ------ -------- ------------
2 1 1 1
2 2 2 2
2 3 3 3
2 16 16 16
-2 1 16 16
2 2 2 2
-2 3 16 16
2 5 5 5
? 1 16 16
2 2 2 2
? 3 16 16
2 2 3 3
2 3 3 3
2 26 26 26
-2 1 26 26
2 2 2 2
-2 3 26 26
2 5 5 5
? 1 26 26
2 2 2 2
? 3 26 26
2 2 3 3
2 3 3 3
2 36 36 36
-2 1 36 36
2 2 2 2
-2 3 36 36
2 5 5 5
? 1 36 36
2 2 2 2
? 3 36 36
2 2 3 3
2 3 3 3
2 46 46 46
-2 1 46 46
2 2 2 2
-2 3 46 46
2 5 5 5
? 1 46 46
2 2 2 2
? 3 46 46
2 2 3 3
2 3 3 3
2 56 56 56
-2 1 56 56
2 2 2 2
-2 3 56 56
2 5 5 5
? 1 56 56
2 2 2 2
? 3 56 56
2 2 3 3
2 3 3 3
2 6 6 6
-2 1 56 56
2 2 2 2
-2 3 56 56
2 5 5 5
? 1 56 56
2 2 2 2
? 3 56 56
2 2 3 3
2 3 3 3
2 6 6 6
-2 1 56 56
2 2 2 2
-2 3 56 56
2 5 5 5
? 1 56 56
2 4 4 4
--- 70 row(s) selected.
>>
>>log;
>>
>>
>>?section olapcrtab
>>
>>create table olap_emp
+> (
+> Num integer unsigned no default not null not droppable ,
+> LastName char(24) no default not null not droppable ,
+> FirstName char(14) no default not null not droppable ,
+> MiddleInitial char no default not null not droppable ,
+> Sex char no default not null not droppable ,
+> WorkGroupNum integer unsigned no default not null not droppable ,
+> PayGrade integer unsigned no default not null not droppable ,
+> AnnualSalary integer unsigned no default not null not droppable ,
+> DateOfBirth date no default not null not droppable ,
+> primary key ( Num ) not droppable
+> )
+> store by primary key ;
--- SQL operation complete.
>>
>>-- ----------------------------------------------------
>>
>>
>>
>>insert into olap_emp values
+>(1,'Anderson','Alvin','A','M',1,1,21,DATE '12/07/1941'),
+>(2,'Anderson','Anna ','A','F',1,2,22,DATE '12/07/1941'),
+>(3,'Anderson','Aloysius','A','M',1,3,23,DATE '12/07/1941'),
+>(4, 'Anderson','Abby ','A','F',1,4,24 ,DATE '12/07/1941'),
+>(5,'Anderson','Alan ','A','M',1,5,25, DATE '12/07/1941'),
+>(6,'Anderson','Anne ','A','F',1,6,26,DATE '12/07/1941'),
+>(7,'Anderson','Abelard','A','M',1,7,27,DATE '12/07/1941'),
+>(8,'Anderson','Alice','A','F',1,8,28,DATE '12/07/1941'),
+>(9,'Anderson','Anton','A','M',1,9,29,DATE '12/07/1941'),
+>(10,'Anderson','Alicia ','A','F',1,10,30,DATE '12/07/1941'),
+>(11,'Anderson','Abe','A','M',2,1,21,DATE '12/07/1941'),
+>(12,'Anderson','Amanda ','A','F',2,2,22,DATE '12/07/1941'),
+>(13,'Anderson','Andrew ','A','M',2,3,23,DATE '12/07/1941'),
+>(14,'Anderson','Annette','A','F',2,4,24,DATE '12/07/1941'),
+>(15,'Anderson','Akihiro','A','M',2,5,25,DATE '12/07/1941'),
+>(16,'Anderson','Andrea ','A','F',2,6,26,DATE '12/07/1941'),
+>(17,'Anderson','Arthur ','A','M',2,7,27,DATE '12/07/1941'),
+>(18,'Anderson','Amy ','A','F',2,8,28,DATE '12/07/1941'),
+>(19,'Anderson','Arnold ','A','M',2,9,29,DATE '12/07/1941'),
+>(20,'Anderson','Adriana','A','F',2,10,30,DATE '12/07/1941'),
+>(21,'Anderson','Arturo ','A','M',3,1,21,DATE '12/07/1941'),
+>(22,'Anderson','Annabelle','A','F',3,2,22,DATE '12/07/1941'),
+>(23,'Anderson','Andreas','A','M',3,3,23,DATE '12/07/1941'),
+>(24,'Anderson','Becky','A','F',3,4,24,DATE '12/07/1941'),
+>(25,'Anderson','Bill ','A','M',3,5,25,DATE '12/07/1941'),
+>(26,'Anderson','Betsy','A','F',3,6,26,DATE '12/07/1941'),
+>(27,'Anderson','Bob ','A','M',3,7,27,DATE '12/07/1941'),
+>(28,'Anderson','Belinda','A','F',3,8,28,DATE '12/07/1941'),
+>(29,'Anderson','Bruce','A','M',3,9,29,DATE '12/07/1941'),
+>(30,'Anderson','Bridget','A','F',3,10,30,DATE '12/07/1941');
--- 30 row(s) inserted.
>>
>>
>>
>>
>>?section olaptest
>>--***********************************************************************************
>>--**************************************TESTING**************************************
>>--***********************************************************************************
>>--***********************************************************************************
>>
>>--set schema olapcat.olapsch;
>>
>>--CONTROL QUERY DEFAULT DEF_MAX_HISTORY_ROWS '1024';
>>
>>--**************************************ALL OLAP FUNCTIONS/ROWS UNBOUNDED PRECEDING *****************************
>>SELECT
+> num, workgroupnum ,
+> RANK() OVER ( PARTITION BY workgroupnum ORDER BY num ) ,
+> DENSE_RANK( ) OVER ( PARTITION BY workgroupnum ORDER BY num ),
+> ROW_NUMBER() OVER ( PARTITION BY workgroupnum ORDER BY num )
+>FROM olap_emp ;
NUM WORKGROUPNUM (EXPR) (EXPR) (EXPR)
---------- ------------ -------------------- -------------------- --------------------
1 1 1 1 1
2 1 2 2 2
3 1 3 3 3
4 1 4 4 4
5 1 5 5 5
6 1 6 6 6
7 1 7 7 7
8 1 8 8 8
9 1 9 9 9
10 1 10 10 10
11 2 1 1 1
12 2 2 2 2
13 2 3 3 3
14 2 4 4 4
15 2 5 5 5
16 2 6 6 6
17 2 7 7 7
18 2 8 8 8
19 2 9 9 9
20 2 10 10 10
21 3 1 1 1
22 3 2 2 2
23 3 3 3 3
24 3 4 4 4
25 3 5 5 5
26 3 6 6 6
27 3 7 7 7
28 3 8 8 8
29 3 9 9 9
30 3 10 10 10
--- 30 row(s) selected.
>>
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> MIN(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> MAX(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> VARIANCE(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> STDDEV(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> COUNT(*) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
---------- ------------ ------------ -------------------- -------------------- ---------- ---------- ------------------------- ------------------------- --------------------
1 1 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
2 1 22 43 21 21 22 5.00000000000000000E-001 7.07106781186547584E-001 2
3 1 23 66 22 21 23 1.00000000000000000E+000 1.00000000000000000E+000 3
4 1 24 90 22 21 24 1.66666666666666688E+000 1.29099444873580560E+000 4
5 1 25 115 23 21 25 2.50000000000000000E+000 1.58113883008418976E+000 5
6 1 26 141 23 21 26 3.50000000000000000E+000 1.87082869338697056E+000 6
7 1 27 168 24 21 27 4.66666666666666688E+000 2.16024689946928704E+000 7
8 1 28 196 24 21 28 6.00000000000000000E+000 2.44948974278317792E+000 8
9 1 29 225 25 21 29 7.50000000000000000E+000 2.73861278752583072E+000 9
10 1 30 255 25 21 30 9.16666666666666624E+000 3.02765035409749184E+000 10
11 2 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
12 2 22 43 21 21 22 5.00000000000000000E-001 7.07106781186547584E-001 2
13 2 23 66 22 21 23 1.00000000000000000E+000 1.00000000000000000E+000 3
14 2 24 90 22 21 24 1.66666666666666688E+000 1.29099444873580560E+000 4
15 2 25 115 23 21 25 2.50000000000000000E+000 1.58113883008418976E+000 5
16 2 26 141 23 21 26 3.50000000000000000E+000 1.87082869338697056E+000 6
17 2 27 168 24 21 27 4.66666666666666688E+000 2.16024689946928704E+000 7
18 2 28 196 24 21 28 6.00000000000000000E+000 2.44948974278317792E+000 8
19 2 29 225 25 21 29 7.50000000000000000E+000 2.73861278752583072E+000 9
20 2 30 255 25 21 30 9.16666666666666624E+000 3.02765035409749184E+000 10
21 3 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
22 3 22 43 21 21 22 5.00000000000000000E-001 7.07106781186547584E-001 2
23 3 23 66 22 21 23 1.00000000000000000E+000 1.00000000000000000E+000 3
24 3 24 90 22 21 24 1.66666666666666688E+000 1.29099444873580560E+000 4
25 3 25 115 23 21 25 2.50000000000000000E+000 1.58113883008418976E+000 5
26 3 26 141 23 21 26 3.50000000000000000E+000 1.87082869338697056E+000 6
27 3 27 168 24 21 27 4.66666666666666688E+000 2.16024689946928704E+000 7
28 3 28 196 24 21 28 6.00000000000000000E+000 2.44948974278317792E+000 8
29 3 29 225 25 21 29 7.50000000000000000E+000 2.73861278752583072E+000 9
30 3 30 255 25 21 30 9.16666666666666624E+000 3.02765035409749184E+000 10
--- 30 row(s) selected.
>>
>>
>>--**************************************ALL OLAP FUNCTIONS/ROWS N PRECEDING *****************************
>>
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING ) ,
+> MIN(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING ) ,
+> MAX(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING ) ,
+> VARIANCE(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING ) ,
+> STDDEV(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING ) ,
+> COUNT(*) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 5 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
---------- ------------ ------------ -------------------- -------------------- ---------- ---------- ------------------------- ------------------------- --------------------
1 1 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
2 1 22 43 21 21 22 5.00000000000000000E-001 7.07106781186547584E-001 2
3 1 23 66 22 21 23 1.00000000000000000E+000 1.00000000000000000E+000 3
4 1 24 90 22 21 24 1.66666666666666688E+000 1.29099444873580560E+000 4
5 1 25 115 23 21 25 2.50000000000000000E+000 1.58113883008418976E+000 5
6 1 26 141 23 21 26 3.50000000000000000E+000 1.87082869338697056E+000 6
7 1 27 147 24 22 27 3.50000000000000000E+000 1.87082869338697056E+000 6
8 1 28 153 25 23 28 3.50000000000000000E+000 1.87082869338697056E+000 6
9 1 29 159 26 24 29 3.50000000000000000E+000 1.87082869338697056E+000 6
10 1 30 165 27 25 30 3.50000000000000000E+000 1.87082869338697056E+000 6
11 2 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
12 2 22 43 21 21 22 5.00000000000000000E-001 7.07106781186547584E-001 2
13 2 23 66 22 21 23 1.00000000000000000E+000 1.00000000000000000E+000 3
14 2 24 90 22 21 24 1.66666666666666688E+000 1.29099444873580560E+000 4
15 2 25 115 23 21 25 2.50000000000000000E+000 1.58113883008418976E+000 5
16 2 26 141 23 21 26 3.50000000000000000E+000 1.87082869338697056E+000 6
17 2 27 147 24 22 27 3.50000000000000000E+000 1.87082869338697056E+000 6
18 2 28 153 25 23 28 3.50000000000000000E+000 1.87082869338697056E+000 6
19 2 29 159 26 24 29 3.50000000000000000E+000 1.87082869338697056E+000 6
20 2 30 165 27 25 30 3.50000000000000000E+000 1.87082869338697056E+000 6
21 3 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
22 3 22 43 21 21 22 5.00000000000000000E-001 7.07106781186547584E-001 2
23 3 23 66 22 21 23 1.00000000000000000E+000 1.00000000000000000E+000 3
24 3 24 90 22 21 24 1.66666666666666688E+000 1.29099444873580560E+000 4
25 3 25 115 23 21 25 2.50000000000000000E+000 1.58113883008418976E+000 5
26 3 26 141 23 21 26 3.50000000000000000E+000 1.87082869338697056E+000 6
27 3 27 147 24 22 27 3.50000000000000000E+000 1.87082869338697056E+000 6
28 3 28 153 25 23 28 3.50000000000000000E+000 1.87082869338697056E+000 6
29 3 29 159 26 24 29 3.50000000000000000E+000 1.87082869338697056E+000 6
30 3 30 165 27 25 30 3.50000000000000000E+000 1.87082869338697056E+000 6
--- 30 row(s) selected.
>>
>>--**************************************ALL OLAP FUNCTIONS/ROWS CURRENT ROW *****************************
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW ) ,
+> MIN(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW ) ,
+> MAX(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW ) ,
+> VARIANCE(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW ) ,
+> STDDEV(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW ) ,
+> COUNT(*) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
---------- ------------ ------------ -------------------- -------------------- ---------- ---------- ------------------------- ------------------------- --------------------
1 1 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
2 1 22 22 22 22 22 0.00000000000000000E+000 0.00000000000000000E+000 1
3 1 23 23 23 23 23 0.00000000000000000E+000 0.00000000000000000E+000 1
4 1 24 24 24 24 24 0.00000000000000000E+000 0.00000000000000000E+000 1
5 1 25 25 25 25 25 0.00000000000000000E+000 0.00000000000000000E+000 1
6 1 26 26 26 26 26 0.00000000000000000E+000 0.00000000000000000E+000 1
7 1 27 27 27 27 27 0.00000000000000000E+000 0.00000000000000000E+000 1
8 1 28 28 28 28 28 0.00000000000000000E+000 0.00000000000000000E+000 1
9 1 29 29 29 29 29 0.00000000000000000E+000 0.00000000000000000E+000 1
10 1 30 30 30 30 30 0.00000000000000000E+000 0.00000000000000000E+000 1
11 2 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
12 2 22 22 22 22 22 0.00000000000000000E+000 0.00000000000000000E+000 1
13 2 23 23 23 23 23 0.00000000000000000E+000 0.00000000000000000E+000 1
14 2 24 24 24 24 24 0.00000000000000000E+000 0.00000000000000000E+000 1
15 2 25 25 25 25 25 0.00000000000000000E+000 0.00000000000000000E+000 1
16 2 26 26 26 26 26 0.00000000000000000E+000 0.00000000000000000E+000 1
17 2 27 27 27 27 27 0.00000000000000000E+000 0.00000000000000000E+000 1
18 2 28 28 28 28 28 0.00000000000000000E+000 0.00000000000000000E+000 1
19 2 29 29 29 29 29 0.00000000000000000E+000 0.00000000000000000E+000 1
20 2 30 30 30 30 30 0.00000000000000000E+000 0.00000000000000000E+000 1
21 3 21 21 21 21 21 0.00000000000000000E+000 0.00000000000000000E+000 1
22 3 22 22 22 22 22 0.00000000000000000E+000 0.00000000000000000E+000 1
23 3 23 23 23 23 23 0.00000000000000000E+000 0.00000000000000000E+000 1
24 3 24 24 24 24 24 0.00000000000000000E+000 0.00000000000000000E+000 1
25 3 25 25 25 25 25 0.00000000000000000E+000 0.00000000000000000E+000 1
26 3 26 26 26 26 26 0.00000000000000000E+000 0.00000000000000000E+000 1
27 3 27 27 27 27 27 0.00000000000000000E+000 0.00000000000000000E+000 1
28 3 28 28 28 28 28 0.00000000000000000E+000 0.00000000000000000E+000 1
29 3 29 29 29 29 29 0.00000000000000000E+000 0.00000000000000000E+000 1
30 3 30 30 30 30 30 0.00000000000000000E+000 0.00000000000000000E+000 1
--- 30 row(s) selected.
>>
>>
>>
>>
>>
>>--**************************************Support for ASC / DESC****************************
>>
>>---PARTITION BY/ORDER BY
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>---PARTITION BY/ORDER BY ASC
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ASC ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>---PARTITION BY/ORDER BY DESC
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num desc ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
10 1 30 30
9 1 29 59
8 1 28 87
7 1 27 114
6 1 26 140
5 1 25 165
4 1 24 189
3 1 23 212
2 1 22 234
1 1 21 255
20 2 30 30
19 2 29 59
18 2 28 87
17 2 27 114
16 2 26 140
15 2 25 165
14 2 24 189
13 2 23 212
12 2 22 234
11 2 21 255
30 3 30 30
29 3 29 59
28 3 28 87
27 3 27 114
26 3 26 140
25 3 25 165
24 3 24 189
23 3 23 212
22 3 22 234
21 3 21 255
--- 30 row(s) selected.
>>
>>--*****************************olap with group by*************************************************
>>
>>SELECT paygrade,workgroupnum,
+> SUM(annualsalary) ,
+> SUM( paygrade) OVER (ORDER BY workgroupnum, num rows UNBOUNDED PRECEDING)
+>from olap_emp
+>group by paygrade,workgroupnum, num
+>order by 1,2,3,4;
PAYGRADE WORKGROUPNUM (EXPR) (EXPR)
---------- ------------ -------------------- --------------------
1 1 21 1
1 2 21 56
1 3 21 111
2 1 22 3
2 2 22 58
2 3 22 113
3 1 23 6
3 2 23 61
3 3 23 116
4 1 24 10
4 2 24 65
4 3 24 120
5 1 25 15
5 2 25 70
5 3 25 125
6 1 26 21
6 2 26 76
6 3 26 131
7 1 27 28
7 2 27 83
7 3 27 138
8 1 28 36
8 2 28 91
8 3 28 146
9 1 29 45
9 2 29 100
9 3 29 155
10 1 30 55
10 2 30 110
10 3 30 165
--- 30 row(s) selected.
>>
>>
>>SELECT paygrade,workgroupnum,
+> SUM(annualsalary) ,
+> SUM( paygrade) OVER (PARTITION BY workgroupnum ORDER BY SUM(annualsalary) ROWS UNBOUNDED PRECEDING)
+>from olap_emp
+>group by paygrade,workgroupnum;
PAYGRADE WORKGROUPNUM (EXPR) (EXPR)
---------- ------------ -------------------- --------------------
1 1 21 1
2 1 22 3
3 1 23 6
4 1 24 10
5 1 25 15
6 1 26 21
7 1 27 28
8 1 28 36
9 1 29 45
10 1 30 55
1 2 21 1
2 2 22 3
3 2 23 6
4 2 24 10
5 2 25 15
6 2 26 21
7 2 27 28
8 2 28 36
9 2 29 45
10 2 30 55
1 3 21 1
2 3 22 3
3 3 23 6
4 3 24 10
5 3 25 15
6 3 26 21
7 3 27 28
8 3 28 36
9 3 29 45
10 3 30 55
--- 30 row(s) selected.
>>SELECT paygrade,workgroupnum,
+> SUM(annualsalary) ,
+> SUM( paygrade) OVER (PARTITION BY workgroupnum ORDER BY sum_sal ROWS UNBOUNDED PRECEDING)
+>from
+> (select paygrade,workgroupnum,annualsalary, sum(annualsalary) sum_sal from olap_emp group by paygrade,workgroupnum, annualsalary) T group by paygrade,workgroupnum,sum_sal;
PAYGRADE WORKGROUPNUM (EXPR) (EXPR)
---------- ------------ -------------------- --------------------
1 1 21 1
2 1 22 3
3 1 23 6
4 1 24 10
5 1 25 15
6 1 26 21
7 1 27 28
8 1 28 36
9 1 29 45
10 1 30 55
1 2 21 1
2 2 22 3
3 2 23 6
4 2 24 10
5 2 25 15
6 2 26 21
7 2 27 28
8 2 28 36
9 2 29 45
10 2 30 55
1 3 21 1
2 3 22 3
3 3 23 6
4 3 24 10
5 3 25 15
6 3 26 21
7 3 27 28
8 3 28 36
9 3 29 45
10 3 30 55
--- 30 row(s) selected.
>>
>>
>>---supported
>>SELECT paygrade,workgroupnum,
+> SUM(annualsalary) ,
+> AVG( SUM(annualsalary) ) OVER (PARTITION BY paygrade ORDER BY workgroupnum ROWS UNBOUNDED PRECEDING)
+>from olap_emp
+>group by paygrade,workgroupnum
+>order by 1,2,3,4;
PAYGRADE WORKGROUPNUM (EXPR) (EXPR)
---------- ------------ -------------------- --------------------
1 1 21 21
1 2 21 21
1 3 21 21
2 1 22 22
2 2 22 22
2 3 22 22
3 1 23 23
3 2 23 23
3 3 23 23
4 1 24 24
4 2 24 24
4 3 24 24
5 1 25 25
5 2 25 25
5 3 25 25
6 1 26 26
6 2 26 26
6 3 26 26
7 1 27 27
7 2 27 27
7 3 27 27
8 1 28 28
8 2 28 28
8 3 28 28
9 1 29 29
9 2 29 29
9 3 29 29
10 1 30 30
10 2 30 30
10 3 30 30
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,
+> SUM(annualsalary) ,
+> SUM( AVG(annualsalary) ) OVER (PARTITION BY paygrade ORDER BY workgroupnum ROWS UNBOUNDED PRECEDING)
+>from olap_emp
+>group by paygrade,workgroupnum
+>order by 1,2,3,4;
PAYGRADE WORKGROUPNUM (EXPR) (EXPR)
---------- ------------ -------------------- --------------------
1 1 21 21
1 2 21 42
1 3 21 63
2 1 22 22
2 2 22 44
2 3 22 66
3 1 23 23
3 2 23 46
3 3 23 69
4 1 24 24
4 2 24 48
4 3 24 72
5 1 25 25
5 2 25 50
5 3 25 75
6 1 26 26
6 2 26 52
6 3 26 78
7 1 27 27
7 2 27 54
7 3 27 81
8 1 28 28
8 2 28 56
8 3 28 84
9 1 29 29
9 2 29 58
9 3 29 87
10 1 30 30
10 2 30 60
10 3 30 90
--- 30 row(s) selected.
>>
>>
>>--********************************** Multiple (PARTITION BY /ORDER BY) support*****************************
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR)
---------- ------------ ------------ -------------------- --------------------
1 1 21 21 21
2 1 22 43 21
3 1 23 66 22
4 1 24 90 22
5 1 25 115 23
6 1 26 141 23
7 1 27 168 24
8 1 28 196 24
9 1 29 225 25
10 1 30 255 25
11 2 21 21 21
12 2 22 43 21
13 2 23 66 22
14 2 24 90 22
15 2 25 115 23
16 2 26 141 23
17 2 27 168 24
18 2 28 196 24
19 2 29 225 25
20 2 30 255 25
21 3 21 21 21
22 3 22 43 21
23 3 23 66 22
24 3 24 90 22
25 3 25 115 23
26 3 26 141 23
27 3 27 168 24
28 3 28 196 24
29 3 29 225 25
30 3 30 255 25
--- 30 row(s) selected.
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num DESC ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num DESC ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num DESC ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR)
---------- ------------ ------------ -------------------- --------------------
10 1 30 30 30
9 1 29 59 29
8 1 28 87 29
7 1 27 114 28
6 1 26 140 28
5 1 25 165 27
4 1 24 189 27
3 1 23 212 26
2 1 22 234 26
1 1 21 255 25
20 2 30 30 30
19 2 29 59 29
18 2 28 87 29
17 2 27 114 28
16 2 26 140 28
15 2 25 165 27
14 2 24 189 27
13 2 23 212 26
12 2 22 234 26
11 2 21 255 25
30 3 30 30 30
29 3 29 59 29
28 3 28 87 29
27 3 27 114 28
26 3 26 140 28
25 3 25 165 27
24 3 24 189 27
23 3 23 212 26
22 3 22 234 26
21 3 21 255 25
--- 30 row(s) selected.
>>
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS UNBOUNDED PRECEDING ) as olapsum,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS UNBOUNDED PRECEDING ) as olapavg
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR)
---------- ------------ ------------ -------------------- --------------------
1 1 21 21 21
2 1 22 43 21
3 1 23 66 22
4 1 24 90 22
5 1 25 115 23
6 1 26 141 23
7 1 27 168 24
8 1 28 196 24
9 1 29 225 25
10 1 30 255 25
11 2 21 276 25
12 2 22 298 24
13 2 23 321 24
14 2 24 345 24
15 2 25 370 24
16 2 26 396 24
17 2 27 423 24
18 2 28 451 25
19 2 29 480 25
20 2 30 510 25
21 3 21 531 25
22 3 22 553 25
23 3 23 576 25
24 3 24 600 25
25 3 25 625 25
26 3 26 651 25
27 3 27 678 25
28 3 28 706 25
29 3 29 735 25
30 3 30 765 25
--- 30 row(s) selected.
>>
>>-- ORDER BY desc / ORDER BY
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num DESC ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num DESC ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( ORDER BY num DESC ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR)
---------- ------------ ------------ -------------------- --------------------
30 3 30 30 30
29 3 29 59 29
28 3 28 87 29
27 3 27 114 28
26 3 26 140 28
25 3 25 165 27
24 3 24 189 27
23 3 23 212 26
22 3 22 234 26
21 3 21 255 25
20 2 30 285 25
19 2 29 314 26
18 2 28 342 26
17 2 27 369 26
16 2 26 395 26
15 2 25 420 26
14 2 24 444 26
13 2 23 467 25
12 2 22 489 25
11 2 21 510 25
10 1 30 540 25
9 1 29 569 25
8 1 28 597 25
7 1 27 624 26
6 1 26 650 26
5 1 25 675 25
4 1 24 699 25
3 1 23 722 25
2 1 22 744 25
1 1 21 765 25
--- 30 row(s) selected.
>>
>>---<> ORDER BY's
>>
>>SELECT num, workgroupnum, paygrade, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum,
+> paygrade ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>---
>>SELECT num, workgroupnum, paygrade, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>---
>>SELECT num, workgroupnum, paygrade, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>---
>>SELECT num, workgroupnum, paygrade, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>---**************************************Olap distinct support****************************
>>SELECT num, workgroupnum, annualsalary,
+> SUM(distinct AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4341] For Window Functions, the set qualifier must be ALL. DISTINCT is not supported for Window Functions.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(all AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>
>>---************************************** PARAMs************************************************
>>set PARAM ?empnum 20;
>>
>>SELECT ?empnum,num, workgroupnum, annualsalary,
+> SUM(all AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp
+>where num < ?empnum;
(EXPR) NUM WORKGROUPNUM ANNUALSALARY (EXPR)
--------------------- ---------- ------------ ------------ --------------------
20.000000 1 1 21 21
20.000000 2 1 22 43
20.000000 3 1 23 66
20.000000 4 1 24 90
20.000000 5 1 25 115
20.000000 6 1 26 141
20.000000 7 1 27 168
20.000000 8 1 28 196
20.000000 9 1 29 225
20.000000 10 1 30 255
20.000000 11 2 21 21
20.000000 12 2 22 43
20.000000 13 2 23 66
20.000000 14 2 24 90
20.000000 15 2 25 115
20.000000 16 2 26 141
20.000000 17 2 27 168
20.000000 18 2 28 196
20.000000 19 2 29 225
--- 19 row(s) selected.
>>
>>prepare s1 from SELECT ?, workgroupnum, annualsalary --,
+> ---SUM(all AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
--- SQL command prepared.
>>
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(all AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp
+>where num < 20
+>order by 1, 2, 3, 4;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
--- 19 row(s) selected.
>>
>>
>>--************************************olap and Sequence functions in SELECT list *****************************
>>-- no SEQUENCE BY
>>
>>SELECT RUNNINGCOUNT(AnnualSalary )
+>FROM olap_emp ;
*** ERROR[4110] The query contains sequence functions but no SEQUENCE BY clause: RUNNINGCOUNT(TRAFODION.SCH.OLAP_EMP.ANNUALSALARY).
*** ERROR[8822] The statement was not prepared.
>>
>>
>>SELECT RUNNINGSUM(AnnualSalary )
+>FROM olap_emp
+>SEQUENCE BY num;
(EXPR)
--------------------
21
43
66
90
115
141
168
196
225
255
276
298
321
345
370
396
423
451
480
510
531
553
576
600
625
651
678
706
735
765
--- 30 row(s) selected.
>>
>>--- olap and sequence functions together
>>SELECT num, SUM(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING ),
+>RUNNINGSUM(AnnualSalary )
+>FROM olap_emp ;
*** ERROR[4345] Window and Sequence functions cannot be used together.
*** ERROR[8822] The statement was not prepared.
>>
>>---olap and sequence functions together
>>SELECT num, RUNNINGSUM(AnnualSalary ), SUM(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4345] Window and Sequence functions cannot be used together.
*** ERROR[8822] The statement was not prepared.
>>
>>--- olap and sequence functions together
>>SELECT num, runningSUM(AnnualSalary ), SUM(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp
+>sequence by num;
*** ERROR[4345] Window and Sequence functions cannot be used together.
*** ERROR[8822] The statement was not prepared.
>>
>>--- olap and sequence functions together
>>SELECT num, SUM(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING ), runningSUM(AnnualSalary )
+>FROM olap_emp
+>sequence by num;
*** ERROR[4345] Window and Sequence functions cannot be used together.
*** ERROR[8822] The statement was not prepared.
>>
>>--************************************olap / qequence in where clause*****************************
>>
>>SELECT num, SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp
+>where SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) > 100;
*** ERROR[4346] Window functions can be placed only in the select list.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT num, runningSUM(AnnualSalary )
+>FROM olap_emp
+>where runningSUM(paygrade ) > 100
+>sequence by num;
*** ERROR[4109] Sequence functions are placed incorrectly: RUNNINGSUM(TRAFODION.SCH.OLAP_EMP.PAYGRADE).
*** ERROR[8822] The statement was not prepared.
>>
>>
>>---************************************** WINDOW FRAME SUPPORT************************************************
>>---PARTITIN BY & ORDER BY
>>----------ROWS UNBOUNDED PRECEDING ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>----------ROWS <n> PRECEDING ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 6 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 175
9 1 29 182
10 1 30 189
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 175
19 2 29 182
20 2 30 189
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 175
29 3 29 182
30 3 30 189
--- 30 row(s) selected.
>>
>>----------ROWS CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 22
3 1 23 23
4 1 24 24
5 1 25 25
6 1 26 26
7 1 27 27
8 1 28 28
9 1 29 29
10 1 30 30
11 2 21 21
12 2 22 22
13 2 23 23
14 2 24 24
15 2 25 25
16 2 26 26
17 2 27 27
18 2 28 28
19 2 29 29
20 2 30 30
21 3 21 21
22 3 22 22
23 3 23 23
24 3 24 24
25 3 25 25
26 3 26 26
27 3 27 27
28 3 28 28
29 3 29 29
30 3 30 30
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> PRECEDING ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND 6 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 ?
5 1 25 ?
6 1 26 ?
7 1 27 21
8 1 28 43
9 1 29 66
10 1 30 90
11 2 21 ?
12 2 22 ?
13 2 23 ?
14 2 24 ?
15 2 25 ?
16 2 26 ?
17 2 27 21
18 2 28 43
19 2 29 66
20 2 30 90
21 3 21 ?
22 3 22 ?
23 3 23 ?
24 3 24 ?
25 3 25 ?
26 3 26 ?
27 3 27 21
28 3 28 43
29 3 29 66
30 3 30 90
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>> --SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>> -- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> >= <m>) ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 PRECEDING AND 3 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 21
5 1 25 43
6 1 26 66
7 1 27 90
8 1 28 94
9 1 29 98
10 1 30 102
11 2 21 ?
12 2 22 ?
13 2 23 ?
14 2 24 21
15 2 25 43
16 2 26 66
17 2 27 90
18 2 28 94
19 2 29 98
20 2 30 102
21 3 21 ?
22 3 22 ?
23 3 23 ?
24 3 24 21
25 3 25 43
26 3 26 66
27 3 27 90
28 3 28 94
29 3 29 98
30 3 30 102
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> < <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 3 PRECEDING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 175
9 1 29 182
10 1 30 189
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 175
19 2 29 182
20 2 30 189
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 175
29 3 29 182
30 3 30 189
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 3 PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN CURRENT ROW AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN CURRENT ROW AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 22
3 1 23 23
4 1 24 24
5 1 25 25
6 1 26 26
7 1 27 27
8 1 28 28
9 1 29 29
10 1 30 30
11 2 21 21
12 2 22 22
13 2 23 23
14 2 24 24
15 2 25 25
16 2 26 26
17 2 27 27
18 2 28 28
19 2 29 29
20 2 30 30
21 3 21 21
22 3 22 22
23 3 23 23
24 3 24 24
25 3 25 25
26 3 26 26
27 3 27 27
28 3 28 28
29 3 29 29
30 3 30 30
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 3 FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> <= <m>) ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 3 FOLLOWING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> > <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 6 FOLLOWING AND 3 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>
>>
>>---PARTITIN BY & NO ORDER BY
>>----------ROWS UNBOUNDED PRECEDING ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS UNBOUNDED PRECEDING ) as olapsum
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS <n> PRECEDING ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS 6 PRECEDING ) as olapsum
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS CURRENT ROW ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS CURRENT ROW ) AS olapsum
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> PRECEDING ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED PRECEDING AND 6 PRECEDING ) AS olapsum
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS olapsum
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> >= <m>) ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 PRECEDING AND 3 PRECEDING ) AS olapsum
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> < <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 3 PRECEDING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND CURRENT ROW ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 3 PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN CURRENT ROW AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND CURRENT ROW ===> Supported
>>SELECT workgroupnum, count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN CURRENT ROW AND CURRENT ROW )
+>FROM olap_emp ) T
+>GROUP BY workgroupnum;
WORKGROUPNUM (EXPR)
------------ --------------------
1 10
2 10
3 10
--- 3 row(s) selected.
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 3 FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> <= <m>) ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 3 FOLLOWING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> > <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ROWS BETWEEN 6 FOLLOWING AND 3 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>---NO PARTITIN BY & ORDER BY
>>----------ROWS UNBOUNDED PRECEDING ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 276
12 2 22 298
13 2 23 321
14 2 24 345
15 2 25 370
16 2 26 396
17 2 27 423
18 2 28 451
19 2 29 480
20 2 30 510
21 3 21 531
22 3 22 553
23 3 23 576
24 3 24 600
25 3 25 625
26 3 26 651
27 3 27 678
28 3 28 706
29 3 29 735
30 3 30 765
--- 30 row(s) selected.
>>
>>----------ROWS <n> PRECEDING ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS 6 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 175
9 1 29 182
10 1 30 189
11 2 21 186
12 2 22 183
13 2 23 180
14 2 24 177
15 2 25 174
16 2 26 171
17 2 27 168
18 2 28 175
19 2 29 182
20 2 30 189
21 3 21 186
22 3 22 183
23 3 23 180
24 3 24 177
25 3 25 174
26 3 26 171
27 3 27 168
28 3 28 175
29 3 29 182
30 3 30 189
--- 30 row(s) selected.
>>
>>----------ROWS CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 22
3 1 23 23
4 1 24 24
5 1 25 25
6 1 26 26
7 1 27 27
8 1 28 28
9 1 29 29
10 1 30 30
11 2 21 21
12 2 22 22
13 2 23 23
14 2 24 24
15 2 25 25
16 2 26 26
17 2 27 27
18 2 28 28
19 2 29 29
20 2 30 30
21 3 21 21
22 3 22 22
23 3 23 23
24 3 24 24
25 3 25 25
26 3 26 26
27 3 27 27
28 3 28 28
29 3 29 29
30 3 30 30
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> PRECEDING ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND 6 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 ?
5 1 25 ?
6 1 26 ?
7 1 27 21
8 1 28 43
9 1 29 66
10 1 30 90
11 2 21 115
12 2 22 141
13 2 23 168
14 2 24 196
15 2 25 225
16 2 26 255
17 2 27 276
18 2 28 298
19 2 29 321
20 2 30 345
21 3 21 370
22 3 22 396
23 3 23 423
24 3 24 451
25 3 25 480
26 3 26 510
27 3 27 531
28 3 28 553
29 3 29 576
30 3 30 600
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 276
12 2 22 298
13 2 23 321
14 2 24 345
15 2 25 370
16 2 26 396
17 2 27 423
18 2 28 451
19 2 29 480
20 2 30 510
21 3 21 531
22 3 22 553
23 3 23 576
24 3 24 600
25 3 25 625
26 3 26 651
27 3 27 678
28 3 28 706
29 3 29 735
30 3 30 765
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> >= <m>) ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 PRECEDING AND 3 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 21
5 1 25 43
6 1 26 66
7 1 27 90
8 1 28 94
9 1 29 98
10 1 30 102
11 2 21 106
12 2 22 110
13 2 23 114
14 2 24 108
15 2 25 102
16 2 26 96
17 2 27 90
18 2 28 94
19 2 29 98
20 2 30 102
21 3 21 106
22 3 22 110
23 3 23 114
24 3 24 108
25 3 25 102
26 3 26 96
27 3 27 90
28 3 28 94
29 3 29 98
30 3 30 102
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> < <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 3 PRECEDING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 175
9 1 29 182
10 1 30 189
11 2 21 186
12 2 22 183
13 2 23 180
14 2 24 177
15 2 25 174
16 2 26 171
17 2 27 168
18 2 28 175
19 2 29 182
20 2 30 189
21 3 21 186
22 3 22 183
23 3 23 180
24 3 24 177
25 3 25 174
26 3 26 171
27 3 27 168
28 3 28 175
29 3 29 182
30 3 30 189
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 3 PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN CURRENT ROW AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND CURRENT ROW ===> Supported
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN CURRENT ROW AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 22
3 1 23 23
4 1 24 24
5 1 25 25
6 1 26 26
7 1 27 27
8 1 28 28
9 1 29 29
10 1 30 30
11 2 21 21
12 2 22 22
13 2 23 23
14 2 24 24
15 2 25 25
16 2 26 26
17 2 27 27
18 2 28 28
19 2 29 29
20 2 30 30
21 3 21 21
22 3 22 22
23 3 23 23
24 3 24 24
25 3 25 25
26 3 26 26
27 3 27 27
28 3 28 28
29 3 29 29
30 3 30 30
--- 30 row(s) selected.
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 3 FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> <= <m>) ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 3 FOLLOWING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> > <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ORDER BY num ROWS BETWEEN 6 FOLLOWING AND 3 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>--=============
>>
>>---NO PARTITIN BY & NO ORDER BY
>>----------ROWS UNBOUNDED PRECEDING ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS UNBOUNDED PRECEDING ) as olapsum
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS <n> PRECEDING ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS 6 PRECEDING ) as olapsum
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS CURRENT ROW ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS CURRENT ROW ) AS olapsum
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> PRECEDING ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND 6 PRECEDING ) AS olapsum
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS olapsum
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED PRECEDING AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 PRECEDING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> >= <m>) ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 PRECEDING AND 3 PRECEDING ) AS olapsum
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> PRECEDING (<n> < <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 3 PRECEDING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND CURRENT ROW ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS BETWEEN <n> PRECEDING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> PRECEDING AND <m> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN 3 PRECEDING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN CURRENT ROW AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN CURRENT ROW AND CURRENT ROW ===> Supported
>>SELECT count(*)
+>FROM (
+>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN CURRENT ROW AND CURRENT ROW )
+>FROM olap_emp ) T;
(EXPR)
--------------------
30
--- 1 row(s) selected.
>>
>>----------ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN CURRENT ROW AND <n> FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>----------ROWS BETWEEN UNBOUNDED FOLLOWING AND <n> FOLLOWING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN UNBOUNDED FOLLOWING AND 6 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> PRECEDING ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 3 FOLLOWING AND 6 PRECEDING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND CURRENT ROW ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 FOLLOWING AND CURRENT ROW )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND UNBOUNDED FOLLOWING ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> <= <m>) ===> Unsupported
>>--SELECT num, workgroupnum, annualsalary,
>>-- SUM(AnnualSalary ) OVER ( ROWS BETWEEN 3 FOLLOWING AND 6 FOLLOWING )
>>--FROM olap_emp ;
>>
>>----------ROWS BETWEEN <n> FOLLOWING AND <m> FOLLOWING (<n> > <m>) ===> Illegal
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( ROWS BETWEEN 6 FOLLOWING AND 3 FOLLOWING )
+>FROM olap_emp ;
*** ERROR[4342] The specified window frame clause is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>---************************************** History buffer************************************************
>>
>>
>>
>>--CONTROL QUERY DEFAULT DEF_MAX_HISTORY_ROWS '10';
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS 9 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>
>>
>>
>>--CONTROL QUERY DEFAULT DEF_MAX_HISTORY_ROWS '10';
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND 9 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 ?
5 1 25 ?
6 1 26 ?
7 1 27 ?
8 1 28 ?
9 1 29 ?
10 1 30 21
11 2 21 ?
12 2 22 ?
13 2 23 ?
14 2 24 ?
15 2 25 ?
16 2 26 ?
17 2 27 ?
18 2 28 ?
19 2 29 ?
20 2 30 21
21 3 21 ?
22 3 22 ?
23 3 23 ?
24 3 24 ?
25 3 25 ?
26 3 26 ?
27 3 27 ?
28 3 28 ?
29 3 29 ?
30 3 30 21
--- 30 row(s) selected.
>>
>>
>>--CONTROL QUERY DEFAULT DEF_MAX_HISTORY_ROWS '7';
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 9 PRECEDING AND 8 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 ?
5 1 25 ?
6 1 26 ?
7 1 27 ?
8 1 28 ?
9 1 29 21
10 1 30 43
11 2 21 ?
12 2 22 ?
13 2 23 ?
14 2 24 ?
15 2 25 ?
16 2 26 ?
17 2 27 ?
18 2 28 ?
19 2 29 21
20 2 30 43
21 3 21 ?
22 3 22 ?
23 3 23 ?
24 3 24 ?
25 3 25 ?
26 3 26 ?
27 3 27 ?
28 3 28 ?
29 3 29 21
30 3 30 43
--- 30 row(s) selected.
>>
>>--CONTROL QUERY DEFAULT DEF_MAX_HISTORY_ROWS '7';
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN 9 PRECEDING AND CURRENT ROW )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 21
2 1 22 43
3 1 23 66
4 1 24 90
5 1 25 115
6 1 26 141
7 1 27 168
8 1 28 196
9 1 29 225
10 1 30 255
11 2 21 21
12 2 22 43
13 2 23 66
14 2 24 90
15 2 25 115
16 2 26 141
17 2 27 168
18 2 28 196
19 2 29 225
20 2 30 255
21 3 21 21
22 3 22 43
23 3 23 66
24 3 24 90
25 3 25 115
26 3 26 141
27 3 27 168
28 3 28 196
29 3 29 225
30 3 30 255
--- 30 row(s) selected.
>>
>>---************************************** Window Size > smallint************************************************
>>
>>--CONTROL QUERY DEFAULT DEF_MAX_HISTORY_ROWS '1024';
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND 65535 PRECEDING )
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR)
---------- ------------ ------------ --------------------
1 1 21 ?
2 1 22 ?
3 1 23 ?
4 1 24 ?
5 1 25 ?
6 1 26 ?
7 1 27 ?
8 1 28 ?
9 1 29 ?
10 1 30 ?
11 2 21 ?
12 2 22 ?
13 2 23 ?
14 2 24 ?
15 2 25 ?
16 2 26 ?
17 2 27 ?
18 2 28 ?
19 2 29 ?
20 2 30 ?
21 3 21 ?
22 3 22 ?
23 3 23 ?
24 3 24 ?
25 3 25 ?
26 3 26 ?
27 3 27 ?
28 3 28 ?
29 3 29 ?
30 3 30 ?
--- 30 row(s) selected.
>>
>>
>>
>>--**************************************olap functions in diffrent scopes*****************************
>>SELECT *
+>FROM ( SELECT num, SUM(AnnualSalary) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) sumA FROM olap_emp) A,
+> ( SELECT num, AVG(AnnualSalary) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) avgB FROM olap_emp) B
+>WHERE A.num=b.num
+>order by 1,2,3,4;
NUM SUMA NUM AVGB
---------- -------------------- ---------- --------------------
1 21 1 21
2 43 2 21
3 66 3 22
4 90 4 22
5 115 5 23
6 141 6 23
7 168 7 24
8 196 8 24
9 225 9 25
10 255 10 25
11 21 11 21
12 43 12 21
13 66 13 22
14 90 14 22
15 115 15 23
16 141 16 23
17 168 17 24
18 196 18 24
19 225 19 25
20 255 20 25
21 21 21 21
22 43 22 21
23 66 23 22
24 90 24 22
25 115 25 23
26 141 26 23
27 168 27 24
28 196 28 24
29 225 29 25
30 255 30 25
--- 30 row(s) selected.
>>
>>
>>SELECT *
+>FROM ( SELECT num, SUM(AnnualSalary) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) sumA FROM olap_emp) A,
+> ( SELECT num, AVG(AnnualSalary) OVER ( PARTITION BY workgroupnum ORDER BY paygrade,num ROWS UNBOUNDED PRECEDING ) avgB FROM olap_emp) B
+>WHERE A.num=b.num
+>order by 1,2,3,4;
NUM SUMA NUM AVGB
---------- -------------------- ---------- --------------------
1 21 1 21
2 43 2 21
3 66 3 22
4 90 4 22
5 115 5 23
6 141 6 23
7 168 7 24
8 196 8 24
9 225 9 25
10 255 10 25
11 21 11 21
12 43 12 21
13 66 13 22
14 90 14 22
15 115 15 23
16 141 16 23
17 168 17 24
18 196 18 24
19 225 19 25
20 255 20 25
21 21 21 21
22 43 22 21
23 66 23 22
24 90 24 22
25 115 25 23
26 141 26 23
27 168 27 24
28 196 28 24
29 225 29 25
30 255 30 25
--- 30 row(s) selected.
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING )
+>
+>FROM olap_emp ;
NUM WORKGROUPNUM ANNUALSALARY (EXPR) (EXPR)
---------- ------------ ------------ -------------------- --------------------
1 1 21 21 21
2 1 22 43 21
3 1 23 66 22
4 1 24 90 22
5 1 25 115 23
6 1 26 141 23
7 1 27 168 24
8 1 28 196 24
9 1 29 225 25
10 1 30 255 25
11 2 21 21 21
12 2 22 43 21
13 2 23 66 22
14 2 24 90 22
15 2 25 115 23
16 2 26 141 23
17 2 27 168 24
18 2 28 196 24
19 2 29 225 25
20 2 30 255 25
21 3 21 21 21
22 3 22 43 21
23 3 23 66 22
24 3 24 90 22
25 3 25 115 23
26 3 26 141 23
27 3 27 168 24
28 3 28 196 24
29 3 29 225 25
30 3 30 255 25
--- 30 row(s) selected.
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY paygrade,num ROWS UNBOUNDED PRECEDING )
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT num, workgroupnum, annualsalary,
+> SUM(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary ) OVER ( PARTITION BY workgroupnum ORDER BY paygrade,num ROWS UNBOUNDED PRECEDING )
+>
+>FROM olap_emp ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>--**************************************cancel rank*****************************
>>SELECT * FROM
+>(select rank() over (order by num) r from olap_emp) T
+>WHERE r > 10;
R
--------------------
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--- 20 row(s) selected.
>>
>>SELECT * FROM
+>(select rank() over (order by num) r from olap_emp) T
+>WHERE r >=10;
R
--------------------
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--- 21 row(s) selected.
>>
>>
>>
>>
>>SELECT * FROM
+>(select rank() over (order by num) r from olap_emp) T
+>WHERE r < 10;
R
--------------------
1
2
3
4
5
6
7
8
9
--- 9 row(s) selected.
>>
>>SELECT * FROM
+>(select rank() over (order by num) r from olap_emp) T
+>WHERE r <=10;
R
--------------------
1
2
3
4
5
6
7
8
9
10
--- 10 row(s) selected.
>>
>>
>>
>>
>>SELECT * FROM
+>(select rank() over (order by num) r from olap_emp) T
+>WHERE r <= 10 or r>= 20;
R
--------------------
1
2
3
4
5
6
7
8
9
10
20
21
22
23
24
25
26
27
28
29
30
--- 21 row(s) selected.
>>
>>
>>
>>
>>SELECT * FROM
+>(select rank() over (order by num) r from olap_emp) T
+>WHERE r <= 10 or r<= 5;
R
--------------------
1
2
3
4
5
6
7
8
9
10
--- 10 row(s) selected.
>>
>>
>>
>>SELECT * FROM
+>(select num,rank() over (order by num) r from olap_emp) T
+>WHERE r <= 10 or num >20;
NUM R
---------- --------------------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
--- 20 row(s) selected.
>>
>>
>>
>>SELECT * FROM
+>(select num,rank() over (order by num) r from olap_emp) T
+>WHERE r not in (select rank() over (order by num) r from olap_emp where num >12);
NUM R
---------- --------------------
19 19
20 20
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
--- 12 row(s) selected.
>>
>>--**************************************rank over 2 or more solumns**************************
>>
>>select rank() over (order by paygrade,num) from olap_emp;
(EXPR)
--------------------
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
--- 30 row(s) selected.
>>
>>select rank() over (order by paygrade,workgroupnum,num) from olap_emp;
(EXPR)
--------------------
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
--- 30 row(s) selected.
>>
>>select paygrade,workgroupnum,num,rank() over (order by paygrade,workgroupnum,num) from olap_emp;
PAYGRADE WORKGROUPNUM NUM (EXPR)
---------- ------------ ---------- --------------------
1 1 1 1
1 2 11 2
1 3 21 3
2 1 2 4
2 2 12 5
2 3 22 6
3 1 3 7
3 2 13 8
3 3 23 9
4 1 4 10
4 2 14 11
4 3 24 12
5 1 5 13
5 2 15 14
5 3 25 15
6 1 6 16
6 2 16 17
6 3 26 18
7 1 7 19
7 2 17 20
7 3 27 21
8 1 8 22
8 2 18 23
8 3 28 24
9 1 9 25
9 2 19 26
9 3 29 27
10 1 10 28
10 2 20 29
10 3 30 30
--- 30 row(s) selected.
>>
>>--**************************************rank with delete/update*****************************
>>delete from olap_emp where rank() over (order by paygrade,num) =1;
*** ERROR[15001] A syntax error occurred at or before:
delete from olap_emp where rank() over (order by paygrade,num) =1;
^ (62 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>
>>update emp set paygrade = rank() over (order by paygrade,num);
*** ERROR[15001] A syntax error occurred at or before:
update emp set paygrade = rank() over (order by paygrade,num);
^ (61 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>
>>-- *****************************test moving rank transformation*****************************
>>-- rank() over (partition by a order by b) ==> (rows since changed (a) - rows since changed (a,b)
>>
>>SELECT workgroupnum,paygrade, sum_annualsalary,Rank() over (partition by workgroupnum order by sum_annualsalary) TD_RANK
+>FROM (SELECT workgroupnum,paygrade, SUM(annualsalary) sum_annualsalary FROM olap_emp GROUP BY workgroupnum,paygrade) T
+>ORDER BY 2 DESC, 1;
WORKGROUPNUM PAYGRADE SUM_ANNUALSALARY TD_RANK
------------ ---------- -------------------- --------------------
1 10 30 10
2 10 30 10
3 10 30 10
1 9 29 9
2 9 29 9
3 9 29 9
1 8 28 8
2 8 28 8
3 8 28 8
1 7 27 7
2 7 27 7
3 7 27 7
1 6 26 6
2 6 26 6
3 6 26 6
1 5 25 5
2 5 25 5
3 5 25 5
1 4 24 4
2 4 24 4
3 4 24 4
1 3 23 3
2 3 23 3
3 3 23 3
1 2 22 2
2 2 22 2
3 2 22 2
1 1 21 1
2 1 21 1
3 1 21 1
--- 30 row(s) selected.
>>
>>--control query default DEF_MAX_HISTORY_ROWS '5';
>>
>>select sex,paygrade, rank() over (partition by sex order by paygrade) r from olap_emp;
SEX PAYGRADE R
--- ---------- --------------------
F 2 1
F 2 1
F 2 1
F 4 4
F 4 4
F 4 4
F 6 7
F 6 7
F 6 7
F 8 10
F 8 10
F 8 10
F 10 13
F 10 13
F 10 13
M 1 1
M 1 1
M 1 1
M 3 4
M 3 4
M 3 4
M 5 7
M 5 7
M 5 7
M 7 10
M 7 10
M 7 10
M 9 13
M 9 13
M 9 13
--- 30 row(s) selected.
>>
>>select rank() over (partition by sex,lastname order by paygrade) r from olap_emp;
R
--------------------
1
1
1
4
4
4
7
7
7
10
10
10
13
13
13
1
1
1
4
4
4
7
7
7
10
10
10
13
13
13
--- 30 row(s) selected.
>>
>>select rank() over (partition by sex order by paygrade,num) r from olap_emp;
R
--------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--- 30 row(s) selected.
>>
>>select rank() over (partition by sex,lastname order by paygrade,num) r from olap_emp;
R
--------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--- 30 row(s) selected.
>>
>>select rank() over (partition by sex order by lastname,paygrade,num) r from olap_emp;
R
--------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--- 30 row(s) selected.
>>
>>select rank() over (partition by dateofbirth,sex order by lastname,paygrade,num) r from olap_emp;
R
--------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--- 30 row(s) selected.
>>
>>
>>select rank() over (partition by sex,lastname,left(firstname,2),middleinitial order by paygrade) r from olap_emp;
R
--------------------
1
1
1
2
1
2
1
1
3
4
4
1
2
3
1
1
2
1
1
2
3
1
1
3
1
2
3
1
1
1
--- 30 row(s) selected.
>>
>>
>>select rank() over (partition by sex,lastname,middleinitial order by paygrade,num) r from olap_emp;
R
--------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--- 30 row(s) selected.
>>
>>
>>select rank() over (partition by sex,lastname,left(firstname,2),middleinitial order by paygrade,workgroupnum,num) r from olap_emp;
R
--------------------
1
1
1
2
1
2
1
2
3
4
5
1
2
3
1
1
2
1
1
2
3
1
2
3
1
2
3
1
1
1
--- 30 row(s) selected.
>>
>>--control query default DEF_MAX_HISTORY_ROWS reset;
>>
>>--***************************** test partition by /order by with rank/dense_rank*****************************
>>
>>select rank() over (partition by sex ) from olap_emp;
*** ERROR[4344] Use of RANK or DENSE_RANK window functions without a window ORDER BY clause is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>select dense_rank() over (partition by sex ) from olap_emp;
*** ERROR[4344] Use of RANK or DENSE_RANK window functions without a window ORDER BY clause is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>select rank() over ( order by sex ) from olap_emp;
(EXPR)
--------------------
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
16
16
16
16
16
16
16
16
16
16
16
16
16
16
16
--- 30 row(s) selected.
>>
>>select dense_rank() over ( order by sex ) from olap_emp;
(EXPR)
--------------------
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
--- 30 row(s) selected.
>>
>>
>>select rank() over ( ) from olap_emp;
*** ERROR[4344] Use of RANK or DENSE_RANK window functions without a window ORDER BY clause is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>select dense_rank() over ( ) from olap_emp;
*** ERROR[4344] Use of RANK or DENSE_RANK window functions without a window ORDER BY clause is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>--***************************** test expressions in order by *****************************
>>prepare stat1 from
+>select case when workgroupnum=1 then 'x' when workgroupnum=2 then 'y' else 'z' end trans_count_expr, sex,
+> rank() over (order by case when workgroupnum=1 then 'x' when workgroupnum=2 then 'y' else 'z' end, sex) olap_rank
+>from olap_emp;
--- SQL command prepared.
>>-- result should match previous statement result
>>prepare stat2 from
+>select case when workgroupnum=1 then 'x' when workgroupnum=2 then 'y' else 'z' end trans_count_expr, sex,
+> rank() over (order by trans_count_expr, sex) olap_rank
+>from (select workgroupnum,sex,case when workgroupnum=1 then 'x' when workgroupnum=2 then 'y' else 'z' end trans_count_expr from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select -1 * annualsalary neg_total, rank() over (order by -1 * annualsalary) olap_rank from olap_emp;
--- SQL command prepared.
>>prepare stat2 from
+>select -1 * annualsalary neg_total, rank() over (order by neg_total2) olap_rank from
+>(select annualsalary, -1 * annualsalary neg_total2 from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select annualsalary/paygrade as A, rank() over(order by annualsalary/paygrade) olap_rank from olap_emp;
--- SQL command prepared.
>>prepare stat2 from
+>select annualsalary/paygrade as A, rank() over(order by B) olap_rank from
+>(select annualsalary,paygrade, annualsalary/paygrade B from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select annualsalary/cast(paygrade as DECIMAL(9,2)) as A, rank() over (order by annualsalary/cast(paygrade as DECIMAL(9,2))) olap_rank from olap_emp;
--- SQL command prepared.
>>prepare stat2 from
+>select annualsalary/cast(paygrade as DECIMAL(9,2)) as A, rank() over (order by B) olap_rank from
+>(select annualsalary, paygrade, annualsalary/cast(paygrade as DECIMAL(9,2)) B from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select paygrade, rank() over (order by paygrade +10) olap_rank from olap_emp;
--- SQL command prepared.
>>prepare stat2 from
+>select paygrade, rank() over (order by pay_total) olap_rank from
+>(select paygrade, paygrade+10 pay_total from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select paygrade, rank() over (order by paygrade -10) olap_rank from olap_emp;
--- SQL command prepared.
>>prepare stat2 from
+>select paygrade, rank() over (order by pay_total) olap_rank from
+>(select paygrade, paygrade-10 pay_total from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>-- aggregates in order by
>>prepare stat1 from
+>select num, rank() over (order by sum(annualsalary)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by sum_a) olap_rank from
+> (select num,sum(annualsalary) sum_a from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, rank() over (order by avg(annualsalary)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by avg_a) olap_rank from
+> (select num,avg(annualsalary) avg_a from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, rank() over (order by min(annualsalary)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by min_a) olap_rank from
+> (select num,min(annualsalary) min_a from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, rank() over (order by max(annualsalary)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by max_a) olap_rank from
+> (select num,max(annualsalary) max_a from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, rank() over (order by variance(annualsalary)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by variance_a) olap_rank from
+> (select num,variance(annualsalary) variance_a from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, rank() over (order by stddev(annualsalary)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by stddev_a) olap_rank from
+> (select num,stddev(annualsalary) stddev_a from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, rank() over (order by count(*)) olap_rank from olap_emp group by num order by num;
--- SQL command prepared.
>>prepare stat2 from
+>select num, rank() over (order by cnt) olap_rank from
+> (select num,count(*) cnt from olap_emp group by num) T order by num;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>--***************************** multiple olap functions w/ same expression in order by *****************************
>>prepare stat1 from
+>select num, workgroupnum,
+> rank() over (order by sum(annualsalary)*num) olap_rank,
+> dense_rank() over (order by sum(annualsalary)*num) olap_drank,
+> row_number() over (order by sum(annualsalary)*num) olap_rnum
+>from olap_emp
+>group by num, workgroupnum, annualsalary
+>order by num, workgroupnum;
--- SQL command prepared.
>>
>>prepare stat2 from
+>select num, workgroupnum,
+> rank() over (order by suma_num) olap_rank,
+> dense_rank() over (order by suma_num) olap_drank,
+> row_number() over (order by suma_num) olap_rnum
+>from
+> (select num,workgroupnum, annualsalary,
+> sum(annualsalary)*num suma_num
+> from olap_emp
+> group by num, workgroupnum, annualsalary) T
+> order by num, workgroupnum;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>prepare stat1 from
+>select num, workgroupnum, annualsalary,
+> SUM(AnnualSalary) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING ) ,
+> MIN(AnnualSalary) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING ) ,
+> MAX(AnnualSalary) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING ) ,
+> VARIANCE(AnnualSalary) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING ) ,
+> STDDEV(AnnualSalary) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING ) ,
+> COUNT(*) OVER (order by sum(annualsalary)*num ROWS UNBOUNDED PRECEDING )
+>from olap_emp
+>group by num, workgroupnum, annualsalary
+>order by num, workgroupnum;
--- SQL command prepared.
>>
>>prepare stat2 from
+>select num, workgroupnum, annualsalary,
+> SUM(AnnualSalary) OVER (order by suma_num ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary) OVER (order by suma_num ROWS UNBOUNDED PRECEDING ) ,
+> MIN(AnnualSalary) OVER (order by suma_num ROWS UNBOUNDED PRECEDING ) ,
+> MAX(AnnualSalary) OVER (order by suma_num ROWS UNBOUNDED PRECEDING ) ,
+> VARIANCE(AnnualSalary) OVER (order by suma_num ROWS UNBOUNDED PRECEDING ) ,
+> STDDEV(AnnualSalary) OVER (order by suma_num ROWS UNBOUNDED PRECEDING ) ,
+> COUNT(*) OVER (order by suma_num ROWS UNBOUNDED PRECEDING )
+>from
+> (select num, workgroupnum, annualsalary,
+> sum(annualsalary)*num suma_num
+> from olap_emp
+> group by num,workgroupnum,annualsalary) T
+> order by num, workgroupnum;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>--************************** multiple olap functions w/ different expression in order by **************************
>>select num, workgroupnum,
+> rank() over (order by sum(annualsalary)) olap_rank,
+> dense_rank() over (order by avg(annualsalary)) olap_drank,
+> row_number() over (order by sum(annualsalary)) olap_rnum
+>from olap_emp group by num, workgroupnum, annualsalary;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>select num, workgroupnum,
+> rank() over (order by sum_a) olap_rank,
+> dense_rank() over (order by avg_a) olap_drank,
+> row_number() over (order by sum_a) olap_rnum
+>from
+> (select num,workgroupnum, annualsalary,
+> sum(annualsalary) sum_a,
+> avg(annualsalary) avg_a
+> from olap_emp
+> group by num, workgroupnum, annualsalary) T ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>select num, workgroupnum, annualsalary,
+> SUM(AnnualSalary) OVER (order by sum(annualsalary) ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary) OVER (order by avg(annualsalary) ROWS UNBOUNDED PRECEDING ) ,
+> MIN(AnnualSalary) OVER (order by min(annualsalary) ROWS UNBOUNDED PRECEDING ) ,
+> MAX(AnnualSalary) OVER (order by max(annualsalary) ROWS UNBOUNDED PRECEDING ) ,
+> VARIANCE(AnnualSalary) OVER (order by variance(annualsalary) ROWS UNBOUNDED PRECEDING ) ,
+> STDDEV(AnnualSalary) OVER (order by stddev(annualsalary) ROWS UNBOUNDED PRECEDING ) ,
+> COUNT(*) OVER (order by count(*) ROWS UNBOUNDED PRECEDING )
+>from olap_emp group by num, workgroupnum, annualsalary;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>select num, workgroupnum, annualsalary,
+> SUM(AnnualSalary) OVER (order by sum_a ROWS UNBOUNDED PRECEDING ) ,
+> AVG(AnnualSalary) OVER (order by avg_a ROWS UNBOUNDED PRECEDING ) ,
+> MIN(AnnualSalary) OVER (order by min_a ROWS UNBOUNDED PRECEDING ) ,
+> MAX(AnnualSalary) OVER (order by max_a ROWS UNBOUNDED PRECEDING ) ,
+> VARIANCE(AnnualSalary) OVER (order by var_a ROWS UNBOUNDED PRECEDING ) ,
+> STDDEV(AnnualSalary) OVER (order by stddev_a ROWS UNBOUNDED PRECEDING ) ,
+> COUNT(*) OVER (order by cnt ROWS UNBOUNDED PRECEDING )
+>from
+>(select num,workgroupnum, annualsalary,
+> sum(annualsalary) sum_a,
+> avg(annualsalary) avg_a,
+> min(annualsalary) min_a,
+> max(annualsalary) max_a,
+> variance(annualsalary) var_a,
+> stddev(annualsalary) stddev_a,
+> count(*) cnt from olap_emp group by num, workgroupnum, annualsalary) T ;
*** ERROR[4340] All Window Functions within a query must have the same window partition clause and window order clause.
*** ERROR[8822] The statement was not prepared.
>>
>>--***************************** strings in order by *****************************
>>prepare stat1 from
+>select substring(firstname, 1 ,4) sub_firstname, rank() over (order by substring(firstname, 1 ,4)) as olap_rank
+>from olap_emp;
--- SQL command prepared.
>>prepare stat2 from
+>select substring(firstname, 1 ,4) sub_firstname, rank() over (order by sub_firstname2) as olap_rank
+>from
+> (select firstname,substring(firstname,1,4) sub_firstname2 from olap_emp) T;
--- SQL command prepared.
>>log;
1,2c1,2
< >> log TEMP1;
< >> execute stat1;
---
> >> log TEMP2;
> >> execute stat2;
>>
>>--***************************** olap with qualify (not allowed) *****************************
>>select workgroupnum, sex,rank() over(order by workgroupnum, sex) olap_rank
+> from olap_emp qualify workgroupnum> 1 and rank() over(order by workgroupnum, sex)<200;
*** ERROR[15001] A syntax error occurred at or before:
select workgroupnum, sex,rank() over(order by workgroupnum, sex) olap_rank fr
om olap_emp qualify workgroupnum> 1 and rank() over(order by workgroupnum, sex)
^ (111 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>
>>log;
>>
>>
>>
>>create table TDemployee
+> (
+> Num integer unsigned no default not null not droppable ,
+> LastName char(24) no default not null not droppable ,
+> FirstName char(14) no default not null not droppable ,
+> MiddleInitial char no default not null not droppable ,
+> Sex char no default not null not droppable ,
+> WorkGroupNum integer unsigned no default not null not droppable ,
+> PayGrade integer unsigned no default not null not droppable ,
+> AnnualSalary integer unsigned no default not null not droppable ,
+> DateOfBirth date no default not null not droppable ,
+> primary key ( Num ) not droppable
+> )
+> store by primary key;
--- SQL operation complete.
>>
>>insert into TDemployee values
+>(1,'Anderson','Alvin','A','M',1,1,21000,DATE '01/07/1941'),
+>(2,'Anderson','Anna ','A','F',1,2,22000,DATE '02/07/1941'),
+>(3,'Anderson','Aloysius','A','M',1,3,23000,DATE '03/07/1941'),
+>(4, 'Anderson','Abby ','A','F',1,4,24000 ,DATE '04/07/1941'),
+>(5,'Anderson','Alan ','A','M',1,5,25000, DATE '05/07/1941'),
+>(6,'Anderson','Anne ','A','F',1,6,26000,DATE '06/07/1941'),
+>(7,'Anderson','Abelard','A','M',1,7,27000,DATE '07/07/1941'),
+>(8,'Anderson','Alice','A','F',1,8,28000,DATE '08/07/1941'),
+>(9,'Anderson','Anton','A','M',1,9,29000,DATE '09/07/1941'),
+>(10,'Anderson','Alicia ','A','F',1,10,30000,DATE '10/07/1941'),
+>(11,'Anderson','Abe','A','M',2,1,21000,DATE '11/07/1941'),
+>(12,'Anderson','Amanda ','A','F',2,2,22000,DATE '12/07/1941'),
+>(13,'Anderson','Andrew ','A','M',2,3,23000,DATE '01/07/1941'),
+>(14,'Anderson','Annette','A','F',2,4,24000,DATE '02/07/1941'),
+>(15,'Anderson','Akihiro','A','M',2,5,25000,DATE '03/07/1941'),
+>(16,'Anderson','Andrea ','A','F',2,6,26000,DATE '04/07/1941'),
+>(17,'Anderson','Arthur ','A','M',2,7,27000,DATE '05/07/1941'),
+>(18,'Anderson','Amy ','A','F',2,8,28000,DATE '06/07/1941'),
+>(19,'Anderson','Arnold ','A','M',2,9,29000,DATE '07/07/1941'),
+>(20,'Anderson','Adriana','A','F',2,10,30000,DATE '08/07/1941'),
+>(21,'Anderson','Arturo ','A','M',3,1,21000,DATE '09/07/1941'),
+>(22,'Anderson','Annabelle','A','F',3,2,22000,DATE '10/07/1941'),
+>(23,'Anderson','Andreas','A','M',3,3,23000,DATE '11/07/1941'),
+>(24,'Anderson','Becky','A','F',3,4,24000,DATE '12/07/1941'),
+>(25,'Anderson','Bill ','A','M',3,5,25000,DATE '01/08/1941'),
+>(26,'Anderson','Betsy','A','F',3,6,26000,DATE '02/08/1941'),
+>(27,'Anderson','Bob ','A','M',3,7,27000,DATE '03/08/1941'),
+>(28,'Anderson','Belinda','A','F',3,8,28000,DATE '04/08/1941'),
+>(29,'Anderson','Bruce','A','M',3,9,29000,DATE '12/08/1941'),
+>(30,'Anderson','Bridget','A','F',3,10,30000,DATE '06/08/1941');
--- 30 row(s) inserted.
>>
>>
>>
>>
>>CONTROL QUERY DEFAULT COMP_BOOL_200 'ON';
--- SQL operation complete.
>>
>>
>>
>>-- SEQUENCE FUNCTIONS --
>>
>>SELECT num ,
+> annualsalary,
+> RUNNINGSUM(annualsalary),
+> RUNNINGAVG(annualsalary),
+> RUNNINGMIN(annualsalary),
+> RUNNINGMAX(annualsalary),
+> RUNNINGSTDDEV(annualsalary),
+> RUNNINGVARIANCE(annualsalary)
+>FROM
+> TDemployee
+>SEQUENCE BY
+> num;
NUM ANNUALSALARY (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
---------- ------------ -------------------- -------------------- ---------- ---------- ------------------------- -------------------------
1 21000 21000 21000 21000 21000 0.00000000000000000E+000 0.00000000000000000E+000
2 22000 43000 21500 21000 22000 7.07106781186547584E+002 5.00000000000000000E+005
3 23000 66000 22000 21000 23000 1.00000000000000000E+003 1.00000000000000000E+006
4 24000 90000 22500 21000 24000 1.29099444873580576E+003 1.66666666666666688E+006
5 25000 115000 23000 21000 25000 1.58113883008418976E+003 2.50000000000000000E+006
6 26000 141000 23500 21000 26000 1.87082869338697056E+003 3.50000000000000000E+006
7 27000 168000 24000 21000 27000 2.16024689946928704E+003 4.66666666666666688E+006
8 28000 196000 24500 21000 28000 2.44948974278317792E+003 6.00000000000000000E+006
9 29000 225000 25000 21000 29000 2.73861278752583072E+003 7.50000000000000000E+006
10 30000 255000 25500 21000 30000 3.02765035409749120E+003 9.16666666666666624E+006
11 21000 276000 25090 21000 30000 3.17661912902839232E+003 1.00909090909090992E+007
12 22000 298000 24833 21000 30000 3.15748269507482752E+003 9.96969696969699712E+006
13 23000 321000 24692 21000 30000 3.06552375581007424E+003 9.39743589743590272E+006
14 24000 345000 24642 21000 30000 2.95106611393089920E+003 8.70879120879121920E+006
15 25000 370000 24666 21000 30000 2.84521318976944256E+003 8.09523809523800448E+006
16 26000 396000 24750 21000 30000 2.76887462097269184E+003 7.66666666666666752E+006
17 27000 423000 24882 21000 30000 2.73592655560178592E+003 7.48529411764705152E+006
18 28000 451000 25055 21000 30000 2.75408193782636960E+003 7.58496732026145024E+006
19 29000 480000 25263 21000 30000 2.82532407704866176E+003 7.98245614035087104E+006
20 30000 510000 25500 21000 30000 2.94689845877250880E+003 8.68421052631578880E+006
21 21000 531000 25285 21000 30000 3.03550419441081664E+003 9.21428571428566016E+006
22 22000 553000 25136 21000 30000 3.04404890996084480E+003 9.26623376623380736E+006
23 23000 576000 25043 21000 30000 3.00723764622446080E+003 9.04347826086963328E+006
24 24000 600000 25000 21000 30000 2.94883912309794304E+003 8.69565217391304320E+006
25 25000 625000 25000 21000 30000 2.88675134594812864E+003 8.33333333333333248E+006
26 26000 651000 25038 21000 30000 2.83521807599724704E+003 8.03846153846153088E+006
27 27000 678000 25111 21000 30000 2.80567191093237472E+003 7.87179487179491968E+006
28 28000 706000 25214 21000 30000 2.80683217850781376E+003 7.87830687830691840E+006
29 29000 735000 25344 21000 30000 2.84449169535193696E+003 8.09113300492613760E+006
30 30000 765000 25500 21000 30000 2.92138370616060800E+003 8.53448275862068992E+006
--- 30 row(s) selected.
>>
>>SELECT num ,
+> annualsalary,
+> MOVINGSUM(annualsalary,3),
+> MOVINGAVG(annualsalary,3),
+> MOVINGMIN(annualsalary,3),
+> MOVINGMAX(annualsalary,3),
+> MOVINGSTDDEV(annualsalary,3),
+> MOVINGVARIANCE(annualsalary,3)
+>FROM
+> TDemployee
+>SEQUENCE BY
+> num;
NUM ANNUALSALARY (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
---------- ------------ -------------------- -------------------- ---------- ---------- ------------------------- -------------------------
1 21000 21000 21000 21000 21000 0.00000000000000000E+000 0.00000000000000000E+000
2 22000 43000 21500 21000 22000 7.07106781186547584E+002 5.00000000000000000E+005
3 23000 66000 22000 21000 23000 1.00000000000000000E+003 1.00000000000000000E+006
4 24000 69000 23000 22000 24000 1.00000000000000000E+003 1.00000000000000000E+006
5 25000 72000 24000 23000 25000 1.00000000000000000E+003 1.00000000000000000E+006
6 26000 75000 25000 24000 26000 1.00000000000000000E+003 1.00000000000000000E+006
7 27000 78000 26000 25000 27000 1.00000000000000000E+003 1.00000000000000000E+006
8 28000 81000 27000 26000 28000 1.00000000000000000E+003 1.00000000000000000E+006
9 29000 84000 28000 27000 29000 1.00000000000000000E+003 1.00000000000000000E+006
10 30000 87000 29000 28000 30000 1.00000000000000000E+003 1.00000000000000000E+006
11 21000 80000 26666 21000 30000 4.93288286231623936E+003 2.43333333333332512E+007
12 22000 73000 24333 21000 30000 4.93288286231625152E+003 2.43333333333333728E+007
13 23000 66000 22000 21000 23000 1.00000000000000000E+003 1.00000000000000000E+006
14 24000 69000 23000 22000 24000 1.00000000000000000E+003 1.00000000000000000E+006
15 25000 72000 24000 23000 25000 1.00000000000000000E+003 1.00000000000000000E+006
16 26000 75000 25000 24000 26000 1.00000000000000000E+003 1.00000000000000000E+006
17 27000 78000 26000 25000 27000 1.00000000000000000E+003 1.00000000000000000E+006
18 28000 81000 27000 26000 28000 1.00000000000000000E+003 1.00000000000000000E+006
19 29000 84000 28000 27000 29000 1.00000000000000000E+003 1.00000000000000000E+006
20 30000 87000 29000 28000 30000 1.00000000000000000E+003 1.00000000000000000E+006
21 21000 80000 26666 21000 30000 4.93288286231623936E+003 2.43333333333332512E+007
22 22000 73000 24333 21000 30000 4.93288286231625152E+003 2.43333333333333728E+007
23 23000 66000 22000 21000 23000 1.00000000000000000E+003 1.00000000000000000E+006
24 24000 69000 23000 22000 24000 1.00000000000000000E+003 1.00000000000000000E+006
25 25000 72000 24000 23000 25000 1.00000000000000000E+003 1.00000000000000000E+006
26 26000 75000 25000 24000 26000 1.00000000000000000E+003 1.00000000000000000E+006
27 27000 78000 26000 25000 27000 1.00000000000000000E+003 1.00000000000000000E+006
28 28000 81000 27000 26000 28000 1.00000000000000000E+003 1.00000000000000000E+006
29 29000 84000 28000 27000 29000 1.00000000000000000E+003 1.00000000000000000E+006
30 30000 87000 29000 28000 30000 1.00000000000000000E+003 1.00000000000000000E+006
--- 30 row(s) selected.
>>
>>SELECT
+> annualsalary,
+> RUNNINGCOUNT(1),
+> ROWS SINCE CHANGED (paygrade) ,
+> RUNNINGCOUNT(1)-ROWS SINCE CHANGED (paygrade) + 1,
+> RANK(paygrade)
+>FROM
+> TDemployee
+>SEQUENCE BY
+> paygrade;
ANNUALSALARY (EXPR) (EXPR) (EXPR) (EXPR)
------------ -------------------- -------------------- -------------------- --------------------
21000 1 1 1 1
21000 2 2 1 1
21000 3 3 1 1
22000 4 1 4 4
22000 5 2 4 4
22000 6 3 4 4
23000 7 1 7 7
23000 8 2 7 7
23000 9 3 7 7
24000 10 1 10 10
24000 11 2 10 10
24000 12 3 10 10
25000 13 1 13 13
25000 14 2 13 13
25000 15 3 13 13
26000 16 1 16 16
26000 17 2 16 16
26000 18 3 16 16
27000 19 1 19 19
27000 20 2 19 19
27000 21 3 19 19
28000 22 1 22 22
28000 23 2 22 22
28000 24 3 22 22
29000 25 1 25 25
29000 26 2 25 25
29000 27 3 25 25
30000 28 1 28 28
30000 29 2 28 28
30000 30 3 28 28
--- 30 row(s) selected.
>>
>>
>>
>>SELECT RANK(paygrade) FROM TDemployee SEQUENCE BY paygrade;
(EXPR)
--------------------
1
1
1
4
4
4
7
7
7
10
10
10
13
13
13
16
16
16
19
19
19
22
22
22
25
25
25
28
28
28
--- 30 row(s) selected.
>>
>>SELECT negpaygrade,RANK(negpaygrade) FROM (SELECT -1 * paygrade as negpaygrade from TDemployee) t SEQUENCE BY negpaygrade;
NEGPAYGRADE (EXPR)
-------------------- --------------------
-10 1
-10 1
-10 1
-9 4
-9 4
-9 4
-8 7
-8 7
-8 7
-7 10
-7 10
-7 10
-6 13
-6 13
-6 13
-5 16
-5 16
-5 16
-4 19
-4 19
-4 19
-3 22
-3 22
-3 22
-2 25
-2 25
-2 25
-1 28
-1 28
-1 28
--- 30 row(s) selected.
>>
>>
>>--SF RANK WITH asc/desC
>>
>>SELECT RANK(workgroupnum ASC ) FROM TDemployee SEQUENCE BY workgroupnum;
*** ERROR[4362] Using ASC/DESC with sequence functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(workgroupnum DESC ) FROM TDemployee SEQUENCE BY workgroupnum;
*** ERROR[4362] Using ASC/DESC with sequence functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(workgroupnum ASC , sex DESC ) FROM TDemployee SEQUENCE BY workgroupnum, sex;
*** ERROR[4362] Using ASC/DESC with sequence functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(workgroupnum DESC , sex DESC) FROM TDemployee SEQUENCE BY workgroupnum, sex;
*** ERROR[4362] Using ASC/DESC with sequence functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(workgroupnum ASC , sex ASC) FROM TDemployee SEQUENCE BY workgroupnum, sex;
*** ERROR[4362] Using ASC/DESC with sequence functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>---------------------------------
>>
>>--QUALIFY & SEQUENCE FUNCTIONS
>>SELECT workgroupnum, sex, RANK(workgroupnum, sex) TD_RANK FROM TDemployee sequence by workgroupnum, sex qualify workgroupnum> 1 and rank(workgroupnum, sex)<200;
*** ERROR[4360] Using sequence by and qualify clauses together in the same query scope is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>-- NO TD GROUP BY
>>-- TERADATA TYPE RANK (TD RANK)
>>
>>SELECT DateOfBirth ,RANK(DateOfBirth) TD_RANK FROM TDemployee;
DATEOFBIRTH TD_RANK
----------- --------------------
1941-12-08 1
1941-12-07 2
1941-12-07 2
1941-11-07 4
1941-11-07 4
1941-10-07 6
1941-10-07 6
1941-09-07 8
1941-09-07 8
1941-08-07 10
1941-08-07 10
1941-07-07 12
1941-07-07 12
1941-06-08 14
1941-06-07 15
1941-06-07 15
1941-05-07 17
1941-05-07 17
1941-04-08 19
1941-04-07 20
1941-04-07 20
1941-03-08 22
1941-03-07 23
1941-03-07 23
1941-02-08 25
1941-02-07 26
1941-02-07 26
1941-01-08 28
1941-01-07 29
1941-01-07 29
--- 30 row(s) selected.
>>
>>SELECT firstname,RANK(firstname) TD_RANK FROM TDemployee;
FIRSTNAME TD_RANK
-------------- --------------------
Bruce 1
Bridget 2
Bob 3
Bill 4
Betsy 5
Belinda 6
Becky 7
Arturo 8
Arthur 9
Arnold 10
Anton 11
Annette 12
Anne 13
Annabelle 14
Anna 15
Andrew 16
Andreas 17
Andrea 18
Amy 19
Amanda 20
Alvin 21
Aloysius 22
Alicia 23
Alice 24
Alan 25
Akihiro 26
Adriana 27
Abelard 28
Abe 29
Abby 30
--- 30 row(s) selected.
>>
>>SELECT sex,RANK(sex) TD_RANK FROM TDemployee;
SEX TD_RANK
--- --------------------
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
--- 30 row(s) selected.
>>
>>SELECT annualsalary,RANK(annualsalary) TD_RANK FROM TDemployee;
ANNUALSALARY TD_RANK
------------ --------------------
30000 1
30000 1
30000 1
29000 4
29000 4
29000 4
28000 7
28000 7
28000 7
27000 10
27000 10
27000 10
26000 13
26000 13
26000 13
25000 16
25000 16
25000 16
24000 19
24000 19
24000 19
23000 22
23000 22
23000 22
22000 25
22000 25
22000 25
21000 28
21000 28
21000 28
--- 30 row(s) selected.
>>
>>SELECT annualsalary,RANK(annualsalary) TD_RANK FROM TDemployee WHERE paygrade >1;
ANNUALSALARY TD_RANK
------------ --------------------
30000 1
30000 1
30000 1
29000 4
29000 4
29000 4
28000 7
28000 7
28000 7
27000 10
27000 10
27000 10
26000 13
26000 13
26000 13
25000 16
25000 16
25000 16
24000 19
24000 19
24000 19
23000 22
23000 22
23000 22
22000 25
22000 25
22000 25
--- 27 row(s) selected.
>>
>>SELECT workgroupnum, sex,RANK(workgroupnum, sex) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
1 M 21
1 M 21
1 M 21
1 M 21
1 M 21
1 F 26
1 F 26
1 F 26
1 F 26
1 F 26
--- 30 row(s) selected.
>>
>>SELECT sex,RANK(sex) TD_RANK FROM TDemployee;
SEX TD_RANK
--- --------------------
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
M 1
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
F 16
--- 30 row(s) selected.
>>
>>SELECT negpaygrade,RANK(negpaygrade) TD_RANK FROM (SELECT -1 * paygrade as negpaygrade from TDemployee ) t;
NEGPAYGRADE TD_RANK
-------------------- --------------------
-1 1
-1 1
-1 1
-2 4
-2 4
-2 4
-3 7
-3 7
-3 7
-4 10
-4 10
-4 10
-5 13
-5 13
-5 13
-6 16
-6 16
-6 16
-7 19
-7 19
-7 19
-8 22
-8 22
-8 22
-9 25
-9 25
-9 25
-10 28
-10 28
-10 28
--- 30 row(s) selected.
>>
>>--Expressions with TD rank
>>
>>SELECT CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END, sex) TD_RANK
+>FROM TDemployee;
TRANS_COUNT_EXPR SEX TD_RANK
---------------- --- --------------------
z M 1
z M 1
z M 1
z M 1
z M 1
z F 6
z F 6
z F 6
z F 6
z F 6
y M 11
y M 11
y M 11
y M 11
y M 11
y F 16
y F 16
y F 16
y F 16
y F 16
x M 21
x M 21
x M 21
x M 21
x M 21
x F 26
x F 26
x F 26
x F 26
x F 26
--- 30 row(s) selected.
>>
>>select -1 * annualsalary neg_total, rank(-1 * annualsalary) TD_RANK from TDemployee;
NEG_TOTAL TD_RANK
-------------------- --------------------
-21000 1
-21000 1
-21000 1
-22000 4
-22000 4
-22000 4
-23000 7
-23000 7
-23000 7
-24000 10
-24000 10
-24000 10
-25000 13
-25000 13
-25000 13
-26000 16
-26000 16
-26000 16
-27000 19
-27000 19
-27000 19
-28000 22
-28000 22
-28000 22
-29000 25
-29000 25
-29000 25
-30000 28
-30000 28
-30000 28
--- 30 row(s) selected.
>>
>>SELECT case when workgroupnum =1 then 'A' else 'B' end workgroupnum_expr, sex,
+>RANK(case when workgroupnum =1 then 'A' else 'B' end, sex) TD_RANK
+>FROM TDemployee qualify RANK(case when workgroupnum =1 then 'A' else 'B' end, sex) > 1;
WORKGROUPNUM_EXPR SEX TD_RANK
----------------- --- --------------------
B F 11
B F 11
B F 11
B F 11
B F 11
B F 11
B F 11
B F 11
B F 11
B F 11
A M 21
A M 21
A M 21
A M 21
A M 21
A F 26
A F 26
A F 26
A F 26
A F 26
--- 20 row(s) selected.
>>
>>--Derived table
>>SELECT workgroupnum,paygrade, sum_annualsalary,Rank(sum_annualsalary) TD_RANK
+>FROM (SELECT workgroupnum,paygrade, SUM(annualsalary) sum_annualsalary FROM TDemployee GROUP BY workgroupnum,paygrade) T
+>ORDER BY 2 DESC, 1;
WORKGROUPNUM PAYGRADE SUM_ANNUALSALARY TD_RANK
------------ ---------- -------------------- --------------------
1 10 30000 1
2 10 30000 1
3 10 30000 1
1 9 29000 4
2 9 29000 4
3 9 29000 4
1 8 28000 7
2 8 28000 7
3 8 28000 7
1 7 27000 10
2 7 27000 10
3 7 27000 10
1 6 26000 13
2 6 26000 13
3 6 26000 13
1 5 25000 16
2 5 25000 16
3 5 25000 16
1 4 24000 19
2 4 24000 19
3 4 24000 19
1 3 23000 22
2 3 23000 22
3 3 23000 22
1 2 22000 25
2 2 22000 25
3 2 22000 25
1 1 21000 28
2 1 21000 28
3 1 21000 28
--- 30 row(s) selected.
>>
>>SELECT workgroupnum, sum_annualsalary,Rank(sum_annualsalary) TD_RANK
+>FROM (SELECT workgroupnum, SUM(annualsalary) sum_annualsalary FROM TDemployee GROUP BY workgroupnum) T
+>ORDER BY 2 DESC, 1;
WORKGROUPNUM SUM_ANNUALSALARY TD_RANK
------------ -------------------- --------------------
1 255000 1
2 255000 1
3 255000 1
--- 3 row(s) selected.
>>
>>
>>
>>--TD and ASC / DESC / default order (desc)
>>
>>SELECT workgroupnum,RANK(workgroupnum ASC ) TD_RANK FROM TDemployee;
WORKGROUPNUM TD_RANK
------------ --------------------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
3 21
3 21
3 21
3 21
3 21
3 21
3 21
3 21
3 21
3 21
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,RANK(workgroupnum DESC ) TD_RANK FROM TDemployee;
WORKGROUPNUM TD_RANK
------------ --------------------
3 1
3 1
3 1
3 1
3 1
3 1
3 1
3 1
3 1
3 1
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
1 21
1 21
1 21
1 21
1 21
1 21
1 21
1 21
1 21
1 21
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(workgroupnum ASC , sex DESC ) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
1 M 1
1 M 1
1 M 1
1 M 1
1 M 1
1 F 6
1 F 6
1 F 6
1 F 6
1 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
3 M 21
3 M 21
3 M 21
3 M 21
3 M 21
3 F 26
3 F 26
3 F 26
3 F 26
3 F 26
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(workgroupnum DESC , sex DESC) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
1 M 21
1 M 21
1 M 21
1 M 21
1 M 21
1 F 26
1 F 26
1 F 26
1 F 26
1 F 26
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(workgroupnum ASC , sex ASC) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
1 F 1
1 F 1
1 F 1
1 F 1
1 F 1
1 M 6
1 M 6
1 M 6
1 M 6
1 M 6
2 F 11
2 F 11
2 F 11
2 F 11
2 F 11
2 M 16
2 M 16
2 M 16
2 M 16
2 M 16
3 F 21
3 F 21
3 F 21
3 F 21
3 F 21
3 M 26
3 M 26
3 M 26
3 M 26
3 M 26
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(workgroupnum ASC , sex) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
1 M 1
1 M 1
1 M 1
1 M 1
1 M 1
1 F 6
1 F 6
1 F 6
1 F 6
1 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
3 M 21
3 M 21
3 M 21
3 M 21
3 M 21
3 F 26
3 F 26
3 F 26
3 F 26
3 F 26
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(workgroupnum , sex ASC) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 F 1
3 F 1
3 F 1
3 F 1
3 F 1
3 M 6
3 M 6
3 M 6
3 M 6
3 M 6
2 F 11
2 F 11
2 F 11
2 F 11
2 F 11
2 M 16
2 M 16
2 M 16
2 M 16
2 M 16
1 F 21
1 F 21
1 F 21
1 F 21
1 F 21
1 M 26
1 M 26
1 M 26
1 M 26
1 M 26
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(workgroupnum, sex) TD_RANK FROM TDemployee;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
1 M 21
1 M 21
1 M 21
1 M 21
1 M 21
1 F 26
1 F 26
1 F 26
1 F 26
1 F 26
--- 30 row(s) selected.
>>
>>SELECT CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee;
TRANS_COUNT_EXPR SEX TD_RANK
---------------- --- --------------------
x M 1
x M 1
x M 1
x M 1
x M 1
x F 6
x F 6
x F 6
x F 6
x F 6
y M 11
y M 11
y M 11
y M 11
y M 11
y F 16
y F 16
y F 16
y F 16
y F 16
z M 21
z M 21
z M 21
z M 21
z M 21
z F 26
z F 26
z F 26
z F 26
z F 26
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,RANK(workgroupnum ASC ) TD_RANK FROM TDemployee qualify RANK(workgroupnum ASC ) >1;
WORKGROUPNUM TD_RANK
------------ --------------------
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
2 11
3 21
3 21
3 21
3 21
3 21
3 21
3 21
3 21
3 21
3 21
--- 20 row(s) selected.
>>
>>-----QUALIFY
>>
>>SELECT workgroupnum, sex,RANK(workgroupnum, sex) TD_RANK FROM TDemployee qualify workgroupnum> 1 and rank(workgroupnum, sex)<200;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
--- 20 row(s) selected.
>>
>>
>>SELECT workgroupnum, sex, RANK(workgroupnum, sex) TD_RANK FROM TDemployee qualify paygrade> 1;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
1 M 21
1 M 21
1 M 21
1 M 21
1 F 26
1 F 26
1 F 26
1 F 26
1 F 26
--- 27 row(s) selected.
>>
>>
>>SELECT workgroupnum, sex, RANK(workgroupnum, sex) TD_RANK FROM TDemployee qualify rank(workgroupnum, sex)<200 and paygrade >1;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
2 M 11
2 M 11
2 M 11
2 M 11
2 F 16
2 F 16
2 F 16
2 F 16
2 F 16
1 M 21
1 M 21
1 M 21
1 M 21
1 F 26
1 F 26
1 F 26
1 F 26
1 F 26
--- 27 row(s) selected.
>>
>>--TD rank in qualify clause but no TD Rank in select list
>>
>>SELECT paygrade, sex FROM TDemployee qualify RANK(paygrade)> 1;
PAYGRADE SEX
---------- ---
9 M
9 M
9 M
8 F
8 F
8 F
7 M
7 M
7 M
6 F
6 F
6 F
5 M
5 M
5 M
4 F
4 F
4 F
3 M
3 M
3 M
2 F
2 F
2 F
1 M
1 M
1 M
--- 27 row(s) selected.
>>
>>--TD RANK neither in qualify nor in select list
>>
>>SELECT paygrade, sex FROM TDemployee qualify paygrade> 1;
*** ERROR[4363] Using Qualify clause without using rank function in the query is not allowed.
*** ERROR[8822] The statement was not prepared.
>>
>>--qualify and devived table
>>
>>SELECT workgroupnum,paygrade, sum_annualsalary,Rank(sum_annualsalary) TD_RANK
+>FROM (SELECT workgroupnum,paygrade, SUM(annualsalary) sum_annualsalary,SUM(paygrade) as sum_paygrade FROM TDemployee GROUP BY workgroupnum,paygrade) T
+>QUALIFY Rank(sum_annualsalary) >5
+>ORDER BY 3 desc ,1,2;
WORKGROUPNUM PAYGRADE SUM_ANNUALSALARY TD_RANK
------------ ---------- -------------------- --------------------
1 8 28000 7
2 8 28000 7
3 8 28000 7
1 7 27000 10
2 7 27000 10
3 7 27000 10
1 6 26000 13
2 6 26000 13
3 6 26000 13
1 5 25000 16
2 5 25000 16
3 5 25000 16
1 4 24000 19
2 4 24000 19
3 4 24000 19
1 3 23000 22
2 3 23000 22
3 3 23000 22
1 2 22000 25
2 2 22000 25
3 2 22000 25
1 1 21000 28
2 1 21000 28
3 1 21000 28
--- 24 row(s) selected.
>>
>>
>>SELECT workgroupnum, sum_paygrade
+>FROM (SELECT workgroupnum, SUM(annualsalary) sum_annualsalary,SUM(paygrade) as sum_paygrade FROM TDemployee GROUP BY workgroupnum) T
+>QUALIFY Rank(sum_paygrade) >2
+>ORDER BY 2 DESC, 1;
--- 0 row(s) selected.
>>
>>SELECT CASE paygrade WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE paygrade WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee
+>QUALIFY RANK(CASE paygrade WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) >1;
TRANS_COUNT_EXPR SEX TD_RANK
---------------- --- --------------------
y F 4
y F 4
y F 4
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z M 7
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
z F 19
--- 27 row(s) selected.
>>
>>---TD RANK , QUALIFY & WHERE
>>SELECT CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee where num <> 1
+>QUALIFY RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) >1;
TRANS_COUNT_EXPR SEX TD_RANK
---------------- --- --------------------
x F 5
x F 5
x F 5
x F 5
x F 5
y M 10
y M 10
y M 10
y M 10
y M 10
y F 15
y F 15
y F 15
y F 15
y F 15
z M 20
z M 20
z M 20
z M 20
z M 20
z F 25
z F 25
z F 25
z F 25
z F 25
--- 25 row(s) selected.
>>
>>SELECT CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee
+>QUALIFY RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) >1 and workgroupnum<>1;
TRANS_COUNT_EXPR SEX TD_RANK
---------------- --- --------------------
y M 11
y M 11
y M 11
y M 11
y M 11
y F 16
y F 16
y F 16
y F 16
y F 16
z M 21
z M 21
z M 21
z M 21
z M 21
z F 26
z F 26
z F 26
z F 26
z F 26
--- 20 row(s) selected.
>>
>>SELECT CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee
+>QUALIFY RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END , sex) >1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Multiple TD Ranks & equivalent expressions
>>
>>SELECT paygrade, RANK(annualsalary) TD_RANK2 FROM TDemployee qualify rank(paygrade)> 1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, RANK(paygrade) TD_RANK2 FROM TDemployee qualify rank(paygrade)> 1;
PAYGRADE TD_RANK2
---------- --------------------
9 4
9 4
9 4
8 7
8 7
8 7
7 10
7 10
7 10
6 13
6 13
6 13
5 16
5 16
5 16
4 19
4 19
4 19
3 22
3 22
3 22
2 25
2 25
2 25
1 28
1 28
1 28
--- 27 row(s) selected.
>>
>>SELECT annualsalary/paygrade as A, RANK(annualsalary/paygrade) TD_RANK2 FROM TDemployee qualify rank(annualsalary/paygrade)> 1;
A TD_RANK2
---------------------- --------------------
11000.0000000000 4
11000.0000000000 4
11000.0000000000 4
7666.6666666666 7
7666.6666666666 7
7666.6666666666 7
6000.0000000000 10
6000.0000000000 10
6000.0000000000 10
5000.0000000000 13
5000.0000000000 13
5000.0000000000 13
4333.3333333333 16
4333.3333333333 16
4333.3333333333 16
3857.1428571428 19
3857.1428571428 19
3857.1428571428 19
3500.0000000000 22
3500.0000000000 22
3500.0000000000 22
3222.2222222222 25
3222.2222222222 25
3222.2222222222 25
3000.0000000000 28
3000.0000000000 28
3000.0000000000 28
--- 27 row(s) selected.
>>
>>SELECT annualsalary/cast(paygrade as DECIMAL(9,2)) as A, RANK(annualsalary/cast(paygrade as DECIMAL(9,2))) TD_RANK2 FROM TDemployee qualify rank(annualsalary/cast(paygrade as DECIMAL(9,2)))> 1;
A TD_RANK2
--------------------- --------------------
11000.000000 4
11000.000000 4
11000.000000 4
7666.666666 7
7666.666666 7
7666.666666 7
6000.000000 10
6000.000000 10
6000.000000 10
5000.000000 13
5000.000000 13
5000.000000 13
4333.333333 16
4333.333333 16
4333.333333 16
3857.142857 19
3857.142857 19
3857.142857 19
3500.000000 22
3500.000000 22
3500.000000 22
3222.222222 25
3222.222222 25
3222.222222 25
3000.000000 28
3000.000000 28
3000.000000 28
--- 27 row(s) selected.
>>
>>SELECT paygrade, RANK(paygrade +10 ) TD_RANK2 FROM TDemployee qualify rank(paygrade +10)> 1;
PAYGRADE TD_RANK2
---------- --------------------
9 4
9 4
9 4
8 7
8 7
8 7
7 10
7 10
7 10
6 13
6 13
6 13
5 16
5 16
5 16
4 19
4 19
4 19
3 22
3 22
3 22
2 25
2 25
2 25
1 28
1 28
1 28
--- 27 row(s) selected.
>>
>>SELECT paygrade, RANK(paygrade +10 ) TD_RANK2 FROM TDemployee qualify rank(paygrade +11)> 1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK1 FROM TDemployee qualify rank(paygrade, sex)<200;
PAYGRADE SEX TD_RANK1
---------- --- --------------------
10 F 1
10 F 1
10 F 1
9 M 4
9 M 4
9 M 4
8 F 7
8 F 7
8 F 7
7 M 10
7 M 10
7 M 10
6 F 13
6 F 13
6 F 13
5 M 16
5 M 16
5 M 16
4 F 19
4 F 19
4 F 19
3 M 22
3 M 22
3 M 22
2 F 25
2 F 25
2 F 25
1 M 28
1 M 28
1 M 28
--- 30 row(s) selected.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK1 ,RANK(paygrade) TD_RANK2 FROM TDemployee qualify paygrade> 1 and rank(paygrade, sex)<200;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK FROM TDemployee qualify paygrade> 1 and rank(paygrade, sex asc)<200;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK FROM TDemployee qualify paygrade> 1 and rank(paygrade)<200;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>--TD rank & STRINGs
>>SELECT substring(firstname, 1 ,4) sub_firstname, rank(substring(firstname, 1 ,4)) as TD_RANK
+>FROM TDemployee
+>QUALIFY rank(substring(firstname, 1 ,4)) >1;
SUB_FIRSTNAME TD_RANK
------------- --------------------
Brid 2
Bob 3
Bill 4
Bets 5
Beli 6
Beck 7
Artu 8
Arth 9
Arno 10
Anto 11
Anne 12
Anne 12
Anna 14
Anna 14
Andr 16
Andr 16
Andr 16
Amy 19
Aman 20
Alvi 21
Aloy 22
Alic 23
Alic 23
Alan 25
Akih 26
Adri 27
Abel 28
Abe 29
Abby 30
--- 29 row(s) selected.
>>
>>SELECT substring(firstname, 1 ,4) sub_firstname, rank(substring(firstname, 1 ,4)) as TD_RANK
+>FROM TDemployee
+>QUALIFY rank(substring(firstname, 1 ,2)) >1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT UPPER(substring(firstname, 1 ,4)) up_sub_firstname , rank(UPPER(substring(firstname, 1 ,4))) as TD_RANK
+>FROM TDemployee
+>QUALIFY rank(UPPER(substring(firstname, 1 ,4))) >1;
UP_SUB_FIRSTNAME TD_RANK
---------------- --------------------
BRID 2
BOB 3
BILL 4
BETS 5
BELI 6
BECK 7
ARTU 8
ARTH 9
ARNO 10
ANTO 11
ANNE 12
ANNE 12
ANNA 14
ANNA 14
ANDR 16
ANDR 16
ANDR 16
AMY 19
AMAN 20
ALVI 21
ALOY 22
ALIC 23
ALIC 23
ALAN 25
AKIH 26
ADRI 27
ABEL 28
ABE 29
ABBY 30
--- 29 row(s) selected.
>>
>>-----TD RANK and aggregate functions
>>SELECT num, Rank(sum(annualsalary)) TD_RANK
+>FROM TDemployee
+>GROUP BY num;
*** ERROR[4365] Using rank function and aggregate functions in the same scope is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT num, Rank(annualsalary) TD_RANK ,sum(annualsalary)
+>FROM TDemployee
+>GROUP BY num;
*** ERROR[4365] Using rank function and aggregate functions in the same scope is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>SELECT paygrade,rank(annualsalary) r
+>FROM TDemployee
+>GROUP BY paygrade
+>having paygrade>1;
*** ERROR[4109] Sequence functions are placed incorrectly: RUNNINGRANK(ANNUALSALARY).
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade,rank(annualsalary) r
+>FROM TDemployee
+>GROUP BY paygrade
+>having paygrade>1;
*** ERROR[4109] Sequence functions are placed incorrectly: RUNNINGRANK(ANNUALSALARY).
*** ERROR[8822] The statement was not prepared.
>>
>>
>>
>>---TD and SF --
>>SELECT num, runningsum(annualsalary) , rank(annualsalary) from TDemployee;
*** ERROR[4367] Using rank function and sequence functions together in the same query scope is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT num, runningsum(annualsalary) , rank(annualsalary) from TDemployee SEQUENCE BY num;
NUM (EXPR) (EXPR)
---------- -------------------- --------------------
1 21000 1
2 43000 2
3 66000 3
4 90000 4
5 115000 5
6 141000 6
7 168000 7
8 196000 8
9 225000 9
10 255000 10
11 276000 11
12 298000 12
13 321000 13
14 345000 14
15 370000 15
16 396000 16
17 423000 17
18 451000 18
19 480000 19
20 510000 20
21 531000 21
22 553000 22
23 576000 23
24 600000 24
25 625000 25
26 651000 26
27 678000 27
28 706000 28
29 735000 29
30 765000 30
--- 30 row(s) selected.
>>
>>--RANK in WEHERE clause -- not supported
>>SELECT num,rank(paygrade) FROM TDemployee where RANK(paygrade) < 2;
*** ERROR[4364] Rank can be placed only in the select list or the qualify clause.
*** ERROR[8822] The statement was not prepared.
>>
>>-- TD RANK and diffrent scopes
>>
>>SELECT * FROM (SELECT num, paygrade,RANK(paygrade) r_paygrade FROM TDemployee) t1 join (SELECT num, workgroupnum, RANK(workgroupnum) r_workgroupnum FROM TDemployee) t2 on t1.num=t2.num order by t1.num;
NUM PAYGRADE R_PAYGRADE NUM WORKGROUPNUM R_WORKGROUPNUM
---------- ---------- -------------------- ---------- ------------ --------------------
1 1 28 1 1 21
2 2 25 2 1 21
3 3 22 3 1 21
4 4 19 4 1 21
5 5 16 5 1 21
6 6 13 6 1 21
7 7 10 7 1 21
8 8 7 8 1 21
9 9 4 9 1 21
10 10 1 10 1 21
11 1 28 11 2 11
12 2 25 12 2 11
13 3 22 13 2 11
14 4 19 14 2 11
15 5 16 15 2 11
16 6 13 16 2 11
17 7 10 17 2 11
18 8 7 18 2 11
19 9 4 19 2 11
20 10 1 20 2 11
21 1 28 21 3 1
22 2 25 22 3 1
23 3 22 23 3 1
24 4 19 24 3 1
25 5 16 25 3 1
26 6 13 26 3 1
27 7 10 27 3 1
28 8 7 28 3 1
29 9 4 29 3 1
30 10 1 30 3 1
--- 30 row(s) selected.
>>
>>
>>SELECT paygrade,r,RANK(r)
+>FROM
+>(SELECT PAYGRADE,RANK(ANNUALSALARY) r FROM TDEMPLOYEE sequence by num ) t;
PAYGRADE R (EXPR)
---------- -------------------- --------------------
10 30 1
9 29 2
8 28 3
7 27 4
6 26 5
5 25 6
4 24 7
3 23 8
2 22 9
1 21 10
10 20 11
9 19 12
8 18 13
7 17 14
6 16 15
5 15 16
4 14 17
3 13 18
2 12 19
1 11 20
10 10 21
9 9 22
8 8 23
7 7 24
6 6 25
5 5 26
4 4 27
3 3 28
2 2 29
1 1 30
--- 30 row(s) selected.
>>
>>--------------------------------------------------------------------------------------
>>--------------------------------------------------------------------------------------
>>--------------------------------------------------------------------------------------
>>
>>--TD GROUP BY
>>
>>SELECT workgroupnum, sex, RANK(workgroupnum, sex) TD_RANK FROM TDemployee sequence by workgroupnum, sex GROUP BY sex qualify workgroupnum> 1 and rank(workgroupnum, sex)<200;
*** ERROR[4360] Using sequence by and qualify clauses together in the same query scope is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>-- TERADATA TYPE RANK (TD RANK)
>>
>>SELECT DateOfBirth ,RANK(DateOfBirth) TD_RANK FROM TDemployee GROUP BY sex,workgroupnum;
DATEOFBIRTH TD_RANK
----------- --------------------
1941-10-07 1
1941-08-07 2
1941-06-07 3
1941-04-07 4
1941-02-07 5
1941-12-07 1
1941-08-07 2
1941-06-07 3
1941-04-07 4
1941-02-07 5
1941-12-07 1
1941-10-07 2
1941-06-08 3
1941-04-08 4
1941-02-08 5
1941-09-07 1
1941-07-07 2
1941-05-07 3
1941-03-07 4
1941-01-07 5
1941-11-07 1
1941-07-07 2
1941-05-07 3
1941-03-07 4
1941-01-07 5
1941-12-08 1
1941-11-07 2
1941-09-07 3
1941-03-08 4
1941-01-08 5
--- 30 row(s) selected.
>>
>>SELECT firstname,RANK(firstname) TD_RANK FROM TDemployee GROUP BY sex;
FIRSTNAME TD_RANK
-------------- --------------------
Bridget 1
Betsy 2
Belinda 3
Becky 4
Annette 5
Anne 6
Annabelle 7
Anna 8
Andrea 9
Amy 10
Amanda 11
Alicia 12
Alice 13
Adriana 14
Abby 15
Bruce 1
Bob 2
Bill 3
Arturo 4
Arthur 5
Arnold 6
Anton 7
Andrew 8
Andreas 9
Alvin 10
Aloysius 11
Alan 12
Akihiro 13
Abelard 14
Abe 15
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(sex) TD_RANK FROM TDemployee GROUP BY workgroupnum;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
1 M 1
1 M 1
1 M 1
1 M 1
1 M 1
1 F 6
1 F 6
1 F 6
1 F 6
1 F 6
2 M 1
2 M 1
2 M 1
2 M 1
2 M 1
2 F 6
2 F 6
2 F 6
2 F 6
2 F 6
3 M 1
3 M 1
3 M 1
3 M 1
3 M 1
3 F 6
3 F 6
3 F 6
3 F 6
3 F 6
--- 30 row(s) selected.
>>
>>SELECT sex,workgroupnum,annualsalary,RANK(annualsalary) TD_RANK FROM TDemployee GROUP BY sex,workgroupnum;
SEX WORKGROUPNUM ANNUALSALARY TD_RANK
--- ------------ ------------ --------------------
F 1 30000 1
F 1 28000 2
F 1 26000 3
F 1 24000 4
F 1 22000 5
F 2 30000 1
F 2 28000 2
F 2 26000 3
F 2 24000 4
F 2 22000 5
F 3 30000 1
F 3 28000 2
F 3 26000 3
F 3 24000 4
F 3 22000 5
M 1 29000 1
M 1 27000 2
M 1 25000 3
M 1 23000 4
M 1 21000 5
M 2 29000 1
M 2 27000 2
M 2 25000 3
M 2 23000 4
M 2 21000 5
M 3 29000 1
M 3 27000 2
M 3 25000 3
M 3 23000 4
M 3 21000 5
--- 30 row(s) selected.
>>
>>SELECT sex,annualsalary,RANK(annualsalary) TD_RANK FROM TDemployee WHERE paygrade >1 GROUP BY sex;
SEX ANNUALSALARY TD_RANK
--- ------------ --------------------
F 30000 1
F 30000 1
F 30000 1
F 28000 4
F 28000 4
F 28000 4
F 26000 7
F 26000 7
F 26000 7
F 24000 10
F 24000 10
F 24000 10
F 22000 13
F 22000 13
F 22000 13
M 29000 1
M 29000 1
M 29000 1
M 27000 4
M 27000 4
M 27000 4
M 25000 7
M 25000 7
M 25000 7
M 23000 10
M 23000 10
M 23000 10
--- 27 row(s) selected.
>>
>>SELECT workgroupnum, paygrade, sex,RANK(workgroupnum, sex) TD_RANK FROM TDemployee GROUP BY paygrade,sex;
WORKGROUPNUM PAYGRADE SEX TD_RANK
------------ ---------- --- --------------------
3 1 M 1
2 1 M 2
1 1 M 3
3 2 F 1
2 2 F 2
1 2 F 3
3 3 M 1
2 3 M 2
1 3 M 3
3 4 F 1
2 4 F 2
1 4 F 3
3 5 M 1
2 5 M 2
1 5 M 3
3 6 F 1
2 6 F 2
1 6 F 3
3 7 M 1
2 7 M 2
1 7 M 3
3 8 F 1
2 8 F 2
1 8 F 3
3 9 M 1
2 9 M 2
1 9 M 3
3 10 F 1
2 10 F 2
1 10 F 3
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,sex,RANK(sex) TD_RANK FROM TDemployee GROUP BY workgroupnum,sex;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
1 F 1
1 F 1
1 F 1
1 F 1
1 F 1
1 M 1
1 M 1
1 M 1
1 M 1
1 M 1
2 F 1
2 F 1
2 F 1
2 F 1
2 F 1
2 M 1
2 M 1
2 M 1
2 M 1
2 M 1
3 F 1
3 F 1
3 F 1
3 F 1
3 F 1
3 M 1
3 M 1
3 M 1
3 M 1
3 M 1
--- 30 row(s) selected.
>>
>>SELECT negpaygrade,RANK(negpaygrade) TD_RANK FROM (SELECT workgroupnum, -1 * paygrade as negpaygrade from TDemployee ) t GROUP BY workgroupnum;
NEGPAYGRADE TD_RANK
-------------------- --------------------
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
-7 7
-8 8
-9 9
-10 10
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
-7 7
-8 8
-9 9
-10 10
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
-7 7
-8 8
-9 9
-10 10
--- 30 row(s) selected.
>>
>>SELECT case when workgroupnum =1 then 'A' else 'B' end expr, paygrade,RANK(paygrade) TD_RANK FROM TDemployee GROUP BY case when workgroupnum =1 then 'A' else 'B' end;
EXPR PAYGRADE TD_RANK
---- ---------- --------------------
A 10 1
A 9 2
A 8 3
A 7 4
A 6 5
A 5 6
A 4 7
A 3 8
A 2 9
A 1 10
B 10 1
B 10 1
B 9 3
B 9 3
B 8 5
B 8 5
B 7 7
B 7 7
B 6 9
B 6 9
B 5 11
B 5 11
B 4 13
B 4 13
B 3 15
B 3 15
B 2 17
B 2 17
B 1 19
B 1 19
--- 30 row(s) selected.
>>
>>--Expressions with TD rank
>>
>>SELECT sex,CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END, sex) TD_RANK
+>FROM TDemployee
+>GROUP BY sex;
SEX TRANS_COUNT_EXPR SEX TD_RANK
--- ---------------- --- --------------------
F z F 1
F z F 1
F z F 1
F z F 1
F z F 1
F y F 6
F y F 6
F y F 6
F y F 6
F y F 6
F x F 11
F x F 11
F x F 11
F x F 11
F x F 11
M z M 1
M z M 1
M z M 1
M z M 1
M z M 1
M y M 6
M y M 6
M y M 6
M y M 6
M y M 6
M x M 11
M x M 11
M x M 11
M x M 11
M x M 11
--- 30 row(s) selected.
>>
>>select -1 * annualsalary neg_total, rank(-1 * annualsalary) TD_RANK from TDemployee GROUP BY dateofbirth;
NEG_TOTAL TD_RANK
-------------------- --------------------
-21000 1
-23000 2
-25000 1
-22000 1
-24000 2
-26000 1
-23000 1
-25000 2
-27000 1
-24000 1
-26000 2
-28000 1
-25000 1
-27000 2
-26000 1
-28000 2
-30000 1
-27000 1
-29000 2
-28000 1
-30000 2
-21000 1
-29000 2
-22000 1
-30000 2
-21000 1
-23000 2
-22000 1
-24000 2
-29000 1
--- 30 row(s) selected.
>>
>>SELECT case when workgroupnum =1 then 'A' else 'B' end workgroupnum_expr, sex,
+>RANK(case when workgroupnum =1 then 'A' else 'B' end, sex) TD_RANK
+>FROM TDemployee
+>GROUP BY paygrade
+>qualify RANK(case when workgroupnum =1 then 'A' else 'B' end, sex) > 1;
WORKGROUPNUM_EXPR SEX TD_RANK
----------------- --- --------------------
A M 3
A F 3
A M 3
A F 3
A M 3
A F 3
A M 3
A F 3
A M 3
A F 3
--- 10 row(s) selected.
>>
>>--Derived table
>>SELECT workgroupnum,paygrade, sum_annualsalary,Rank(sum_annualsalary) TD_RANK
+>FROM (SELECT workgroupnum,paygrade, SUM(annualsalary) sum_annualsalary FROM TDemployee GROUP BY workgroupnum,paygrade) T
+>GROUP BY workgroupnum
+>ORDER BY 2 DESC, 1;
WORKGROUPNUM PAYGRADE SUM_ANNUALSALARY TD_RANK
------------ ---------- -------------------- --------------------
1 10 30000 1
2 10 30000 1
3 10 30000 1
1 9 29000 2
2 9 29000 2
3 9 29000 2
1 8 28000 3
2 8 28000 3
3 8 28000 3
1 7 27000 4
2 7 27000 4
3 7 27000 4
1 6 26000 5
2 6 26000 5
3 6 26000 5
1 5 25000 6
2 5 25000 6
3 5 25000 6
1 4 24000 7
2 4 24000 7
3 4 24000 7
1 3 23000 8
2 3 23000 8
3 3 23000 8
1 2 22000 9
2 2 22000 9
3 2 22000 9
1 1 21000 10
2 1 21000 10
3 1 21000 10
--- 30 row(s) selected.
>>
>>
>>
>>--TD and ASC / DESC / default order (desc)
>>
>>SELECT sex,workgroupnum,RANK(workgroupnum ASC ) TD_RANK FROM TDemployee GROUP BY sex;
SEX WORKGROUPNUM TD_RANK
--- ------------ --------------------
F 1 1
F 1 1
F 1 1
F 1 1
F 1 1
F 2 6
F 2 6
F 2 6
F 2 6
F 2 6
F 3 11
F 3 11
F 3 11
F 3 11
F 3 11
M 1 1
M 1 1
M 1 1
M 1 1
M 1 1
M 2 6
M 2 6
M 2 6
M 2 6
M 2 6
M 3 11
M 3 11
M 3 11
M 3 11
M 3 11
--- 30 row(s) selected.
>>
>>SELECT workgroupnum,RANK(workgroupnum DESC ) TD_RANK FROM TDemployee GROUP BY sex;
WORKGROUPNUM TD_RANK
------------ --------------------
3 1
3 1
3 1
3 1
3 1
2 6
2 6
2 6
2 6
2 6
1 11
1 11
1 11
1 11
1 11
3 1
3 1
3 1
3 1
3 1
2 6
2 6
2 6
2 6
2 6
1 11
1 11
1 11
1 11
1 11
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,sex,RANK(workgroupnum ASC , sex DESC ) TD_RANK FROM TDemployee GROUP BY paygrade;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
1 1 M 1
1 2 M 2
1 3 M 3
2 1 F 1
2 2 F 2
2 3 F 3
3 1 M 1
3 2 M 2
3 3 M 3
4 1 F 1
4 2 F 2
4 3 F 3
5 1 M 1
5 2 M 2
5 3 M 3
6 1 F 1
6 2 F 2
6 3 F 3
7 1 M 1
7 2 M 2
7 3 M 3
8 1 F 1
8 2 F 2
8 3 F 3
9 1 M 1
9 2 M 2
9 3 M 3
10 1 F 1
10 2 F 2
10 3 F 3
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,sex,RANK(workgroupnum DESC , sex DESC) TD_RANK FROM TDemployee GROUP BY paygrade;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
1 3 M 1
1 2 M 2
1 1 M 3
2 3 F 1
2 2 F 2
2 1 F 3
3 3 M 1
3 2 M 2
3 1 M 3
4 3 F 1
4 2 F 2
4 1 F 3
5 3 M 1
5 2 M 2
5 1 M 3
6 3 F 1
6 2 F 2
6 1 F 3
7 3 M 1
7 2 M 2
7 1 M 3
8 3 F 1
8 2 F 2
8 1 F 3
9 3 M 1
9 2 M 2
9 1 M 3
10 3 F 1
10 2 F 2
10 1 F 3
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,sex,RANK(workgroupnum ASC , sex ASC) TD_RANK FROM TDemployee GROUP BY paygrade;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
1 1 M 1
1 2 M 2
1 3 M 3
2 1 F 1
2 2 F 2
2 3 F 3
3 1 M 1
3 2 M 2
3 3 M 3
4 1 F 1
4 2 F 2
4 3 F 3
5 1 M 1
5 2 M 2
5 3 M 3
6 1 F 1
6 2 F 2
6 3 F 3
7 1 M 1
7 2 M 2
7 3 M 3
8 1 F 1
8 2 F 2
8 3 F 3
9 1 M 1
9 2 M 2
9 3 M 3
10 1 F 1
10 2 F 2
10 3 F 3
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,sex,RANK(workgroupnum ASC , sex) TD_RANK FROM TDemployee GROUP BY paygrade;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
1 1 M 1
1 2 M 2
1 3 M 3
2 1 F 1
2 2 F 2
2 3 F 3
3 1 M 1
3 2 M 2
3 3 M 3
4 1 F 1
4 2 F 2
4 3 F 3
5 1 M 1
5 2 M 2
5 3 M 3
6 1 F 1
6 2 F 2
6 3 F 3
7 1 M 1
7 2 M 2
7 3 M 3
8 1 F 1
8 2 F 2
8 3 F 3
9 1 M 1
9 2 M 2
9 3 M 3
10 1 F 1
10 2 F 2
10 3 F 3
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,sex,RANK(workgroupnum , sex ASC) TD_RANK FROM TDemployee GROUP BY paygrade;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
1 3 M 1
1 2 M 2
1 1 M 3
2 3 F 1
2 2 F 2
2 1 F 3
3 3 M 1
3 2 M 2
3 1 M 3
4 3 F 1
4 2 F 2
4 1 F 3
5 3 M 1
5 2 M 2
5 1 M 3
6 3 F 1
6 2 F 2
6 1 F 3
7 3 M 1
7 2 M 2
7 1 M 3
8 3 F 1
8 2 F 2
8 1 F 3
9 3 M 1
9 2 M 2
9 1 M 3
10 3 F 1
10 2 F 2
10 1 F 3
--- 30 row(s) selected.
>>
>>SELECT paygrade,workgroupnum,sex,RANK(workgroupnum, sex) TD_RANK FROM TDemployee GROUP BY paygrade;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
1 3 M 1
1 2 M 2
1 1 M 3
2 3 F 1
2 2 F 2
2 1 F 3
3 3 M 1
3 2 M 2
3 1 M 3
4 3 F 1
4 2 F 2
4 1 F 3
5 3 M 1
5 2 M 2
5 1 M 3
6 3 F 1
6 2 F 2
6 1 F 3
7 3 M 1
7 2 M 2
7 1 M 3
8 3 F 1
8 2 F 2
8 1 F 3
9 3 M 1
9 2 M 2
9 1 M 3
10 3 F 1
10 2 F 2
10 1 F 3
--- 30 row(s) selected.
>>
>>SELECT CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE WHEN workgroupnum=1 THEN 'x' WHEN workgroupnum=2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee
+>GROUP BY sex;
TRANS_COUNT_EXPR SEX TD_RANK
---------------- --- --------------------
x F 1
x F 1
x F 1
x F 1
x F 1
y F 6
y F 6
y F 6
y F 6
y F 6
z F 11
z F 11
z F 11
z F 11
z F 11
x M 1
x M 1
x M 1
x M 1
x M 1
y M 6
y M 6
y M 6
y M 6
y M 6
z M 11
z M 11
z M 11
z M 11
z M 11
--- 30 row(s) selected.
>>
>>SELECT paygrade, workgroupnum,RANK(workgroupnum ASC ) TD_RANK FROM TDemployee GROUP BY paygrade qualify RANK(workgroupnum ASC ) >1;
PAYGRADE WORKGROUPNUM TD_RANK
---------- ------------ --------------------
1 2 2
1 3 3
2 2 2
2 3 3
3 2 2
3 3 3
4 2 2
4 3 3
5 2 2
5 3 3
6 2 2
6 3 3
7 2 2
7 3 3
8 2 2
8 3 3
9 2 2
9 3 3
10 2 2
10 3 3
--- 20 row(s) selected.
>>
>>-----QUALIFY
>>
>>SELECT workgroupnum, sex,RANK(workgroupnum, sex) TD_RANK FROM TDemployee GROUP BY paygrade qualify workgroupnum> 1 and rank(workgroupnum, sex)<200;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 M 1
2 M 2
3 F 1
2 F 2
3 M 1
2 M 2
3 F 1
2 F 2
3 M 1
2 M 2
3 F 1
2 F 2
3 M 1
2 M 2
3 F 1
2 F 2
3 M 1
2 M 2
3 F 1
2 F 2
--- 20 row(s) selected.
>>
>>
>>SELECT paygrade ,workgroupnum, sex, RANK(workgroupnum, sex) TD_RANK FROM TDemployee GROUP BY paygrade qualify paygrade> 1;
PAYGRADE WORKGROUPNUM SEX TD_RANK
---------- ------------ --- --------------------
2 3 F 1
2 2 F 2
2 1 F 3
3 3 M 1
3 2 M 2
3 1 M 3
4 3 F 1
4 2 F 2
4 1 F 3
5 3 M 1
5 2 M 2
5 1 M 3
6 3 F 1
6 2 F 2
6 1 F 3
7 3 M 1
7 2 M 2
7 1 M 3
8 3 F 1
8 2 F 2
8 1 F 3
9 3 M 1
9 2 M 2
9 1 M 3
10 3 F 1
10 2 F 2
10 1 F 3
--- 27 row(s) selected.
>>
>>
>>SELECT workgroupnum, sex, RANK(workgroupnum, sex) TD_RANK FROM TDemployee GROUP BY paygrade qualify rank(workgroupnum, sex)<200 and paygrade >1;
WORKGROUPNUM SEX TD_RANK
------------ --- --------------------
3 F 1
2 F 2
1 F 3
3 M 1
2 M 2
1 M 3
3 F 1
2 F 2
1 F 3
3 M 1
2 M 2
1 M 3
3 F 1
2 F 2
1 F 3
3 M 1
2 M 2
1 M 3
3 F 1
2 F 2
1 F 3
3 M 1
2 M 2
1 M 3
3 F 1
2 F 2
1 F 3
--- 27 row(s) selected.
>>
>>--TD rank in qualify clause but no TD Rank in select list
>>
>>SELECT paygrade, sex FROM TDemployee GROUP BY sex qualify RANK(paygrade)> 1;
PAYGRADE SEX
---------- ---
8 F
8 F
8 F
6 F
6 F
6 F
4 F
4 F
4 F
2 F
2 F
2 F
7 M
7 M
7 M
5 M
5 M
5 M
3 M
3 M
3 M
1 M
1 M
1 M
--- 24 row(s) selected.
>>
>>--TD RANK neither in qualify clause nor in select list
>>
>>SELECT paygrade, sex FROM TDemployee GROUP BY sex qualify paygrade> 1;
*** ERROR[4363] Using Qualify clause without using rank function in the query is not allowed.
*** ERROR[8822] The statement was not prepared.
>>
>>--qualify and devived table
>>
>>SELECT workgroupnum,paygrade, sum_annualsalary,Rank(sum_annualsalary) TD_RANK
+>FROM (SELECT workgroupnum,paygrade, SUM(annualsalary) sum_annualsalary,SUM(paygrade) as sum_paygrade FROM TDemployee GROUP BY workgroupnum,paygrade) T
+>GROUP BY workgroupnum
+>QUALIFY Rank(sum_annualsalary) >5
+>ORDER BY 3 desc ,1,2;
WORKGROUPNUM PAYGRADE SUM_ANNUALSALARY TD_RANK
------------ ---------- -------------------- --------------------
1 5 25000 6
2 5 25000 6
3 5 25000 6
1 4 24000 7
2 4 24000 7
3 4 24000 7
1 3 23000 8
2 3 23000 8
3 3 23000 8
1 2 22000 9
2 2 22000 9
3 2 22000 9
1 1 21000 10
2 1 21000 10
3 1 21000 10
--- 15 row(s) selected.
>>
>>
>>SELECT workgroupnum, sum_paygrade
+>FROM (SELECT workgroupnum, SUM(annualsalary) sum_annualsalary,SUM(paygrade) as sum_paygrade FROM TDemployee GROUP BY workgroupnum) T
+>GROUP BY workgroupnum
+>QUALIFY Rank(sum_paygrade) >2
+>ORDER BY 2 DESC, 1;
--- 0 row(s) selected.
>>
>>
>>---TD RANK , QUALIFY & WHERE
>>SELECT sex, CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee where num <> 1
+>GROUP BY sex
+>QUALIFY RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) >1;
SEX TRANS_COUNT_EXPR SEX TD_RANK
--- ---------------- --- --------------------
F y F 6
F y F 6
F y F 6
F y F 6
F y F 6
F z F 11
F z F 11
F z F 11
F z F 11
F z F 11
M y M 5
M y M 5
M y M 5
M y M 5
M y M 5
M z M 10
M z M 10
M z M 10
M z M 10
M z M 10
--- 20 row(s) selected.
>>
>>-- Multiple TD Ranks & equivalent expressions
>>SELECT sex, CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END trans_count_expr, sex,
+> RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END ASC, sex) TD_RANK
+>FROM TDemployee
+>GROUP BY sex
+>QUALIFY RANK(CASE workgroupnum WHEN 1 THEN 'x' WHEN 2 THEN 'y' ELSE 'z' END , sex) >1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, RANK(annualsalary) TD_RANK2 FROM TDemployee GROUP BY sex qualify rank(paygrade)> 1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT sex, paygrade, RANK(paygrade) TD_RANK2 FROM TDemployee GROUP BY sex qualify rank(paygrade)> 1;
SEX PAYGRADE TD_RANK2
--- ---------- --------------------
F 8 4
F 8 4
F 8 4
F 6 7
F 6 7
F 6 7
F 4 10
F 4 10
F 4 10
F 2 13
F 2 13
F 2 13
M 7 4
M 7 4
M 7 4
M 5 7
M 5 7
M 5 7
M 3 10
M 3 10
M 3 10
M 1 13
M 1 13
M 1 13
--- 24 row(s) selected.
>>
>>SELECT sex,annualsalary/paygrade as A, RANK(annualsalary/paygrade) TD_RANK2 FROM TDemployee GROUP BY sex qualify rank(annualsalary/paygrade)> 1;
SEX A TD_RANK2
--- ---------------------- --------------------
F 6000.0000000000 4
F 6000.0000000000 4
F 6000.0000000000 4
F 4333.3333333333 7
F 4333.3333333333 7
F 4333.3333333333 7
F 3500.0000000000 10
F 3500.0000000000 10
F 3500.0000000000 10
F 3000.0000000000 13
F 3000.0000000000 13
F 3000.0000000000 13
M 7666.6666666666 4
M 7666.6666666666 4
M 7666.6666666666 4
M 5000.0000000000 7
M 5000.0000000000 7
M 5000.0000000000 7
M 3857.1428571428 10
M 3857.1428571428 10
M 3857.1428571428 10
M 3222.2222222222 13
M 3222.2222222222 13
M 3222.2222222222 13
--- 24 row(s) selected.
>>
>>SELECT sex,annualsalary/cast(paygrade as DECIMAL(9,2)) as A, RANK(annualsalary/cast(paygrade as DECIMAL(9,2))) TD_RANK2 FROM TDemployee GROUP BY sex qualify rank(annualsalary/cast(paygrade as DECIMAL(9,2)))> 1;
SEX A TD_RANK2
--- --------------------- --------------------
F 6000.000000 4
F 6000.000000 4
F 6000.000000 4
F 4333.333333 7
F 4333.333333 7
F 4333.333333 7
F 3500.000000 10
F 3500.000000 10
F 3500.000000 10
F 3000.000000 13
F 3000.000000 13
F 3000.000000 13
M 7666.666666 4
M 7666.666666 4
M 7666.666666 4
M 5000.000000 7
M 5000.000000 7
M 5000.000000 7
M 3857.142857 10
M 3857.142857 10
M 3857.142857 10
M 3222.222222 13
M 3222.222222 13
M 3222.222222 13
--- 24 row(s) selected.
>>
>>SELECT paygrade, RANK(paygrade +10 ) TD_RANK2 FROM TDemployee GROUP BY sex qualify rank(paygrade +10)> 1;
PAYGRADE TD_RANK2
---------- --------------------
8 4
8 4
8 4
6 7
6 7
6 7
4 10
4 10
4 10
2 13
2 13
2 13
7 4
7 4
7 4
5 7
5 7
5 7
3 10
3 10
3 10
1 13
1 13
1 13
--- 24 row(s) selected.
>>
>>SELECT paygrade, RANK(paygrade +10 ) TD_RANK2 FROM TDemployee GROUP BY sex qualify rank(paygrade +11)> 1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK1 FROM TDemployee GROUP BY workgroupnum qualify rank(paygrade, sex)<200;
PAYGRADE SEX TD_RANK1
---------- --- --------------------
10 F 1
9 M 2
8 F 3
7 M 4
6 F 5
5 M 6
4 F 7
3 M 8
2 F 9
1 M 10
10 F 1
9 M 2
8 F 3
7 M 4
6 F 5
5 M 6
4 F 7
3 M 8
2 F 9
1 M 10
10 F 1
9 M 2
8 F 3
7 M 4
6 F 5
5 M 6
4 F 7
3 M 8
2 F 9
1 M 10
--- 30 row(s) selected.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK1 ,RANK(paygrade) TD_RANK2 FROM TDemployee GROUP BY workgroupnum qualify paygrade> 1 and rank(paygrade, sex)<200;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK FROM TDemployee GROUP BY workgroupnum qualify paygrade> 1 and rank(paygrade, sex asc)<200;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade, sex,RANK(paygrade, sex) TD_RANK FROM TDemployee GROUP BY sex,paygrade,workgroupnum qualify paygrade> 1 and rank(paygrade)<200;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>--TD rank & STRING Manipulation
>>SELECT substring(firstname, 1 ,4) sub_firstname, rank(substring(firstname, 1 ,4)) as TD_RANK
+>FROM TDemployee
+>GROUP BY dateofbirth
+>QUALIFY rank(substring(firstname, 1 ,4)) >1;
SUB_FIRSTNAME TD_RANK
------------- --------------------
Alvi 2
Anna 2
Akih 2
Abby 2
Alan 2
Amy 2
Abel 2
Adri 2
Anto 2
Alic 2
Abe 2
Aman 2
--- 12 row(s) selected.
>>
>>SELECT substring(firstname, 1 ,4) sub_firstname, rank(substring(firstname, 1 ,4)) as TD_RANK
+>FROM TDemployee
+>GROUP BY sex
+>QUALIFY rank(substring(firstname, 1 ,2)) >1;
*** ERROR[4361] All rank functions must use the same expression.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT UPPER(substring(firstname, 1 ,4)) up_sub_firstname , rank(UPPER(substring(firstname, 1 ,4))) as TD_RANK
+>FROM TDemployee
+>GROUP BY sex,paygrade
+>QUALIFY rank(UPPER(substring(firstname, 1 ,4))) >1;
UP_SUB_FIRSTNAME TD_RANK
---------------- --------------------
AMAN 3
ANNE 2
ABBY 3
ANNE 2
ANDR 3
AMY 2
ALIC 3
ALIC 2
ADRI 3
ALVI 2
ABE 3
ALOY 3
ALAN 2
AKIH 3
ARTH 2
ABEL 3
ARNO 2
ANTO 3
--- 18 row(s) selected.
>>
>>-----TD and GROUP BY -- TD rank and aggregate functions are not-- need to add an error message
>>
>>
>>SELECT * FROM (SELECT num, paygrade,RANK(paygrade) r_paygrade FROM TDemployee GROUP BY sex) t1 join (SELECT num, workgroupnum, RANK(workgroupnum) r_workgroupnum FROM TDemployee GROUP BY sex) t2 on t1.num=t2.num order by t1.num;
NUM PAYGRADE R_PAYGRADE NUM WORKGROUPNUM R_WORKGROUPNUM
---------- ---------- -------------------- ---------- ------------ --------------------
1 1 13 1 1 11
2 2 13 2 1 11
3 3 10 3 1 11
4 4 10 4 1 11
5 5 7 5 1 11
6 6 7 6 1 11
7 7 4 7 1 11
8 8 4 8 1 11
9 9 1 9 1 11
10 10 1 10 1 11
11 1 13 11 2 6
12 2 13 12 2 6
13 3 10 13 2 6
14 4 10 14 2 6
15 5 7 15 2 6
16 6 7 16 2 6
17 7 4 17 2 6
18 8 4 18 2 6
19 9 1 19 2 6
20 10 1 20 2 6
21 1 13 21 3 1
22 2 13 22 3 1
23 3 10 23 3 1
24 4 10 24 3 1
25 5 7 25 3 1
26 6 7 26 3 1
27 7 4 27 3 1
28 8 4 28 3 1
29 9 1 29 3 1
30 10 1 30 3 1
--- 30 row(s) selected.
>>
>>-------------------------------------------
>>--NESTING TD rank functions
>>
>>SELECT RANK(1-RANK(paygrade)) FROM TDemployee ;
*** ERROR[4368] Nesting rank functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(1-RANK(paygrade)) FROM TDemployee GROUP BY sex;
*** ERROR[4368] Nesting rank functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(1-RANK(paygrade)) FROM TDemployee QUALIFY RANK(1-RANK(paygrade)) >1;
*** ERROR[4368] Nesting rank functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT RANK(1-RANK(paygrade)) FROM TDemployee GROUP BY sex QUALIFY RANK(1-RANK(paygrade)) >1;
*** ERROR[4368] Nesting rank functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade FROM TDemployee QUALIFY RANK(1-RANK(paygrade)) >1;
*** ERROR[4368] Nesting rank functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>
>>SELECT paygrade FROM TDemployee GROUP BY sex QUALIFY RANK(1-RANK(paygrade)) >1;
*** ERROR[4368] Nesting rank functions is not supported.
*** ERROR[8822] The statement was not prepared.
>>-------------------------------------------
>>--testing rank with update/delete
>>
>>
>>create table TDemp_rank
+> (
+> Num integer unsigned no default not null not droppable ,
+> PayGrade integer unsigned no default not null not droppable ,
+> AnnualSalary integer unsigned no default not null not droppable ,
+> arank largeint no default not null not droppable ,
+> primary key ( Num ) not droppable
+> )
+> store by primary key;
--- SQL operation complete.
>>
>>INSERT INTO TDemp_rank SELECT Num, paygrade, AnnualSalary,rank(annualsalary) FROM TDemployee ;
--- 30 row(s) inserted.
>>
>>
>>UPDATE TDemp_rank SET arank = rank(annualsalary);
*** ERROR[15001] A syntax error occurred at or before:
UPDATE TDemp_rank SET arank = rank(annualsalary);
^ (48 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>
>>DELETE FROM TDemp_rank WHERE rank(annualsalary)=1;
*** ERROR[15001] A syntax error occurred at or before:
DELETE FROM TDemp_rank WHERE rank(annualsalary)=1;
^ (48 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>
>>------------------Qualify clause with exists and in predicates --- semi join
>>
>>select annualsalary,rank(annualsalary) from tdemployee
+>qualify exists (select num from tdemployee where num =1 ) and rank(annualsalary) in (1,7,10);
ANNUALSALARY (EXPR)
------------ --------------------
30000 1
30000 1
30000 1
28000 7
28000 7
28000 7
27000 10
27000 10
27000 10
--- 9 row(s) selected.
>>
>>select sex, annualsalary,rank(annualsalary) from tdemployee
+>group by sex
+>qualify exists (select num from tdemployee where num =1 ) and rank(annualsalary) in (1,7,10);
SEX ANNUALSALARY (EXPR)
--- ------------ --------------------
F 30000 1
F 30000 1
F 30000 1
F 26000 7
F 26000 7
F 26000 7
F 24000 10
F 24000 10
F 24000 10
M 29000 1
M 29000 1
M 29000 1
M 25000 7
M 25000 7
M 25000 7
M 23000 10
M 23000 10
M 23000 10
--- 18 row(s) selected.
>>
>>----------------------outer references and parameters
>>set param ?p 2;
>>select rank(annualsalary + ?p) from tdemployee;
*** ERROR[4369] Parameters and outer references are not supported with rank function.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>select num,( select rank(o.num) from tdemployee i where i.num=1)
+>from tdemployee o;
*** ERROR[4369] Parameters and outer references are not supported with rank function.
*** ERROR[8822] The statement was not prepared.
>>
>>-------------------NULL ORDERING
>>
>>
>>CREATE TABLE nulltest
+> ( vch7 varchar(7)
+> , nint integer not null
+> , nint2 integer
+> , ch3 char(3)
+> , nnum9 numeric(9,2)
+> , ch4 char(4)
+> , nnum5 numeric(5,1)
+> , vch5 varchar(5)
+> , nsint smallint
+> ) store by (nint);
--- SQL operation complete.
>>
>>
>>INSERT INTO nulltest VALUES ('a', 1, NULL, 'c' , 0.9, NULL , NULL , NULL, 0);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES ('a', 1, 2, 'c' , 0.9, '' , NULL , NULL, 0);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES ('cc' , 2, 5, 'cc', 2.00, 'cc', 2.0 , 'cc', 2);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES ('abcdefg', 3, NULL, 'cc', 0.09, 'alph', 2 , 'cc', 1);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES ('b', 4, 10, 'c' , 1234567.89, 'e' , 1234.5, 'c' , 12345);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES ('abcdefg', 5, 15, 'cc', 0.09, 'cc', 2 , 'cc', 2);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES (NULL, 5, NULL, 'cc', 0.09, 'cc', 2 , 'cc', 2);
--- 1 row(s) inserted.
>>INSERT INTO nulltest VALUES (NULL, 7, NULL, 'cd', 0.10, 'cd', 2 , 'cc', 2);
--- 1 row(s) inserted.
>>
>>
>>SELECT vch7, nint, nint2, rank (vch7, nint, nint2) r from nulltest;
VCH7 NINT NINT2 R
------- ----------- ----------- --------------------
cc 2 5 1
b 4 10 2
abcdefg 5 15 3
abcdefg 3 ? 4
a 1 2 5
a 1 ? 6
? 7 ? 7
? 5 ? 8
--- 8 row(s) selected.
>>
>>SELECT vch7, nint, nint2, rank (vch7 ASC, nint ASC, nint2 ASC) r from nulltest;
VCH7 NINT NINT2 R
------- ----------- ----------- --------------------
? 5 ? 1
? 7 ? 2
a 1 ? 3
a 1 2 4
abcdefg 3 ? 5
abcdefg 5 15 6
b 4 10 7
cc 2 5 8
--- 8 row(s) selected.
>>
>>SELECT nnum9, vch5, nsint, rank (nnum9, vch5, nsint) r from nulltest;
NNUM9 VCH5 NSINT R
------------ ----- ------ --------------------
1234567.89 c 12345 1
2.00 cc 2 2
.90 ? 0 3
.90 ? 0 3
.10 cc 2 5
.09 cc 2 6
.09 cc 2 6
.09 cc 1 8
--- 8 row(s) selected.
>>
>>SELECT nnum9, vch5, nsint, rank (nnum9 ASC, vch5 ASC, nsint ASC) r from nulltest;
NNUM9 VCH5 NSINT R
------------ ----- ------ --------------------
.09 cc 1 1
.09 cc 2 2
.09 cc 2 2
.10 cc 2 4
.90 ? 0 5
.90 ? 0 5
2.00 cc 2 7
1234567.89 c 12345 8
--- 8 row(s) selected.
>>
>>SELECT rank (vch7, nint, nint2) r from nulltest;
R
--------------------
1
2
3
4
5
6
7
8
--- 8 row(s) selected.
>>
>>SELECT rank (nnum9 ) r from nulltest;
R
--------------------
1
2
3
3
5
6
6
6
--- 8 row(s) selected.
>>
>>SELECT rank (nnum5 ) r from nulltest;
R
--------------------
1
2
2
2
2
2
7
7
--- 8 row(s) selected.
>>
>>SELECT rank ( vch5) r from nulltest;
R
--------------------
1
1
1
1
1
6
7
7
--- 8 row(s) selected.
>>
>>SELECT rank ( vch5) r from nulltest QUALIFY rank ( vch5) >1;
R
--------------------
6
7
7
--- 3 row(s) selected.
>>
>>SELECT rank (vch7, nint, nint2) r from nulltest QUALIFY rank (vch7, nint, nint2) <10;
R
--------------------
1
2
3
4
5
6
7
8
--- 8 row(s) selected.
>>
>>
>>SELECT
+>CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END A,
+>RANK(CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END) r
+>FROM nulltest;
A R
----------- --------------------
2 1
? 2
? 2
? 2
? 2
? 2
? 2
? 2
--- 8 row(s) selected.
>>
>>
>>SELECT
+>CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END A,
+>RANK(CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END ASC) r
+>FROM nulltest;
A R
----------- --------------------
? 1
? 1
? 1
? 1
? 1
? 1
? 1
2 8
--- 8 row(s) selected.
>>
>>
>>SELECT nint+ nint2 a ,RANK(nint + nint2) r FROM nulltest;
A R
-------------------- --------------------
20 1
14 2
7 3
3 4
? 5
? 5
? 5
? 5
--- 8 row(s) selected.
>>
>>SELECT RANK(SUBSTRING(VCH7, 1,2)) r FROM nulltest;
R
--------------------
1
2
3
3
5
5
7
7
--- 8 row(s) selected.
>>
>>
>>--- nullordering with group by
>>
>>SELECT vch5,vch7, nint, nint2, rank (vch7 ASC, nint ASC, nint2 ASC) r from nulltest GROUP BY vch5;
VCH5 VCH7 NINT NINT2 R
----- ------- ----------- ----------- --------------------
? a 1 ? 1
? a 1 2 2
c b 4 10 1
cc ? 5 ? 1
cc ? 7 ? 2
cc abcdefg 3 ? 3
cc abcdefg 5 15 4
cc cc 2 5 5
--- 8 row(s) selected.
>>
>>SELECT vch7,nnum9, vch5, nsint, rank (nnum9, vch5, nsint) r from nulltest group by nint2,vch7;
VCH7 NNUM9 VCH5 NSINT R
------- ------------ ----- ------ --------------------
? .10 cc 2 1
? .09 cc 2 2
a .90 ? 0 1
abcdefg .09 cc 1 1
a .90 ? 0 1
cc 2.00 cc 2 1
b 1234567.89 c 12345 1
abcdefg .09 cc 2 1
--- 8 row(s) selected.
>>
>>SELECT vch7,nnum9, vch5, nsint, rank (nnum9 ASC, vch5 ASC, nsint ASC) r from nulltest group by vch7;
VCH7 NNUM9 VCH5 NSINT R
------- ------------ ----- ------ --------------------
? .09 cc 2 1
? .10 cc 2 2
a .90 ? 0 1
a .90 ? 0 1
abcdefg .09 cc 1 1
abcdefg .09 cc 2 2
b 1234567.89 c 12345 1
cc 2.00 cc 2 1
--- 8 row(s) selected.
>>
>>SELECT nnum5,rank (vch7, nint, nint2) r from nulltest group by nnum5;
NNUM5 R
------------ --------------------
? 1
? 2
2.0 1
2.0 2
2.0 3
2.0 4
2.0 5
1234.5 1
--- 8 row(s) selected.
>>
>>SELECT nnum5,rank (nnum9 ) r from nulltest group by nnum5;
NNUM5 R
------------ --------------------
? 1
? 1
2.0 1
2.0 2
2.0 3
2.0 3
2.0 3
1234.5 1
--- 8 row(s) selected.
>>
>>SELECT vch7,rank (nnum5 ) r from nulltest group by vch7;
VCH7 R
------- --------------------
? 1
? 1
a 1
a 1
abcdefg 1
abcdefg 1
b 1
cc 1
--- 8 row(s) selected.
>>
>>SELECT vch7,rank ( vch5) r from nulltest group by vch7;
VCH7 R
------- --------------------
? 1
? 1
a 1
a 1
abcdefg 1
abcdefg 1
b 1
cc 1
--- 8 row(s) selected.
>>
>>SELECT vch7,rank ( vch5) r from nulltest group by vch7 QUALIFY rank ( vch5) >1;
--- 0 row(s) selected.
>>
>>SELECT vch5,rank (vch7, nint, nint2) r from nulltest group by vch5 QUALIFY rank (vch7, nint, nint2) <10;
VCH5 R
----- --------------------
? 1
? 2
c 1
cc 1
cc 2
cc 3
cc 4
cc 5
--- 8 row(s) selected.
>>
>>
>>SELECT vch5,
+>CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END A,
+>RANK(CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END) r
+>FROM nulltest
+>group by vch5
+>QUALIFY RANK(CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END)<10;
VCH5 A R
----- ----------- --------------------
? 2 1
? ? 2
c ? 1
cc ? 1
cc ? 1
cc ? 1
cc ? 1
cc ? 1
--- 8 row(s) selected.
>>
>>
>>SELECT vch5,
+>CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END A,
+>RANK(CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END ASC) r
+>FROM nulltest
+>group by vch5
+>QUALIFY RANK(CASE WHEN nint=1 THEN nint2 WHEN nnum9 >= 1 THEN NULL WHEN VCH5 IS NULL THEN nsint ELSE NULL END ASC)<10;
VCH5 A R
----- ----------- --------------------
? ? 1
? 2 2
c ? 1
cc ? 1
cc ? 1
cc ? 1
cc ? 1
cc ? 1
--- 8 row(s) selected.
>>
>>
>>SELECT vch7,nint+ nint2 a ,RANK(nint + nint2) r FROM nulltest GROUP BY vch7;
VCH7 A R
------- -------------------- --------------------
? ? 1
? ? 1
a 3 1
a ? 2
abcdefg 20 1
abcdefg ? 2
b 14 1
cc 7 1
--- 8 row(s) selected.
>>
>>SELECT vch5,RANK(SUBSTRING(VCH7, 1,2)) r FROM nulltest GROUP BY vch5;
VCH5 R
----- --------------------
? 1
? 1
c 1
cc 1
cc 2
cc 2
cc 4
cc 4
--- 8 row(s) selected.
>>
>>--more than 2 items in group by list / group by with ordinals
>>
>>SELECT workgroupnum,sex,paygrade,annualsalary,RANK(workgroupnum,sex,paygrade,annualsalary) TD_RANK1
+>FROM TDemployee
+>GROUP BY middleinitial,lastname,firstname;
WORKGROUPNUM SEX PAYGRADE ANNUALSALARY TD_RANK1
------------ --- ---------- ------------ --------------------
1 F 4 24000 1
2 M 1 21000 1
1 M 7 27000 1
2 F 10 30000 1
2 M 5 25000 1
1 M 5 25000 1
1 F 8 28000 1
1 F 10 30000 1
1 M 3 23000 1
1 M 1 21000 1
2 F 2 22000 1
2 F 8 28000 1
2 F 6 26000 1
3 M 3 23000 1
2 M 3 23000 1
1 F 2 22000 1
3 F 2 22000 1
1 F 6 26000 1
2 F 4 24000 1
1 M 9 29000 1
2 M 9 29000 1
2 M 7 27000 1
3 M 1 21000 1
3 F 4 24000 1
3 F 8 28000 1
3 F 6 26000 1
3 M 5 25000 1
3 M 7 27000 1
3 F 10 30000 1
3 M 9 29000 1
--- 30 row(s) selected.
>>
>>
>>
>>SELECT middleinitial,lastname,sex,workgroupnum,RANK(annualsalary) TD_RANK1
+>FROM TDemployee
+>GROUP BY middleinitial,lastname,sex,workgroupnum;
MIDDLEINITIAL LASTNAME SEX WORKGROUPNUM TD_RANK1
------------- ------------------------ --- ------------ --------------------
A Anderson F 1 1
A Anderson F 1 2
A Anderson F 1 3
A Anderson F 1 4
A Anderson F 1 5
A Anderson F 2 1
A Anderson F 2 2
A Anderson F 2 3
A Anderson F 2 4
A Anderson F 2 5
A Anderson F 3 1
A Anderson F 3 2
A Anderson F 3 3
A Anderson F 3 4
A Anderson F 3 5
A Anderson M 1 1
A Anderson M 1 2
A Anderson M 1 3
A Anderson M 1 4
A Anderson M 1 5
A Anderson M 2 1
A Anderson M 2 2
A Anderson M 2 3
A Anderson M 2 4
A Anderson M 2 5
A Anderson M 3 1
A Anderson M 3 2
A Anderson M 3 3
A Anderson M 3 4
A Anderson M 3 5
--- 30 row(s) selected.
>>
>>
>>SELECT RANK(annualsalary) TD_RANK1
+>FROM TDemployee
+>GROUP BY 1,lastname,sex,workgroupnum;
*** ERROR[4364] Rank can be placed only in the select list or the qualify clause.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>
>>SELECT middleinitial,lastname,sex,workgroupnum,RANK(annualsalary) TD_RANK1
+>FROM TDemployee
+>GROUP BY 1,2,sex,workgroupnum;
MIDDLEINITIAL LASTNAME SEX WORKGROUPNUM TD_RANK1
------------- ------------------------ --- ------------ --------------------
A Anderson F 1 1
A Anderson F 1 2
A Anderson F 1 3
A Anderson F 1 4
A Anderson F 1 5
A Anderson F 2 1
A Anderson F 2 2
A Anderson F 2 3
A Anderson F 2 4
A Anderson F 2 5
A Anderson F 3 1
A Anderson F 3 2
A Anderson F 3 3
A Anderson F 3 4
A Anderson F 3 5
A Anderson M 1 1
A Anderson M 1 2
A Anderson M 1 3
A Anderson M 1 4
A Anderson M 1 5
A Anderson M 2 1
A Anderson M 2 2
A Anderson M 2 3
A Anderson M 2 4
A Anderson M 2 5
A Anderson M 3 1
A Anderson M 3 2
A Anderson M 3 3
A Anderson M 3 4
A Anderson M 3 5
--- 30 row(s) selected.
>>
>>
>>SELECT middleinitial,lastname,sex,workgroupnum,RANK(annualsalary) TD_RANK1
+>FROM TDemployee
+>GROUP BY 8,2,sex,workgroupnum;
*** ERROR[4366] Invalid Select list index.
*** ERROR[8822] The statement was not prepared.
>>
>>CONTROL QUERY DEFAULT COMP_BOOL_200 'OFF';
--- SQL operation complete.
>>
>>log ;