Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    21

    Unanswered: Informix SQL - Creating subqueries in the SELECT statment

    I'm having issues creating a subquery in Informix. I'm using the Informix 3.33 driver.

    I'm trying to combine this query, which works perfectly fine by itself...

    SELECT sl_store,
    COUNT(sl_store) AS Total
    FROM informix.sales
    GROUP BY sl_store

    ...with this query, which also works perfectly fine by itself...

    SELECT sl_store, sl_inv_num,
    COUNT(sl_inv_num) AS NumOccurrences
    FROM informix.sales
    WHERE sl_date between '06-20-2007' AND '07/31/2007'
    GROUP BY sl_store, sl_inv_num
    HAVING ( COUNT(sl_inv_num) >= 1 )

    ...to give me this query, which is yielding a -201 error, which either means there is extra or missing punctuation, or that something is mispelled.

    SELECT sl_store,
    COUNT(sl_store) AS Total
    FROM (SELECT sl_store, sl_inv_num,
    COUNT(sl_inv_num) AS NumOccurrences
    FROM informix.sales
    WHERE sl_date between '06-20-2007' AND '07/31/2007'
    GROUP BY sl_store, sl_inv_num
    HAVING ( COUNT(sl_inv_num) >= 1 ))
    GROUP BY sl_store

    I'm not seeing the issue. Hopefully someone else can. Please let me know if I wasn't clear enough. What I'm trying to do makes sense in my head, but may not make sense with someone else.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    I do not see anything wrong...

    But this syntax is supported only after IDS version 10 ....
    Last edited by ceinma; 05-15-08 at 17:11.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    May 2008
    Posts
    21
    Thanks ceinma!

    Informix is new for me. I've used MS SQL Server platforms for a long time. I believe we're using IDS version 9.3. I just downloaded the SQL Syntax Guide for this version. I'll play around with it.

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    to work around.. try this..

    SELECT sl_store, sl_inv_num,
    COUNT(sl_inv_num) AS NumOccurrences
    FROM informix.sales
    WHERE sl_date between '06-20-2007' AND '07/31/2007'
    GROUP BY sl_store, sl_inv_num
    HAVING ( COUNT(sl_inv_num) >= 1 )
    INTO TEMP TP_SALES WITH NO LOG;

    SELECT sl_store,
    COUNT(sl_store) AS Total
    FROM (TP_SALES)
    GROUP BY sl_store;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    May 2008
    Posts
    21
    I just had to remove the () from TP_SALES in the second SELECT query and it worked like a charm.

    César...thanks for your help!

Posting Permissions

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