CODEWITHSUNDEEP
sqldatabaseinformix
Bharath
Bharath

Reputation: 195

Informix Error Creating Function : SQL error(-999) Not implemented yet

I'm getting the SQL Error(-999) Not implemented yet in the below query inside a function:

 SELECT 
     RES.resourcename,
     RES.resourceloginid,
     RES.extension,
     ASD.eventdatetime,
     ASD.eventtype,
     RANK() OVER (PARTITION BY RES.resourcename ORDER BY RES.resourcename,ASD.eventdatetime)
FROM agentstatedetail ASD JOIN resource RES ON ASD.agentid=RES.resourceid
WHERE ASD.eventdatetime BETWEEN to_date('18/04/2015 00:00:00', 
            "%d/%m/%Y %H:%M:%S")
     AND to_date('18/04/2015 23:59:59', "%d/%m/%Y %H:%M:%S")
     AND ASD.agentid IN(2620,2622)
     AND ASD.eventtype IN(1, 7);

When I replace the rank statement with an interger it works properly. Also when I execute the query with rank function as a separate query (Not inside the function), I'm getting the desired results. Have any idea why I'm getting this error on having this query inside the function ? Thanks alot... Note: I'm using Server Studio as client

Upvotes: 0

Views: 1862

Answers (2)

AndRAM
AndRAM

Reputation: 165

I'm responding for reference of future visitors as the specific issue is not correctly identified in previous comments and response.

You get that specific error code in Informix whenever your sub-query references an outer query column in its WHEN clause. In this particular instance is the ASD.eventdatetime reference which is triggering this error. Nothing to do with ranking.

Depending on circumstances you might work around this by using a JOIN instead. ON clauses do not show this restriction.

That is not to say that another, different, error would not be triggered by RANK in an older Informix release once that column reference is removed.

BTW, I happen to often fight with that awful database Cisco bestows upon us with CUCCX. Using to_date is gonna kill performance.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

Transferring comment to answer.

Which version of Informix are you using?

The OLAP functionality (such as RANK) was recently added, in 12.10 and maybe later versions of 11.70. If your version is older than that, it won't work and you'll need to upgrade.

If your version supports it, then you should probably contact IBM/Informix Tech Support to find out whether it's a known problem and/or fixed in a more recent fix-pack. And, if it isn't a known problem, your report may help them.

You should check the IDS 12.10 Knowledge Center too.

And a comment from Bharath confirms the 'version too old' hypothesis:

The Informix version was lower that 11.70 as you stated.

Upvotes: 1

Related Questions