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 > New to informix - trouble with simple subquery

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 2
Red face New to informix - trouble with simple subquery

Hi,

I'm 2 days old in informix, and am having trouble with what should be a simple subquery. In oracle, mysql, sybase and sql server such queries are perfectly fine.

However, informix (via the odbc driver) tells me the there's something wrong with the syntax. Now, the inner query works just fine by itself, so what gives? (According to the IBM syntax guide, this should work unless I missed something?)

Note that I have tried adding an aliase after the closing parenthesis, but still get syntax error...


SELECT
SUM(incalls) AS total,
(SUM(ivr_calls) - (SUM(connected) + SUM(answered))) AS ivr_calls,
SUM(abandoned) AS abandoned,
(SUM(busycalls) + SUM(disccalls) + SUM(other)) AS other,
SUM(internal_xfer) AS internal_xfer
FROM
(
SELECT
1 AS incalls,
CASE disposition WHEN 1 THEN 1 ELSE 0 END AS connected,
CASE disposition WHEN 2 THEN 1 ELSE 0 END AS answered,
CASE disposition WHEN 3 THEN 1 ELSE 0 END AS abandoned,
CASE disposition WHEN 4 THEN 1 ELSE 0 END AS ivr_calls,
CASE disposition WHEN 5 THEN 1 ELSE 0 END AS busycalls,
CASE disposition WHEN 6 THEN 1 ELSE 0 END AS disccalls,
CASE disposition WHEN 7 THEN 1 ELSE 0 END AS other,
CASE firstvdn WHEN '3003' THEN 0 ELSE 1 END AS internal_xfer
FROM
call_rec
WHERE
row_date = '2/21/2013'
AND dialed_num = '3003'
) a

Last edited by jersey355; 02-22-13 at 12:32.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 2
Ok... I guess I must be going against a slightly older driver version. For any others going through similar issues, I modified my query as follows to make it work:

SELECT
SUM(incalls) AS total,
(SUM(ivr_calls) - (SUM(connected) + SUM(answered))) AS ivr_calls,
SUM(abandoned) AS abandoned,
(SUM(busycalls) + SUM(disccalls) + SUM(other)) AS other,
SUM(internal_xfer) AS internal_xfer
FROM TABLE (MULTISET(
SELECT
1 AS incalls,
CASE disposition WHEN 1 THEN 1 ELSE 0 END AS connected,
CASE disposition WHEN 2 THEN 1 ELSE 0 END AS answered,
CASE disposition WHEN 3 THEN 1 ELSE 0 END AS abandoned,
CASE disposition WHEN 4 THEN 1 ELSE 0 END AS ivr_calls,
CASE disposition WHEN 5 THEN 1 ELSE 0 END AS busycalls,
CASE disposition WHEN 6 THEN 1 ELSE 0 END AS disccalls,
CASE disposition WHEN 7 THEN 1 ELSE 0 END AS other,
CASE firstvdn WHEN '3003' THEN 0 ELSE 1 END AS internal_xfer
FROM
call_rec
WHERE
row_date = '2/21/2013'
AND dialed_num = '3003'
)) AS a
Reply With Quote
Reply

Tags
from clause, informix, subquery

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