Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> group function not allowed in where clause?

group function not allowed in where clause?

From: Marc Eggenberger <marc.eggenberger_at__REMOVE_itc.alstom.com>
Date: Fri, 18 Jun 2004 16:14:10 +0200
Message-ID: <MPG.1b3d1719d1792ee198968c@10.128.18.49>


Hi there.

I try this on a Oracle 10g on Windows2000 Server:

select pl.probeid, max(pl.logdate) as lastcheck, (sysdate - max (pl.logdate)) * 24*60*60 as timespan, p.interval from tblprobeloghttp pl inner join tblprobehttp p on pl.PROBEID = p.PROBEID
group by pl.probeid, p.interval

this works perfectly, but I want only those where timespan is larger than interval, so I tried to add a

where timespan > p.interval

but this gives me an

ORA-00904 "TIMESPAN": invalid identifier

when I try:
where ((sysdate - max(pl.logdate))*24"60"60) > interval I get an:

ORA-00934: group function is not allowed here.

How can I accomplish this? Is it only possible with PL/SQL?

Thanks for any help

-- 
mfg
Marc
Received on Fri Jun 18 2004 - 09:14:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US