>>> Before decide what to do, I'd like to hear Vlad about the error
>>> isc_dsql_cte_wrong_clause, which, for example, happens if a recursive
>>> query has a GROUP BY.
>>>
>>> Why is it not allowed?
>> Because standard prohibits any kind of aggregates in recursive CTE
>> members.
>>
> Could you point me where is this described? I don't found it.
I can't find it right now, but links below could prove it.
DB2
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.sqlref/src/tpc/db2z_sql_commontableexpression.htm
Each fullselect that is part of the recursion cycle must not include any
aggregate functions, GROUP BY clauses, or HAVING clauses.
PG
http://wiki.postgresql.org/wiki/CTEReadme :
Next we check if the recursive query is following the SQL standard. Following
example queries are not allowed by the standard.
...
- GROUP BY, HAVING in a recursive term not allowed
-- aggregate functions a recursive term not allowed
MSSQL
http://msdn.microsoft.com/en-us/library/ms175972.aspx :
The following items are not allowed in the CTE_query_definition of a recursive
member:
SELECT DISTINCT
GROUP BY
HAVING
Scalar aggregation
Hope it helps,
Vlad
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
