RE: SQL UDF to use *LIBL

Birgitta, Thank you for the fast response.
I read about and am aware of having to use *SYS naming standard.
But, how do I get it to do that?

I tried using "OPTION NAMING", but it is not allowed in a SQL Function.
I updated my example in the thread with the rest of the settings I have.

Thank you,
-Lee



date: Fri, 29 Sep 2023 05:42:48 +0200

from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx<mailto:Hauser@xxxxxxxxxxxxxxx>>

subject: RE: SQL UDF to use *LIBL



Libraries/Schemas are resolved at compile time (at least for static SQL).

Dealing with database objects in different schemas can only be done by using dynamic SQL, so the schema is resolved at runtime.

If you want to use the library list, the function must be created and run under system naming conventions ... otherwise you need to determine the schema first before constructing the dynamic SQL Statement.



Mit freundlichen Gr??en / Best regards



Birgitta Hauser

Modernization ? Education ? Consulting on IBM i

IBM Champion since 2020



"Shoot for the moon, even if you miss, you'll land among the stars." (Les

Brown)

"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson) "Learning is experience ? everything else is only information!" (Albert

Einstein)



From: Lee Paul
Sent: Thursday, September 28, 2023 9:47 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: SQL UDF to use *LIBL

I would like to be able to create a UDF with SQL that reads from table in different libraries and have it use *LIBL to resolve to those tables.

CREATE OR REPLACE FUNCTION "My_UDF"(
"p_File1Field2" CHAR(10))

RETURNS TABLE (
"Field1" VARCHAR(10)
,"Field2" VARCHAR(10)
,"Field3" VARCHAR(10)
,"Field4" VARCHAR(10)
,"Field5" VARCHAR(10)

LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT
SET OPTION SQLPATH = *LIBL
--SET OPTION NAMING = *SYS
-- Not allowed in a LANGUAGE SQL Function

BEGIN
SET PATH = *LIBL;
RETURN
SELECT "File1"."Field1"
, "File1"."Field2"
, "File1"."Field3"
, "File2"."Field1"
, "File2"."Field2"
FROM "File1"
LEFT JOIN "File2"
ON "File1"."Field1" = "File2"."Field1"
WHERE "File1"."Field2" = "p_File1Field2"
;
END;

File 2 is in a different Library than File 1 and is different per user. Is there a way to define this so that it uses *LIBL in the SELECT?
Currently it errors that it can't find the File2 in the File1 Library.

Any help would be greatly appreciated!
-Lee