Union all within subquery-CodePudding

I need some assistance in getting the following solution.

I have a case when statement that when a value is a specific number, I need it to return a certain number of union all results. Query example below.

For example, if T0.Docnum = 1 I need it to return 3 rows of 9's.

Obviously when I run the query below I get the error that the subquery returns more than one result. I am out of ideas at this point.

Any ideas are appreciated.

I am using SSMS and SQL Server 2016.

Thanks

select 
    case 
        when cast(count(T0.Docnum) as nvarchar(max)) = 1 
           then (select '999' 
                 union all
                 select '999'
                 union all
                 select '999'
                 union all
                 select '999'
                 union all
                 select '999') 

         when cast(count(T0.docnum) as nvarchar(max)) = 2 
             then (select '999' 
                   union all
                   select '999'
                   union all
                   select '999'
                   union all
                   select '999')

         when cast(count(T0.docnum) as nvarchar(max)) = 3 
             then (select '999' 
                   union all
                   select '999'
                   union all
                   select '999')
     end

CodePudding user response：

A simple way to 'generate' additional rows is with CROSS APPLY

(AFTER the GROUP BY, which I do in a sub-query...)

SELECT
  *
FROM
(
  SELECT
    X,
    Y,
    COUNT(TO.docnum)   AS doc_count
  FROM
    something
  GROUP BY
    X,
    Y
)
  AS foo
CROSS APPLY
(
  SELECT '999' AS generated
  UNION ALL
  SELECT '999' WHERE doc_count IN (3,2,1)
  UNION ALL
  SELECT '999' WHERE doc_count IN (2,1)
  UNION ALL
  SELECT '999' WHERE doc_count IN (1)
)
  AS bar

Returns 1 row, always.
Returns a second row if doc_count is 1, 2 or 3. Returns a third row if doc_count is 1 or 2
Returns a fourth row if doc_count is 1

EDIT : reply to comment.

CROSS APPLY
(
  SELECT '999' AS generated
  UNION ALL
  SELECT '999' WHERE doc_count IN (5,3,2,1)
  UNION ALL
  SELECT '999' WHERE doc_count IN (5,2,1)
  UNION ALL
  SELECT '999' WHERE doc_count IN (5,1)
  UNION ALL
  SELECT '999' WHERE doc_count IN (5)
)

All five rows match the scenario where doc_count = 5, so it generates five rows.

But only four rows match the scenario where doc_count = 1, so that would generate four rows.

CodePudding user response：

Check this fiddle and see if it helps you. I have used a variable to simulate your condition. I wasn't exactly sure what your logic was for determining the number of rows.

I use a CTE to generate an arbitrary number of rows, then select 999 from it.

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=cbaf378f43139dc47c2bcc1f1190442d

DECLARE @docnum INT;
SET @docnum = 1;

WITH r AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n 1 FROM r WHERE n 1<= 
      CASE WHEN @docnum = 3 then 1
        when @docnum = 2 then 2
        when @docnum = 1 then 3 end
)
SELECT 999 FROM r;