Scott Nishizaki beat me to it! The SQL he shared is probably the 5th iteration over the past few years, as I attempted to learn hierarchical queries to loop through the PSTREENODE table and adjust for however many levels we end up having on the tree. A few things to point out:
The "DATES" common table expression is just used to get a list of all effective dates where either the ACAD_ORG_TBL changed, or the tree that we use for tracking acad structure over time. You'll notice that we created a separate ACAD_ORG_REPORT tree for this historical reporting, whereas we don't try to retain the full effective-dated history on the delivered ACAD_ORGANIZATION tree used for security.
Our departments are extremely granular, sometimes split by Career code, so we are not joining Subjects to the tree. We just use the "dynamic flag".
Some schools are nested within colleges and some schools are not, so in order to find top-level college/school and second-level college/school where applicable, we have to test the descriptions against naming conventions... this requires some maintenance over time, but it's worth it for us in the long run.
Basically if you were to replace the tree name with yours (probably ACAD_ORGANIZATION)
Then you should at least get some data out, and you can start tweaking with the rest of the hard-coded stuff, etc. Have fun! :)
Sr. Business Systems Analyst, Student Information Systems
Original Message:
Sent: 02-11-2025 11:25 AM
From: Scott Nishizaki
Subject: Querying tree tables like ACAD_ORGANIZATION
This SQL was written by my co-worker Scott Douglas, it's a bit complex so let us know if you have questions.
WITH DATES AS ( SELECT DISTINCT A.EFFDT FROM SYSADM.PSTREENODE A WHERE A.TREE_NAME = 'ACAD_ORG_REPORT' UNION SELECT DISTINCT B.EFFDT FROM SYSADM.PS_ACAD_ORG_TBL B ) , TREE AS ( SELECT DISTINCT T.TREE_NODE ACAD_ORG , T.EFFDT , COALESCE(TRIM(SYS_CONNECT_BY_PATH (CASE WHEN LEVEL = 2 THEN TREE_NODE END ,' ')) ,' ') AS APU_SCHL_ORG , COALESCE(TRIM(SYS_CONNECT_BY_PATH (CASE WHEN LEVEL = 3 THEN TREE_NODE END ,' ')) ,' ') AS APU_SCH2_ORG , LTRIM(SYS_CONNECT_BY_PATH (TREE_NODE ,' > ') ,' > ') AS HIERARCHY , LEVEL LVL FROM SYSADM.PSTREENODE T WHERE TREE_NAME = 'ACAD_ORG_REPORT' START WITH TREE_NODE = 'APU' CONNECT BY PRIOR TREE_NODE = PARENT_NODE_NAME AND TREE_NAME = PRIOR TREE_NAME AND EFFDT = PRIOR EFFDT ) SELECT DISTINCT MAIN.ACAD_ORG , D.EFFDT , MAIN.DESCR , MAIN.DESCRFORMAL , COALESCE(TRIM(MAIN.APU_SCHL_ORG) , MAIN.ACAD_ORG) AS APU_SCHL_ORG , COALESCE(SCHL.DESCR , 'Azusa Pacific University') AS APU_SCHL_DESCR , COALESCE(SCHL.DESCRFORMAL , 'Azusa Pacific University') AS APU_SCHL_DESCRFRML , CASE WHEN SCH2.IS_SCHOOL = 'Y' THEN MAIN.APU_SCH2_ORG END AS APU_SCH2_ORG , CASE WHEN SCH2.IS_SCHOOL = 'Y' THEN SCH2.DESCR END AS APU_SCH2_DESCR , CASE WHEN SCH2.IS_SCHOOL = 'Y' THEN SCH2.DESCRFORMAL END AS APU_SCH2_DESCRFRML , MAIN.LVL , MAIN.HIERARCHY FROM DATES D JOIN ( SELECT DISTINCT ORG1.ACAD_ORG , ORG1.EFFDT , ORG1.DESCR , ORG1.DESCRFORMAL , T1.APU_SCHL_ORG , T1.APU_SCH2_ORG , T1.LVL , T1.HIERARCHY FROM ( SELECT ORG.ACAD_ORG , D1.EFFDT , ORG.EFFDT AS ORG_EFFDT , ORG.DESCR , ORG.DESCRFORMAL , ROW_NUMBER () OVER (PARTITION BY ORG.ACAD_ORG , D1.EFFDT ORDER BY ORG.EFFDT DESC) RN FROM DATES D1 JOIN SYSADM.PS_ACAD_ORG_TBL ORG ON ORG.EFFDT <= D1.EFFDT ) ORG1 JOIN TREE T1 ON ORG1.ACAD_ORG = T1.ACAD_ORG AND ORG1.EFFDT = T1.EFFDT WHERE ORG1.RN = 1 ) MAIN ON D.EFFDT = MAIN.EFFDT LEFT JOIN SYSADM.PS_ACAD_ORG_TBL SCHL ON MAIN.APU_SCHL_ORG = SCHL.ACAD_ORG LEFT JOIN ( SELECT ACAD_ORG , DESCR , DESCRFORMAL , EFFDT , CASE WHEN (DESCRFORMAL LIKE '%School%' OR DESCRFORMAL = 'Azusa Pacific Seminary') AND DESCRFORMAL NOT LIKE '%Dept%' AND DESCRFORMAL NOT LIKE '%Department%' AND SUBSTR (DESCRFORMAL , - 3) NOT IN (' UG' , ' GR' , ' UP' , ' GH' , ' ND') THEN 'Y' END AS IS_SCHOOL FROM SYSADM.PS_ACAD_ORG_TBL ) SCH2 ON MAIN.APU_SCH2_ORG = SCH2.ACAD_ORG WHERE ( SCHL.EFFDT = (SELECT MAX (O2.EFFDT) FROM SYSADM.PS_ACAD_ORG_TBL O2 WHERE SCHL.ACAD_ORG = O2.ACAD_ORG AND O2.EFFDT <= D.EFFDT) OR SCHL.EFFDT IS NULL) AND ( SCH2.EFFDT = (SELECT MAX (O3.EFFDT) FROM SYSADM.PS_ACAD_ORG_TBL O3 WHERE SCH2.ACAD_ORG = O3.ACAD_ORG AND O3.EFFDT <= MAIN.EFFDT) OR SCH2.EFFDT IS NULL) ORDER BY MAIN.ACAD_ORG, D.EFFDT DESC
------------------------------
Scott Nishizaki
Developer/Analyst
Azusa Pacific University
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
Original Message:
Sent: 02-07-2025 06:01 PM
From: Cho Kim
Subject: Querying tree tables like ACAD_ORGANIZATION
That's correct, Scott. My end goal was to report the school associated with each academic plan. The diagram below depicts how our academic organization structure is laid out. Sometimes there is a "sub-school" folder where querying the parent node information returns the sub-school and not the actual school the academic org resides in. Thanks for the idea about creating a SQL view, curious what that SQL looks like if you can share it!
I was trying to do what was in the attached slides shared at SCHRUG that eliminates joining to the PSTREENODE over and over again but the example uses HCM data and doesn't illustrate the joins in the screenshots.
Original Message:
Sent: 02-07-2025 11:29 AM
From: Scott Nishizaki
Subject: Querying tree tables like ACAD_ORGANIZATION
As someone at an institution with a similar setup, I'm going to guess Cho is being asked to report the school associated with each plan (right ?). We never found a better way to do this than navigating the tree unfortunately, so we created a SQL view to do it for us so that at least we didn't have to manually add it to the PS Query.
------------------------------
Scott Nishizaki
Developer/Analyst
Azusa Pacific University
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
Original Message:
Sent: 02-07-2025 07:46 AM
From: Dana Pawlowicz
Subject: Querying tree tables like ACAD_ORGANIZATION
Do you mean in PS Query?
You can do "in tree" and then you don't have to do the joins manually to the tree tables.
For example:
If I need to look at ranges, I connect directly to the tree records. A lot of my trees are very basic and is just a list so to speak so if I just need to know if something is in the list, I'll use the in tree or not in tree function.
Is this what you are asking?
Original Message:
Sent: 2/6/2025 6:27:00 PM
From: Cho Kim
Subject: Querying tree tables like ACAD_ORGANIZATION
Hello!
I am looking for a way to simplify querying trees without repeatedly joining to PSTREENODE for different levels of a tree.
Is there an effective way to do this in query?
For example, we have acad org on the academic plan owner record and sometimes the acad org is at the department level as the owner vs school. I am trying to pull in the school. The parent node of the acad org is not always the school in our setup.
Thanks in advance!
------------------------------
Cho Kim
Associate Registrar - Enrollment & Systems Management
Southern Methodist University
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------