When generating Database Objects (Tables, Views, Indexes or SQL Routines)
the Naming Convention used in the connection or job is adopted.
In the green screen programs (STRSQL or RUNSQLSTM) the default naming
conventions is *SYS for the "colored" environments ACS or third party tools
*SQL Naming conventions are the default.
Normally you can change the naming conventions directly in the connection
(for example ACS - Connection: Edit --> JDBC Configurations --> Select the
configuration and click on Edit --> Naming Conventions can be found under
Format.
If you want to create database Objects with ACS - Schemas, naming
conventions can be set: Edit --> Preferences
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 18:51
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: 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
--
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
