Re: How to use result column names in having cause - Mailing list pgsql-general

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)