I have a table
test1
view1
CREATE TABLE test1 (
A_ VARCHAR(10),
B_ VARCHAR(10),
C_ VARCHAR(10),
D_ VARCHAR(10)
);
CREATE VIEW view1 AS
SELECT
CASE
WHEN D_ in ('false') THEN LEFT(A_, CHARINDEX('(', A_) -1)
ELSE D_
END AS D,
MIN(B_) B,
CASE
WHEN C_ IS NOT NULL THEN '0'
ELSE C_
END AS C
FROM test1
GROUP BY
D_,
B_,
C_;
Column 'test1.A_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
A_
GROUP BY
GROUP BY
Since the _A
is used in the LEFT(A_, CHARINDEX('(', A_) -1)
you have to include it in the GROUP BY
. It does not matter that it is in the CASE
. The only situation that we do not have to add the attribute to GROUP BY
is when the attribute is part of an aggregate function.