Create function displayval(i_fldid numeric, i_key varchar(10), i_lib varchar(10))
returns varchar(50)
language sql deterministic not fenced
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;

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
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 = ' ';

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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives


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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.