sum inside sa_rowgenerator?

[former_SQLA_member1694869]

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';

[Breck_Carter]

Please explain what you are trying to accomplish. It is unlikely that "sum inside sa_rowgenerator" is a solution, but who knows? Not me 🙂

[former_SQLA_member1694869]

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.

[former_SQLA_member1694869]

The result should be like this:

row_num
-------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

[VolkerBarth · Accepted solution]

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:

Subselect and subqueries supported for parameter expressions in functions and procedures.
Subselect and subqueries are supported for parameter expressions in functions and procedures.

[Breck_Carter · Accepted solution]

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)

[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)