Re: SQL function ... this one is for SQL gurus

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

It seems you already have the answer to your own question right there in
the post.
A "select into" does not take an "order by",
the message is right.


On Mon, 2005-08-08 at 16:06 -0500, David Gibbs wrote:
> I'm posting this for a co-worker ...
> 
> He's trying to create a SQL user defined function ...
> 
> -----
> create function historicIntOperand(issueID integer, atom integer,
>     historic timestamp)
>     returns integer
>     language SQL
>     specific historicIntOperand
>     reads sql data
> begin atomic
>     return (
>         select IntOperand into foo from IssueDeltas
>             join IssueDeltaAtoms on IssueDeltas.ID =
> IssueDeltaAtoms.ParentID
>             where (IssueDeltas.ParentID = issueID
>             and CreatedDate <= historic
>             and AtomID = atom)
>             order by CreatedDate DESC
>             fetch first 1 row only);
> end
> 
> -----
> 
> The problem is this ...
> 
> Return takes an expression.
> Well, an expression is allowed to be (scalar subselect),
> so we need () around the select.
> Then we get an error when it hits the keyword ORDER.
> A scalar subselect is a subselect.  It is not a select statement.
> A select statement is a fullselect which can be followed by an orderby;
> but a subselect may not have an order by!
> 
> The specific error we're getting is: [SQL0199] Keyword ORDER not
> expected. Valid tokens: ) UNION.
> 
> We're using V5R3.
> 
> Any suggestions?
> 
> 
> Thanks!
> 
> david