Here is my problematic query which runs OK in other DBMS.
Only way to run this in Postgres is to duplicate reatasum expression two
times in HAVING clause, right ?
Andrus.
SELECT
'z' as doktyyp,
r1.dokumnr,
r1.kuluobjekt as objekt,
r1.rid2obj,
r1.rid3obj,
r1.rid4obj,
r1.rid5obj,
r1.rid6obj,
r1.rid7obj,
r1.rid8obj,
r1.rid9obj,
dok.tasumata,
dok.raha,
CASE WHEN ( sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)>=
avg(r1.reasumma) AND avg(r1.reasumma)>=0) OR
( sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)<
avg(r1.reasumma) AND avg(r1.reasumma)<0)
THEN
avg(r1.reasumma)
ELSE
sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)
END as reatasum
FROM dok JOIN reakoond r1 USING (dokumnr)
JOIN reakoond r2 USING (dokumnr)
where
( r1.kuluobjekt::VARCHAR(10)||r1.rid2obj::VARCHAR(10)||
r1.rid3obj::VARCHAR(10)||r1.rid4obj::VARCHAR(10)||
r1.rid5obj::VARCHAR(10)||
r1.rid6obj::VARCHAR(10)||r1.rid7obj::VARCHAR(10)||
r1.rid8obj::VARCHAR(10)||r1.rid9obj::VARCHAR(10))>=
( r2.kuluobjekt::VARCHAR(10)||r2.rid2obj::VARCHAR(10)||
r2.rid3obj::VARCHAR(10)||r2.rid4obj::VARCHAR(10)||
r2.rid5obj::VARCHAR(10)||
r2.rid6obj::VARCHAR(10)||r2.rid7obj::VARCHAR(10)||
r2.rid8obj::VARCHAR(10)||r2.rid9obj::VARCHAR(10) )
group by 1,2,3,4,5,6,7,8,9,10,11,12,13
having (reatasum>0 AND avg(r1.reasumma)>=0) OR
(reatasum<0 AND avg(r1.reasumma)<0)
