Create function displayval(i_fldid numeric, i_key varchar(10), i_lib varchar(10))
returns varchar(50)
language sql deterministic not fenced
begin
declare result char(50);
declare tbl char(11);
if i_key = ' ' then return ' '; end if;
sqlstmt = "select tblid from " concat trim(i_lib) concat "/FOO where dfid = ?";
prepare s1 for sqlstmt;
execute immediate into tbl using i_fldid;
sqlstmt = "select def from " concat trim(i_lib) concat "/BAR where ptble = substring(tbl,1,3) and pcode = substring(?, 1, 5) and stble = ' ' and scode = ' ';
prepare s2 for sqlstmt;
execute immediate into result using i_key;
return result;
end
this should do it.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James Lampert
Sent: Friday, April 29, 2011 12:46 PM
To: Midrange Systems Technical Discussion
Subject: Re: Another SQL-newbie UDF question: qualifying with a library passed as a variable
Dennis wrote:
Yes. See PREPARE and EXECUTE IMMEDIATE statements.
Hmm. I'm not entirely sure how (or even if) this would work for the
issue at hand:
What I have is along the lines (simplified and censored) of:
create function displayval(i_fldid NUMERIC,
i_key VARCHAR(10))
returns varchar(50)
language sql deterministic not fenced
begin
declare result char(50);
declare tbl char(11);
if i_key = ' ' then return ' '; end if;
SELECT TBLID INTO TBL from FOO where dfid = i_fldid;
select DEF INTO result from BAR
where PTBLE = substring(tbl from 1 for 3)
and PCODE = substring(i_key from 1 for 5)
and STBLE = ' ' and SCODE = ' ';
RETURN RESULT;
END
What I'd like to have would add a third parameter:
create function wintouch/displayval(i_fldid NUMERIC,
i_key VARCHAR(10) i_lib varchar(10))
and then qualify FOO and BAR with i_lib.
Or otherwise guarantee that FOO and BAR are both the ones in i_lib, and
not from some other library.
--
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.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
midrange.com
