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';
Advertisement
Answer
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