ORDER BY in a stored procedure

Volker · #1 (**permalink**) 08-11-04, 11:35

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?

fprose · #2 (**permalink**) 08-11-04, 17:43

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.

Volker · #3 (**permalink**) 08-18-04, 06:17

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;

gurey · #4 (**permalink**) 08-18-04, 11:49

Hi,

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

Gustavo.

Internet Services

Web Development

Digital Marketing

Consumer Tech