Results 1 to 2 of 2
  1. #1
    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.

  2. #2
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •