RE: Another SQL-newbie UDF question: qualifying with a library passed as a variable

Don't you just hate it when you press <send> and then realize you wanted to
say something else!?


Using the SET PATH statement probably changes the path for all subsequent
accesses, so using EXECUTE IMMEDIATE is probably the safer way to go.

Have fun!

Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Casey
Sent: Friday, April 29, 2011 1:36 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Another SQL-newbie UDF question: qualifying with a library
passed as a variable

James,

You can't include a parameter variable as the library in your SELECT
statement, but you can construct a SELECT statement and store it in a
variable and then use the EXECUTE IMMEDIATE statement to run it.

For example:

SELECT TBLID INTO TBL from FOO where dfid = i_fldid;

becomes

declare select1 char(250);
SET select1 = 'SELECT TBLID INTO TBL from ' || i_lib || '/FOO where
dfid = ' || i_fldid;
EXECUTE IMMEDIATE select1;

I'm not where I can test this out, but it should work.


Another possibility would be to use the SET PATH statement. As in:

SET PATH = i_lib;


Hope this helps!

Richard

-----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.