Re: How to use result column names in having cause

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)