Re: SQL UDF help

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.