Joel Griffiths wrote :
>Can someone tell me what's wrong with this. I've been trying to see if I
>could port an application from MySQL, but it's quickly becoming too time
>intensive to implement a LIMIT function in SAPDB. This stored procedure
>attempts to, inefficiently, implement a LIMIT function, but I get the
>following error:
>---- Error -------------------------------
>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
>Syntax error or access violation;-3014 POS(80) Invalid end of SQL
>statement.
>call MSGHEADERLIMIT(' where MSGNO=2 ', 5, 10)
>Here is the stored procedure:
>CREATE DBPROC MSGHEADERLIMIT(
>IN WHERE_CLAUSE char(255),
>IN START_POS INTEGER,
>IN NUM_FIELDS INTEGER)
>RETURNS CURSOR AS
> VAR END_POS Integer;
> stmt varchar(1000);
> SET END_POS = NUM_FIELDS + START_POS;
> IF FALSE
> THEN CREATE TABLE TEMP.resulttable AS SELECT * FROM
>DBA.MSGHEADERS;
> SET stmt = 'CREATE TABLE TEMP.resulttable AS SELECT * FROM
>DBA.MSGHEADERS ' || WHERE_CLAUSE || ' ORDER BY INTERNALDATE ';
> execute stmt;
> stop($rc, $errmsg);
>
> /* Retrieve all rows up to the end position */
> DECLARE C4 CURSOR FOR SELECT * FROM TEMP.resulttable WHERE ROWNO<=
>:END_POS FOR REUSE;
>
> /* Turn it over so we can count back from the end */
> DECLARE C5 CURSOR FOR SELECT * FROM DBA.C4 ORDER BY INTERNALDATE DESC
>FOR REUSE;
>
> /* Select num_fields from the end */
> DECLARE :$cursor CURSOR FOR SELECT * FROM DBA.C5 WHERE
>ROWNO<=:NUM_FIELDS;
>
> drop table TEMP.resulttable;
> //
>call MSGHEADERLIMIT(' where MSGNO=2 ', 5, 10)
The error is returned by your create table statement, because
the <query expression> contains an <order by clause>, which is not allowed
(http://www.mysql.com/documentation/maxdb/0f/486fa22f9611d3a98100a0c9449261/frameset.htm)
Best Regards,
Thomas
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
