0

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.