CREATE FUNCTION() containing ORDER BY clause
User couldn't create a function with SELECT INTO and ORDER BY clause. The issue occurred because ORDER BY can return multiple rows, incompatible with INTO single-row assignment. Solutions provided: use a subquery with MAX() to find the latest record, or SELECT multiple columns including the sort column into variables to satisfy the parser.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hey guys,
Ive been going out of my brain trying to figure out what the issue is with the
following CREATE FUNCTION() statment:
CREATE FUNCTION get_reason(x SMALLINT) RETURNING SMALLINT;
DEFINE reason_code SMALLINT;
SELECT FIRST 1 reason_fk INTO reason_code
FROM mytablename
WHERE record_fk = x
ORDER BY record_datetime DESC;
RETURN reason_code;
END FUNCTION;
If I remove the line 'ORDER BY act_datetime DESC', then the function creates
successfully. Can anyone suggest an alternative? Because the data could
potentially have multiple records, I really need to be able to retrieve the
last entry.
Probably not the greatest answer, but a sub-select might work:
Select reason_fk into reason_code from mytablename where record_fk = x and
record_datetime = (select max(record_datetime) from mytablename where
record_fx = x)
-Justin
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DANIEL
KIRTON
Sent: Tuesday, August 13, 2013 4:32 PM
To: ids@iiug.org
Subject: CREATE FUNCTION() containing ORDER BY clause [31187]
Hey guys,
Ive been going out of my brain trying to figure out what the issue is with the
following CREATE FUNCTION() statment:
CREATE FUNCTION get_reason(x SMALLINT) RETURNING SMALLINT;
DEFINE reason_code SMALLINT;
SELECT FIRST 1 reason_fk INTO reason_code
FROM mytablename
WHERE record_fk = x
ORDER BY record_datetime DESC;
RETURN reason_code;
END FUNCTION;
If I remove the line 'ORDER BY act_datetime DESC', then the function creates
successfully. Can anyone suggest an alternative? Because the data could
potentially have multiple records, I really need to be able to retrieve the
last entry.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Very wild guess... couldn't find documentation to prove it:
If you use "ORDER BY", usually we'll get more than one row... and that
can't be used with "INTO".
Try to use a FOREACH.
Regards
On Wed, Aug 14, 2013 at 12:31 AM, DANIEL KIRTON <phpking@gmail.com> wrote:
> Hey guys,
>
> Ive been going out of my brain trying to figure out what the issue is with
> the
> following CREATE FUNCTION() statment:
>
> CREATE FUNCTION get_reason(x SMALLINT) RETURNING SMALLINT;>
> DEFINE reason_code SMALLINT;
>
> SELECT FIRST 1 reason_fk INTO reason_code>
> FROM mytablename
>
> WHERE record_fk = x
>
> ORDER BY record_datetime DESC;
>
> RETURN reason_code;
> END FUNCTION;
>
> If I remove the line 'ORDER BY act_datetime DESC', then the function
> creates
> successfully. Can anyone suggest an alternative? Because the data could
> potentially have multiple records, I really need to be able to retrieve the
> last entry.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--047d7b3a8a8e01d53604e3dd2ad3
absolute gold guys! I ended up implementing justin's solution using the sub-query, and its working as expected. Thanks for all the help =)
Excellent point Fernando! It seemed very obvious as I read your response. Thank you for pointing that out.
Try this:
CREATE FUNCTION get_reason(x SMALLINT) RETURNING SMALLINT;DEFINE reason_code SMALLINT;
DEFINE rdt datetime;
SELECT FIRST 1 reason_fk, record_datetime
INTO reason_code, rdt
FROM mytablename
WHERE record_fk = x
ORDER BY record_datetime DESC;
RETURN reason_code;
END FUNCTION;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Aug 13, 2013 at 7:31 PM, DANIEL KIRTON <phpking@gmail.com> wrote:
> Hey guys,
>
> Ive been going out of my brain trying to figure out what the issue is with
> the
> following CREATE FUNCTION() statment:
>
> CREATE FUNCTION get_reason(x SMALLINT) RETURNING SMALLINT;>
> DEFINE reason_code SMALLINT;
>
> SELECT FIRST 1 reason_fk INTO reason_code>
> FROM mytablename
>
> WHERE record_fk = x
>
> ORDER BY record_datetime DESC;
>
> RETURN reason_code;
> END FUNCTION;
>
> If I remove the line 'ORDER BY act_datetime DESC', then the function
> creates
> successfully. Can anyone suggest an alternative? Because the data could
> potentially have multiple records, I really need to be able to retrieve the
> last entry.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3327a58d63d04e3e655c6