Re: SQL UDF help

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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

You can do the same with the other statement....

When it comes to SQL...I have a saying, "If you're using a cursor,
you're probably doing something wrong."

Lastly, you're functions specify DETERMINISTIC; they should be NOT
DETERMINISTIC and you might want to add READS SQL DATA.  Though NOT
DETERMINISTIC and READS SQL DATA are the defaults, so you could simply
remove DETERMINISTIC.

By definition, any function/procedure using data from a table is NOT
DETERMINISTIC...the data in the table could be changed.

Charles




On Tue, Mar 29, 2011 at 4:50 PM, James Lampert <jamesl@xxxxxxxxxxxxxxxxx> wrote:
> Joe Pluta wrote:
> > Be sure to cast your variables correctly.  SQL default types are not
> > amenable to RPG variable types.  Show us the definition of your
> > DISPLAYVAL function.
>
> The DISPLAYVAL function is defined thusly:
> > create function displayval(i_fldid NUMERIC, i_key VARCHAR(5))
> > returns varchar(50)
> > language sql deterministic not fenced
> > begin
> >  declare result char(50);
> >  declare cur02 cursor for select DEF from WTTBLE
> >             where PTBLE = whichtbl(i_fldid)
> >   and PCODE = i_key and STBLE = '   ' and SCODE = '     ';
> >  fetch cur02 into result; close cur02; return result; end
>
> The "WHICHTBL" function it calls is defined thusly:
> > create function whichtbl(i_fldid NUMERIC)
> > returns varchar(3)
> > language sql deterministic not fenced
> > begin
> >  declare tbl char(11); declare tbl2 char(3);
> > declare cur01 cursor for select HDG from wtfldd where dfid = i_fldid
> > ; open cur01; fetch cur01 into tbl; close cur01; set tbl2 = tbl;
> > return tbl2; end
>
> Both compiled just fine.
>
> --
> 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.