I am usin informix and i am unable to use case statement inside aggregate function. It always throws an error. 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

Plz help me out.

ERROR: A syntax error has occurred.
Error Code: -201

This question was also asked on Stack Overflow as sql - Informix Database Issue - Stack Overflow.

It was also answered there.

As Jonathan says, the CASE is not supported in "old" informix versions.

A little hint when you have a 201 with dbaccess, and you do not know where exactly is the syntax error:
run dbaccess databasename
type the query, preferrably with a syntax error
type 'R' / choose Run in the ring menu
get the syntax error
type 'u' / choose the 'use editor' option in the ring menu
choose vi or whatever editor your are familiar with
when entering the editor, you will see a '^' below the offset in the sql statement string where the parser
starts disagreeing with the syntax.

In programs, you have the same information in the sqlca.sqlerrd[2] in esql/c and sqlca.sqlerrd[3] in 4GL.

Cheers
E.