Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unhappy Unanswered: ORDER BY in a stored procedure

    We are using Informix SE 7.25 UC1 on SuSE Linux.

    I have a stored procedure that looks like this:

    Code:
    CREATE PROCEDURE informix.devisenkurs (ciso CHAR(3), date DATE)
            RETURNING CHAR(3), DECIMAL(14, 4), DATE;
            DEFINE iso CHAR(3);
            DEFINE value DECIMAL(14, 4);
            DEFINE ddate DATE;
    		SELECT c.curiso, d.devvalue, d.devdate
    		INTO iso, value, ddate
    		FROM informix.currency c, informix.devisenkurs d
    		WHERE c.currencyid = d.currencyid AND d.devdate = date
    		AND c.curiso = ciso;
    		RETURN iso, value, ddate;
    END PROCEDURE;
    This works fine. Now I try the following:

    Code:
    CREATE PROCEDURE informix.devisenkurs (ciso CHAR(3), date DATE)
            RETURNING CHAR(3), DECIMAL(14, 4), DATE;
            DEFINE iso CHAR(3);
            DEFINE value DECIMAL(14, 4);
            DEFINE ddate DATE;
    		SELECT c.curiso, d.devvalue, d.devdate
    		INTO iso, value, ddate
    		FROM informix.currency c, informix.devisenkurs d
    		WHERE c.currencyid = d.currencyid AND d.devdate <= date
    		AND c.curiso = ciso 
    		ORDER BY d.devdate DESC;
    		RETURN iso, value, ddate;
    END PROCEDURE;
    Now I get a syntax error (-201). I've added an "ORDER BY" statement, but I can't get it to work. The idea is to get the last value in a set of values on that day or a previous day. It doesn't matter if I replace 'd.devdate' with '3'.

    I tried to enclose the SELECT in a FOREACH and a RETURN ... WITH RESUME. That does not work either.

    Do you have any idea what I'm doing wrong?
    Last edited by Volker; 08-11-04 at 12:43.

  2. #2
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Two issues here:

    1. Your SELECT will obviously try and return more than one row into the variables - and will abort. I think you need to define the select within a cursor and then break out of the cursor after your first time into the loop.

    2. I believe your use of "date" - a reserved word - is contributing to your syntax error.
    Fred Prose

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    Quote Originally Posted by fprose
    Two issues here:

    1. Your SELECT will obviously try and return more than one row into the variables - and will abort. I think you need to define the select within a cursor and then break out of the cursor after your first time into the loop.

    2. I believe your use of "date" - a reserved word - is contributing to your syntax error.
    Many thanks for your answer, I changed the statement so that it returns exactly one row and changed the name "date" to "tdate" - and I have the same problem, still.

    Actually, the solution is quite simple: the ";" at the end of the sql-statement was wrong.

    Here is the corrected statement:

    Code:
    CREATE PROCEDURE informix.devisenkurs (ciso CHAR(3), date DATE)
            RETURNING CHAR(3), DECIMAL(14, 4), DATE;
            DEFINE iso CHAR(3);
            DEFINE value DECIMAL(14, 4);
            DEFINE ddate DATE;
    		SELECT c.curiso, d.devvalue, d.devdate
    		INTO iso, value, ddate
    		FROM informix.currency c, informix.devisenkurs d
    		WHERE c.currencyid = d.currencyid AND d.devdate = date
    		AND c.curiso = ciso
    		RETURN iso, value, ddate;
    END PROCEDURE;

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    I think that you must create SELECT .... INTO TEMP WITH NO LOG, then
    SELECT .. ORDER BY.

    Gustavo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •