2
« on: December 09, 2020, 01:53:26 PM »
Hi Folks,
EDIT: we are on iSeries, but this should not be special to iSeries (I assume?)
I am searching for a simple SQL to get an overview on a folder which results in:
Folder xy => having application groups => having only the currently selected applications
Scenario: each client has a folder assigned (based on groups/users) to search over "their" application groups, each AppGrp with the same datamodel and the same number of applications (let's say up to 5 apps: OrderType10000 to OrderType50000)
So a list could be like (1st line is the header)
Folder name;Application group;AppFromAppGrp
FolderOrd00;AppGrp_Client100;OrderType10000
FolderOrd00;AppGrp_Client100;OrderType20000
FolderOrd00;AppGrp_Client100;OrderType50000
... where OrderType30000 and OrdesType40000 are deselected in the configuration (via adminclient)
FolderOrd55;AppGrp_Client155;OrderType10000
FolderOrd55;AppGrp_Client155;OrderType30000
FolderOrd55;AppGrp_Client155;OrderType50000
... where OrdersType20000 and OrderType40000 are deselected in this case
I have tested following simple SQL statement, but none provides what I am searching for, exluded applications are always include in the list!
Test 1: db2 "
select distinct
ag2f.FID,
substr(fol.NAME,1,25) as Folder,
substr(ag.NAME,1,25) as AppGroup,
ag2f.AID as AID_INFLR,
substr(app.NAME,1,25) as AppName
from
ARSFOL as fol,
ARSAG as ag,
ARSAPP as app,
ARSAG2FOL as ag2f
WHERE
ag2f.FID = fol.FID
and ag2f.AGID = ag.AGID
and ag2f.AGID = app.AGID
and ag2f.AID = 0
order by ag2f.FID ASC
"
Test 2:
In the 2nd test I only changed the part in the WHERE-clause to select only "... and ag2f.AID <> 0"
Anyone has an idea?
Thanks a lot for your suggestions and have a good day
from GeeKay/Gülle/Gültekin/Gultekin ;-)