As you may read here: http://dev.mysql.com/doc/maxdb/en/40/1311fd2fa511d3a98100a0c9449261/content.htm
you will see that a SELECT INTO (which can only have 1 result) does not accept an ORDER BY clause (because it does not make really sense to sort 1 row). Regards Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Matteo Gattoni [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 22, 2004 3:31 PM > To: [EMAIL PROTECTED] > Subject: ORDER BY in FUNCTION > > > HI, > I use a select statement in a function; everything goes if > the select statement doesn't contain an ORDER BY clause. > The error returned is always > Syntax error or access violation;-3014 POS(357) Invalid end > of SQL statement. > > If I create a Cursor using the same select Statement, it goes well. > > For example the following function gives that error. > > DROP FUNCTION GET_K_CITY > // > CREATE FUNCTION GET_K_CITY (NAME CHAR(50), CAP CHAR(10), PROV > CHAR(10)) RETURNS NUMBER AS > VAR RES FIXED(6,0); > TRY > IF CAP IS NULL AND PROV IS NULL THEN > BEGIN > TRY > SELECT K_CITY INTO :RES > FROM PSF.CITY > WHERE > CITY.N_CITY = :NAME > AND CITY.D_END IS NULL > ORDER BY K_CITY; > RETURN RES; > CATCH > IF $RC <> 100 THEN STOP($RC, $ERRMSG) > ELSE RETURN NULL; > END; > CATCH > STOP($RC, $ERRMSG); > // > GRANT EXECUTE ON PPORTAL.GET_K_CITY TO PSF > // > GRANT EXECUTE ON PPORTAL.GET_K_CITY TO PPORTAL_PERMISSION > // > > Couldn't I use an ORDER BY in a select statement within a > function? Only using a Cursor explicit definition too? > > Thanks in advance. > > Bye, > Matteo > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
