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)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Lee
Paul
Sent: Friday, 29 September 2023 03:47
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
BEGIN
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
