Re: SQL UDF help

You can't return 3 fields from a scalar UDF...

- You could use CONCAT to combine them all into a single returned field
- You could use a UDTF, that returns one row with three fields
- You could use a stored procedure that had three output fields

In any of the above, the SELECT INTO can still be used

declare result1 char(50);
declare result4 char(50);
declare result3 char(50);
select DEF, HIJ, KLM into result1, result2, result3
from WTTBLE
where PTBLE = whichtbl(i_fldid)
and PCODE = i_key and STBLE = ' '
and SCODE = ' ';

You might decide to use a cursor for the UDTF option, though it's not required.

HTH,
Charles


On Wed, Apr 20, 2011 at 11:51 AM, James Lampert
<jamesl@xxxxxxxxxxxxxxxxx> wrote:

Charles Wilt wrote:

James,

You don't need a cursor for this...

 create function displayval(i_fldid NUMERIC, i_key VARCHAR(5))
 returns varchar(50)
 language sql deterministic not fenced
 begin
  declare result char(50);
  select DEF into result
    from WTTBLE
    where PTBLE = whichtbl(i_fldid)
      and PCODE = i_key and STBLE = '   '
      and SCODE = '     ';
  return result;
 end

Ok. But what if (different situation) I need three fields out of the record?

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.