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

David, 
with this statement he is likely to be returning a UDTF (User Defined Table
Function) - result set, many values. 
His UDF declaration states that he really wants to return an integer (valid
for UDFs) - single value.

Raul's suggestion is a good solution.

Also, 'into foo' part seems out of place.

There is a very good redbook on this topic: "Stored Procedures, Triggers and
User Defined Functions on DB2 Universal Database for iSeries".

Elvis


-----Original Message-----
 Subject: Re: SQL function ... this one is for SQL gurus

 From your definition I assume the function should return an integer, if 
so try:

select min(inOperand)...

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