Trying to add another field for SSRS

ssrs-2016

I have a union all that combines two statements for an SSRS report. I would like to create a third column that is based on a where clause from the table as the third field (col1, and col2 being the first two fields). Essentially I'm wanting this:

DECLARE @Parameter int = 0

SELECT col1,col2, 'here I would like to use the result of another select col3 WHERE some_condition FROM table 1' 
FROM table 1
WHERE @Parameter int = 0

UNION ALL

SELECT col1,col2, 'same as above' 
FROM table 1
WHERE @Parameter int = 1

Best Answer

You could try subqueries

However, a subquery that returns more than one value will fail

This works but you have to consider that it can only return one value, and it could break easily

DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)

INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(3,4,5,1)


DECLARE @Parameter int = 0

SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter  = 0

UNION ALL

SELECT col1,col2, (select col3  FROM @table WHERE some_condition = 0 ) as col3 
FROM @table
WHERE @Parameter  = 1

Result:

col1    col2    col3
1       2   5
3       4   5

5 will be returned for each col1 and col2 value, because parameter = 0.

This fails

DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)

INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(2,3,4,0),(3,4,5,1),(6,7,8,1)


DECLARE @Parameter int = 0

SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter  = 0

UNION ALL

SELECT col1,col2, (select col3  FROM @table WHERE some_condition = 0 ) as col3 
FROM @table
WHERE @Parameter  = 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Related Question