|
How do I do this correctly? Want to use sum inside sa_rowgenerator. CREATE TABLE t ( mat NVARCHAR(20), quantity INTEGER NOT NULL ); INSERT t VALUES ( 'ABC', 10 ); INSERT t VALUES ( 'ABC', 5 ); INSERT t VALUES ( 'ABC21', 7 ); INSERT t VALUES ( 'ABC21', 13 ); COMMIT; SELECT NoOfRovs.row_num FROM t CROSS APPLY sa_rowgenerator(1, sum(t.quantity)) NoOfRovs WHERE mat = 'ABC'; |
|
Have no chance to test code currently but with v16 and above, it is allowed to use a subquery as argument for a procedure, so I guess it should also be possible to use the "SELECT SUM..." directly for parameter two, right? That would avoid the cool CROSS APPLY. FWIW, here's the according quote from the v16 What's New topic on System procedures and functions:
1
Indeed... it works in V17, which means you can put "sum inside sa_rowgenerator" :) SELECT row_num
FROM sa_rowgenerator (
1,
( SELECT SUM ( t.quantity )
FROM T
WHERE mat = 'ABC' ) );
row_num
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(15 rows)
(23 Mar '19, 13:48)
Breck Carter
|
|
The SUM has to be calculated in the SELECT FROM t and the result can then be passed to sa_rowgenerator via CROSS APPLY ( and congratulations on showing another cool use for CROSS APPLY :) CREATE TABLE t (
mat NVARCHAR(20),
quantity INTEGER NOT NULL );
INSERT t VALUES ( 'ABC', 10 );
INSERT t VALUES ( 'ABC', 5 );
INSERT t VALUES ( 'ABC21', 7 );
INSERT t VALUES ( 'ABC21', 13 );
COMMIT;
SELECT NoOfRovs.row_num
FROM ( SELECT SUM ( t.quantity ) AS sum_quantity
FROM T
WHERE mat = 'ABC'
) AS sum_t
CROSS APPLY sa_rowgenerator ( 1, sum_t.sum_quantity ) AS NoOfRovs;
row_num
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(15 rows)
|
Please explain what you are trying to accomplish. It is unlikely that "sum inside sa_rowgenerator" is a solution, but who knows? Not me :)
Excuse my faintness. In my example, I want to summarize quantity on 'ABC' and create as many rows (numbered) as what the sum becomes. In this case, there should be 15 rows counted from 1 to 15.
The result should be like this: