I am trying to get the name of the folder that contains the test.
Subject -> Folder1 -> Folder2 -> Folder3 -> 'The test'
Is there a way to get the name of Folder1?
I know that the TS_SUBJECT is used in ALL_LISTS table and TS_SUBJECT is primary key AL_ITEM_ID in ALL_LISTS table, but how do I construct the query to get the name of the folder that contains the actual.
I am reachable for both MS SQL and Oracle queries... Here is the MS SQL version:
with folders (folder_id, parent_folder_id, top_folder_name) as (
select al_item_id as folder_id, al_father_id as parent_folder_id,
al_description as top_folder_name
where al_father_id = 2
select al_item_id, al_father_id, top_folder_name
join folders on al_father_id=folder_id
where al_father_id > 1
where folder_id = (SELECT max(TS_SUBJECT) FROM td.TEST WHERE TS_NAME='YOUR TEST NAME')
Replace 'YOUR TEST NAME' in the query with your test name. Adjust the sub-query if the test name is not unique.
The query returns the top folder for a specified test.
Here is the Oracle version. It does not use the SYS_CONNECT_BY_PATH and that allows returning just one row:
SELECT AL_DESCRIPTION AS top_folder_name, level as folder_level
WHERE AL_FATHER_ID > 0
START WITH AL_ITEM_ID = (SELECT max(TS_SUBJECT) FROM TEST WHERE TS_NAME='YOUR TEST NAME')
CONNECT BY PRIOR AL_FATHER_ID = AL_ITEM_ID
ORDER BY 2 DESC
WHERE rownum = 1