If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Informix > Informix Database Problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 2
Informix Database Problem

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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 2
The error in Informix is

ERROR: A syntax error has occurred.
Error Code: -201
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2012
Posts: 2
This question was also asked on Stack Overflow as sql - Informix Database Issue - Stack Overflow.

It was also answered there.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Location: Pont l'Abbé, Brittany, France
Posts: 336
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On