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

Question

I've serched google and stackoverflow before posting this. There are many post on this case, but I can't find the problem in my query.

    DECLARE @hipero_a int
SET @hipero_a = 1

SELECT
CASE @hipero_a 
WHEN 1 THEN --month
    (
    SELECT Year(o.AccomplishDate) AS [Rok], Month(o.AccomplishDate) AS [Miesiąc], SUM(oi.Price) AS [Przychód]
    FROM Orders o JOIN OrdersItems oi ON oi.OrderId = o.Id
    WHERE o.State = 0
    GROUP BY Year(o.AccomplishDate), Month(o.AccomplishDate)
    --ORDER BY Year(o.AccomplishDate), Month(o.AccomplishDate) ASC
)
WHEN 2 THEN --year
    (
    SELECT Year(o.AccomplishDate) AS [Rok], SUM(oi.Price) AS [Przychód]
    FROM Orders o JOIN OrdersItems oi ON oi.OrderId = o.Id
    WHERE o.State = 0
    GROUP BY Year(o.AccomplishDate)
    --ORDER BY Year(o.AccomplishDate) ASC
    )
    END

I get errors:

Msg 116, Level 16, State 1, Line 14 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Msg 116, Level 16, State 1, Line 22 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What is wrong?

Exactly what it says from where I am standing, you have too many columns selected in the 'SELECT' statement. — ChrisBint, Jun 28 '11 at 21:41
possible duplicate of [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.](http://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — Chris J, Jun 28 '11 at 21:46
and there's lots of other questions on the same theme already covering this message here on StackOverflow — Chris J, Jun 28 '11 at 21:46
Does this answer your question? [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS](https://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — Chris Latta, Feb 09 '20 at 23:55

score 2 · Answer 1 · answered Jun 28 '11 at 21:52

YOu want to use IF instead of CASE

DECLARE @hipero_a int
SET @hipero_a = 1

IF @hipero_a = 1 BEGIN ;
    SELECT Year(o.AccomplishDate) AS [Rok], Month(o.AccomplishDate) AS [Miesiąc], SUM(oi.Price) AS [Przychód]
    FROM Orders o JOIN OrdersItems oi ON oi.OrderId = o.Id
    WHERE o.State = 0
    GROUP BY Year(o.AccomplishDate), Month(o.AccomplishDate)
    --ORDER BY Year(o.AccomplishDate), Month(o.AccomplishDate) ASC
END ;
IF @hipero_a = 2 BEGIN ;
    SELECT Year(o.AccomplishDate) AS [Rok], SUM(oi.Price) AS [Przychód]
    FROM Orders o JOIN OrdersItems oi ON oi.OrderId = o.Id
    WHERE o.State = 0
    GROUP BY Year(o.AccomplishDate)
    --ORDER BY Year(o.AccomplishDate) ASC
    )
END;

score 2 · Answer 2 · answered Jun 28 '11 at 23:44

AlexKuznetsov has the correct answer. The problem is that the outside select is expecting a list of columns that can be returned. The Case statement is ONE of those columns, so the subselect must return at most ONE column to be passed up to the parent select as the column value.

Hopefully this will help explain why you're seeing the error you were getting.

Steve G.

score 0 · Answer 3 · answered Jun 29 '11 at 06:32

Thanks! I was just to tired to find this. So this works:

DECLARE @DivideBy int
SET @DivideBy = 1

IF @DivideBy = 1 BEGIN ;
    SELECT Year(o.AccomplishDate) AS [Rok], Month(o.AccomplishDate) AS [Miesiąc], SUM(oi.Price) AS [Przychód]
    FROM Orders o JOIN OrdersItems oi ON oi.OrderId = o.Id
    WHERE o.State = 0
    GROUP BY Year(o.AccomplishDate), Month(o.AccomplishDate)
    ORDER BY Year(o.AccomplishDate) DESC, Month(o.AccomplishDate) DESC
END ;
IF @DivideBy = 2 BEGIN ;
    SELECT Year(o.AccomplishDate) AS [Rok], SUM(oi.Price) AS [Przychód]
    FROM Orders o JOIN OrdersItems oi ON oi.OrderId = o.Id
    WHERE o.State = 0
    GROUP BY Year(o.AccomplishDate)
    ORDER BY Year(o.AccomplishDate) DESC
END;

May I optimize this somehow?

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

3 Answers3