Informix

 View Only

  • 1.  LIMIT clause not allows in WITH ... (Common Table Expressions) ?

    Posted 26 days ago

    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
    ------------------------------


  • 2.  RE: LIMIT clause not allows in WITH ... (Common Table Expressions) ?

    IBM Champion
    Posted 26 days ago

    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


  • 3.  RE: LIMIT clause not allows in WITH ... (Common Table Expressions) ?

    Posted 25 days ago

    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


  • 4.  RE: LIMIT clause not allows in WITH ... (Common Table Expressions) ?

    Posted 25 days ago

    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
    ------------------------------

    Original Message


  • 5.  RE: LIMIT clause not allows in WITH ... (Common Table Expressions) ?

    IBM Champion
    Posted 25 days ago

    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
    ------------------------------

    Original Message