Your additional calculated value:
(select count(t1.num) from T t1)
Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function
error
However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:
group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)
just results in an ORA-22818: subquery expressions not allowed here
error.
The only ways around this are to either convert your scalar subquery to an aggregate value like so:
max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)
or
(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)
or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:
select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
from (select sum(t3.num) sum from t t3) t2;
or precompute your scalar subquery so it can be used in the group by clause:
select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
from t t2
, (select count(*) cnt from t) t1
group by case t1.cnt when 0 then 1 else 0 end
You can use the first function with ANY
but you have to have a valid syntax as you found out (<expression> <**operator**> ANY ...
). So the following would work (the "trick" piece is to use unnest()
whild unfolds the array into rows):
SELECT true = ANY (select myfun(x) from unnest(ARRAY['test', 'world']) as x) ;
or the more compact:
SELECT true = ANY (select myfun(unnest(ARRAY['tt', 'foo']))) ;
Best Answer
1. Subquery expression
You can fix it with parentheses like @a_horse commented:
But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether
id
is unique and neither does Postgres (unless it looks up system catalogs). We have to rely on it and hope it won't break.We could add
LIMIT 1
to be sure, but then we probably should also addORDER BY
to get deterministic results ...2.
JOIN LATERAL
The modern syntax for this (Postgres 9.3+) would be a
LATERAL
join:Which is short syntax for:
Exactly the same result as above, except for one subtle but possibly important difference:
tableX
has no row fort.id = 1
, the function is never called and the query returns nothing (no row). If, on the other hand, a row is found withcustomerid IS NULL
, the function is called withNULL
input and we get whatever the function returns forNULL
.That's how it should be.
customerid
beingNULL
. The function is called withNULL
input either way.That's not how it should be.
The
LATERAL
query also does not break ifid
is not unique. And you can easily return additional columns fromtableX
if you want.