Skip site navigation (1) Skip section navigation (2)

Re: How to use result column names in having cause

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to use result column names in having cause
Date: 2006-03-31 13:14:18
Message-ID: e0j9vh$1uoc$1@news.hub.org (view raw or flat)
Thread:
Lists: 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)




In response to

pgsql-general by date

Next:From: chris smithDate: 2006-03-31 13:30:18
Subject: Re: How to use result column names in having cause
Previous:From: AndrusDate: 2006-03-31 13:05:53
Subject: Re: How to use result column names in having cause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group