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.

I do not see anything wrong...

But this syntax is supported only after IDS version 10 ....

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.

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;

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!