Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

I am trying to write a case statement for my query which checks that if a person got two address on file then get the primary address else which ever one is on file. I have created a temp table for all persons address.

This is my code:

SELECT 
psn_int_id, 
CASE (SELECT COUNT(*) FROM #ADDRESS)
WHEN 2 THEN 
(SELECT * FROM #ADDRESS WHERE pri_fg = 'Y' )
--ELSE
--(SELECT * FROM #ADDRESS)
END AS TEST
FROM
#ADDRESS

Answer 1

Hi @Ali Ahad,

A minimal reproducible example was not provided.

Please try the following conceptual example.

-- DDL and sample data population, start
DECLARE @tbl TABLE (psn_int_id INT, _address VARCHAR(30), pri_fg  CHAR(1));
INSERT INTO @tbl (psn_int_id, _address, pri_fg) VALUES
(3948, 'Miami', 'N'),
(3948, 'Chicago', 'Y'),
(770, 'Chicago_18', 'N');
-- DDL and sample data population, end

;WITH rs AS
(
	SELECT * 
		, cnt = ROW_NUMBER() OVER (PARTITION BY psn_int_id ORDER BY pri_fg DESC)
	FROM @tbl
)
SELECT * FROM rs
WHERE cnt = 1;

Answer 2

Try an alternative: select top(1) * from #ADDRESS order by case pri_fg when 'Y' then 0 else 1 end.