Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> group function not allowed in where clause?
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 MarcReceived on Fri Jun 18 2004 - 09:14:10 CDT