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 4
Near character position 42
Database closed.
Seb
------------------------------
Sebastien FLAESCH
------------------------------
Original Message:
Sent: Thu June 06, 2024 01:08 PM
From: Art Kagel
Subject: LIMIT clause not allows in WITH ... (Common Table Expressions) ?
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
Original Message:
Sent: Thu June 06, 2024 11:37 AM
From: Sebastien FLAESCH
Subject: LIMIT clause not allows in WITH ... (Common Table Expressions) ?
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 DESC LIMIT 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
------------------------------