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
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 in esql/c and sqlca.sqlerrd in 4GL.