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

asked 22 Mar, 15:43

Rolle's gravatar image

Rolle
492353852
accept rate: 0%

edited 22 Mar, 15:48

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

(23 Mar, 05:33) Breck Carter

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.

(23 Mar, 06:05) Rolle
Replies hidden

The result should be like this:

row_num
-------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(23 Mar, 07:15) Rolle

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.

permanent link

answered 23 Mar, 09:20

Volker%20Barth's gravatar image

Volker Barth
33.9k333487718
accept rate: 33%

edited 25 Mar, 03:48

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, 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)
permanent link

answered 23 Mar, 08:32

Breck%20Carter's gravatar image

Breck Carter
29.3k486649957
accept rate: 20%

edited 23 Mar, 08:35

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×96

question asked: 22 Mar, 15:43

question was seen: 172 times

last updated: 25 Mar, 03:48