LIMIT clause not allows in WITH ... (Common Table Expressions) ?
Sebastien FLAESCH found that on IDS 14.10.FC10 a LIMIT clause inside a WITH (CTE) query gave error 201, while FIRST 10 worked. Art Kagel advised opening a PMR and Sebastien opened a case. Andreas Legner noted the end-of-statement LIMIT clause is documented as invalid in subqueries and embedded SELECTs, which was likely the cause. No fix was confirmed.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello,
Is this a bug or expected limitation?
SQL statements should be self-explaining...
/* WORKS */
SELECT log_ordinal FROM dbsync_log WHERE log_table_name = 'stkitem' ORDER BY log_ordinal DESCLIMIT 10
;
/* FAILS */
WITH recent_rows AS (SELECT log_ordinal FROM dbsync_log WHERE log_table_name = 'stkitem' ORDER BY log_ordinal DESC
LIMIT 10
)
SELECT log_ordinal FROM dbsync_log WHERE log_ordinal IN (SELECT log_ordinal FROM recent_rows);
/* WORKS */
WITH recent_rows AS (SELECT
FIRST 10
log_ordinal FROM dbsync_log WHERE log_table_name = 'stkitem' ORDER BY log_ordinal DESC)
SELECT log_ordinal FROM dbsync_log WHERE log_ordinal IN (SELECT log_ordinal FROM recent_rows);
Seb
------------------------------
Sebastien FLAESCH
------------------------------
Sebastian: I would open a PMR. I cannot think of any reason why FIRST n works but LIMIT n does not. Was there a specific error code or just -201 Syntax Error! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Hello Art,
Using LIMIT clause in a CTE gives a syntax error (with IDS 14.10.FC10)
sf@toro (master):~/genero/devel/dbsync/dbsync$ cat x.sql
WITH recent_rows AS
(SELECT log_ordinal FROM dbsync_log
WHERE log_table_name = 'stkitem'
ORDER BY log_ordinal DESC LIMIT 10)
SELECT log_ordinal
FROM dbsync_log
WHERE log_ordinal IN (SELECT log_ordinal FROM recent_rows);
sf@toro (master):~/genero/devel/dbsync/dbsync$ dbaccess mystore x.sql
Database selected.
201: A syntax error has occurred.
Error in line 4Near character position 42
Database closed.
Seb
------------------------------
Sebastien FLAESCH
------------------------------
Case created: https://www.ibm.com/mysupport/s/case/500Ke000001EOntIAG/informix-ids-1410fc10-limit-clause-gives-syntax-error-in-with-cte?openCase=true Seb ------------------------------ Sebastien FLAESCH ------------------------------
Didn't even know about this end-of-statement LIMIT clause , as opposed to the "FIRST N"/"LIMIT N" projection clause options which, at least syntactically, seem to be different. And it has a couple of documented restrictions: The LIMIT clause is not valid in any of the following contexts: In the definition of a view In nested SELECT statements In subqueries, except for subqueries that specify table expressions in the FROM clause In a singleton SELECT (where max = 1) within an SPL routine Where embedded SELECT statements are used as expressions. I'd assume the last one is what you're hitting? ------------------------------ Andreas Legner ------------------------------