CODEWITHSUNDEEP
sqldatabaseinformix
Ayush Kumar
Ayush Kumar

Reputation: 41

Informix Database Issue

I am using informix and I am unable to use a CASE statement inside an aggregate function. It always throws an error (-201: A syntax error has occurred). The following is the query I am using.

select nvl(count(case when (ccd.contacttype =1 and ccd.contactdisposition =2) then (ccd.sessionid) else 0 end),'ELSED (att)') as calls 
from contactcalldetail ccd 
inner join agentconnectiondetail acd on ccd.sessionid=acd.sessionid

Can you explain what's wrong?

Upvotes: 1

Views: 601

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 754860

Informix 11.70 allows CASE inside an aggregate

The following SQL works for me in Informix 11.70.FC6 on Mac OS X 10.7.5 (also 11.70.FC4 on RHEL 5):

CREATE TEMP TABLE contactcalldetail
(
    contacttype        INTEGER NOT NULL,
    contactdisposition INTEGER NOT NULL,
    sessionid          INTEGER NOT NULL
);
CREATE TEMP TABLE agentconnectiondetail
(
    sessionid INTEGER NOT NULL
);

SELECT NVL(COUNT(CASE
                 WHEN (ccd.contacttype = 1 AND ccd.contactdisposition = 2)
                 THEN (ccd.sessionid)
                 ELSE 0 END),
           'ELSED (att)') AS calls 
  FROM contactcalldetail ccd 
  JOIN agentconnectiondetail acd ON ccd.sessionid = acd.sessionid;

Informix 11.50 or older does not

Since it generates a -201 syntax error for you, we can infer that you are using an older version of Informix. It probably means you need to upgrade to a newer version of Informix. (Based on the information from Copilot's comments, it appears the Informix 11.50 did not support the notation; only 11.70 does.)

If you are using Informix 11.70, you need to document exactly which version you are using, and the platform on which you are running it. If it is 11.70.xC[4-7], then we may have a bug to chase; if it is earlier, then the support may have been added since the version you are using was released. Studying the release notes might help understand this. I've not checked when it was first available.

Does COUNT ever return NULL?

I observe that I don't think there are any circumstances when COUNT returns NULL. Certainly, with the empty tables, the output of the following variant of the query above returns zeros for all the values. Consequently, I think the NVL function call is unnecessary.

SELECT NVL(COUNT(CASE
                 WHEN (ccd.contacttype = 1 AND ccd.contactdisposition = 2)
                 THEN (ccd.sessionid)
                 ELSE 0 END),
           'ELSED (att)') AS calls,
       COUNT(*) AS count1,
       COUNT(CASE
             WHEN (ccd.contacttype = 1 AND ccd.contactdisposition = 2)
             THEN (ccd.sessionid)
             ELSE 0 END) AS count2
  FROM contactcalldetail ccd 
  JOIN agentconnectiondetail acd ON ccd.sessionid = acd.sessionid;

Upvotes: 1

Copilot
Copilot

Reputation: 782

Seems like an IDS bug to me. I tried similar queries (a case nested in a count function) and this always gives a syntax error on the 'when' keyword. However, according to the official documentation, this should work.

Upvotes: 0

Related Questions