In the following SQL query, I want to display if factor count is greater than one select NULL else return factor.
SELECT
CASE
WHEN COUNT(factor) > 1
THEN NULL
ELSE factor
END
FROM
TABLE
WHERE
ColumnA = '1'
AND ColumnB = '2';
However, I get this error:
EDITED
It is working fine if we use it as follows, however, I need to use factor in else part and Null in IF part
SELECT
CASE
WHEN COUNT(factor) > 1
THEN 'A'
ELSE 'B'
END
FROM
TABLE
WHERE
ColumnA = '1'
AND ColumnB = '2';
CodePudding user response:
You can't use COUNT(factor) and factor in one expression, try an aggregation, mox(factor) or min(factor) should be the same...
SELECT
CASE WHEN COUNT(factor) > 1
THEN
NULL
ELSE
MAX(factor)
END
FROM TABLE
WHERE ColumnA = '1'
AND ColumnB = '2';
But the error message doesn't fit, it should be
ORA-00937: not a single-group group function