Author Topic: SQL to get an overview of relations in a Folder (or in all folders)  (Read 2741 times)

gkirca

  • Newbie
  • *
  • Posts: 6
    • View Profile
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 ;-)
« Last Edit: December 09, 2020, 02:05:01 PM by gkirca »
from GeeKay/Gülle/Gültekin/Gultekin ;-)
---
https://www.10m.de

Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
Re: SQL to get an overview of relations in a Folder (or in all folders)
« Reply #1 on: December 10, 2020, 07:55:22 AM »
For the case where not all applications are in the folder, the following where clause works.
WHERE                   
  ag2f.FID = fol.FID     
  and ag2f.AGID = ag.AGID
  and ag2f.AID = app.AID
  and ag2f.AID <> 0   

I realize that does not solve your problem completely, I'll think about it some more and post again if I come up with other ideas.

Update: you don't need this line in the where clause:   and ag2f.AID <> 0
« Last Edit: December 10, 2020, 08:30:34 AM by Darrell Bryant »
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

Norbert Novotny

  • Jr. Member
  • **
  • Posts: 46
    • View Profile
Re: SQL to get an overview of relations in a Folder (or in all folders)
« Reply #2 on: March 03, 2021, 02:33:13 PM »
Hi,
Would this be any help to you?

Code: [Select]
select distinct f.name as folder, ag.name as applGroup, a.name as appl from arsag2fol a2f
  inner join arsfol f
    on a2f.fid = f.fid
  inner join arsapp a
    on a2f.agid = a.agid
   and (not exists (select 1 from arsag2fol where aid != 0 and agid = a2f.agid and fid = a2f.fid)
    or a2f.aid=a.aid)
  inner join arsag ag
    on a2f.agid = ag.agid
where f.name in ('My Folder A','My Folder B')
order by 1,2,3;

... probably not the most optimal SQL :-[

Cheers,
 N.
Norbert Novotny
Legal archiving - Swisscom AG

Mobile:  +41-On-request

Dev: #SQL, #Perl, #Java, #C

Interests: #CMOD, #Multiplatforms, #DB2, #Oracle, #TSM, #ERM, #Performance

gkirca

  • Newbie
  • *
  • Posts: 6
    • View Profile
Re: SQL to get an overview of relations in a Folder (or in all folders)
« Reply #3 on: March 04, 2021, 12:48:26 AM »
Yesterday in the evening call I knew I know you - but plenty of time to check! :D
I hope everything is fine with you?
I will test your suggestion and let you know if that fits to my needs - thanks a lot meanwhile for that ...
from GeeKay/Gülle/Gültekin/Gultekin ;-)
---
https://www.10m.de