Sorry, operation not implemented yet (7484)

[mullah]

Hello everyone,

Need help from SQL gurus
Please look at following query:

select prh_site, prh_part, prh_rcp_date, sum(prh_rcvd)
from pub.prh_hist p1
where p1.prh_rcp_date > sysdate - 30
group by p1.prh_site, p1.prh_part, prh_rcp_date
having p1.prh_rcp_date = (select max(p2.prh_rcp_date) 
from pub.prh_hist p2
where p2.prh_site = p1.prh_site and
p2.prh_part = p1.prh_part 
group by p2.prh_site, p2.prh_part)

what I want to get here is obvious I think: for each combination of prh_site and prh_part I need sum of prh_rcvd
for maximum value of prh_rcp_date. When I try to run this I get error from the subject (Progress 9.1E).

I tried also this:

select p1.prh_site, p1.prh_part, p1.prh_rcp_date, sum(p1.prh_rcvd)
from pub.prh_hist p1
where p1.prh_rcp_date = (select max(p2.prh_rcp_date) from
      pub.prh_hist p2
      where p1.prh_site = p2.prh_site and
      p1.prh_part = p2.prh_part and
      p2.prh_rcp_date > sysdate - 30
      group by p2.prh_site, p2.prh_part)
group by p1.prh_site, p1.prh_part, p1.prh_rcp_date

This one works, but executing takes too long.

Any ideas, how can I rewrite it to get it working ?

Thanks in advance.

 

[TomBascom]

The operation is not yet implemented (in version 9).

9.1E is ancient, obsolete and (essentially) unsupported.

Service pack 4 was released 6 years ago. It was the last service pack that will ever be released for version 9. Anything that was not implemented by then will never be implemented in version 9.

OpenEdge 11 was just released a couple of months ago.

You should upgrade.

You might also try running UPDATE STATISTICS to improve the performance of the query which works but takes too long.

 

[Marian EDU]

you might try something like this, don't have an V9 at hand but I guess select from select should work... in V10 it does work, so you might consider the update path if needed.

select p2.prh_site, p2.prh_part, p2.prh_rcp_date, sum(p2.prh_rcvd)
   from 
      (select p1.prh_site, p1.prh_part, max(p1.prh_rcp_date) prh_rcp_date 
            from pub.prh_hist p1 
                where p1.prh_rcp_date > sysdate - 30
                group by p1.prh_site, p1.prh_part) s,
      pub.prh_hist p2
      where p2.prh_site = s.prh_site 
         and p2.prh_part = s.prh_part 
         and p2.prh_rcp_date = s.prh_rcp_date
      group by p2.prh_site, p2.prh_part, p2.prh_rcp_date

 

[mullah]

Hello

Marian: thank you, that works much better

Tom: I realize that 9.1E is ancient, and upgrade is already planned.
UPDATE STATISTICS I ran of course.

Thank you both for help !