Re: SQL query to get names of LFs pointing to PFs created in incorrect libraries

You might change the where as (SQL_Object_Type LIKE '%LOGICAL%' or
SQL_Object_Type LIKE '%VIEW%') and add to the main select the
field SQL_OBJECT_TYPE.

And feel free to support this idea :-)

https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-4358

Systools.Related_Objects is a nice function but it's very resource eating.

HTH

--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno sab 15 feb 2025 alle ore 11:59 Birgitta Hauser <
Hauser@xxxxxxxxxxxxxxx> ha scritto:

You may use a composition of the SYSTABLES Catalog-View (for selecting your
physical files or SQL Tables) and the SQL Service RELATED_OBJECTS within
the
SYSTOOLS Library.
The following examples will for all tables in YOURSCHEMA the dependent
logical files which are not in the same library as the physical
files/tables
(i.e. which are not in YOURSCHEMA).

Select Table_Schema as PFTable_Schema, Table_Name as PFTable,
Schema_Name as LFSchema, SQL_Name as LFSQLName, System_Name
as LFSYSName
from SysTables a cross join
Lateral(Select *
from Table(Systools.Related_Objects(
Library_Name => a.Table_Schema,
File_Name => a.Table_Name))
Where SQL_Object_Type like '%LOGICAL%'
and Schema_Name <> a.Table_Schema
)
Where a.Table_Schema = 'YOURSCHEMA'
and Table_Type in ('P', 'T');

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
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: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
pr20251925
via RPG400-L
Sent: Friday, 14 February 2025 15:21
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: pr20251925 <pr20251925@xxxxxxxxx>
Subject: SQL query to get names of LFs pointing to PFs created in incorrect
libraries

Hi,

How to extract all LFs which are created based on PFs(which are in
incorrect
libraries)

For example LF1 which is there in LIB1 and has been created based on PF1 in
Lib2( ideally should have been created in LIB1 only).

So Any SQL query by which detailss of all Such LFs could be extracted?

Thanks...
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.