Find All Top-level Folders from a List of Folders

 

Problem description & analysis

We have a database table TBLFOLDERS as follows:

FLDR

C:\Folder1

C:\Folder1\Subfolder1

C:\Folder1\Subfolder2

C:\Folder2\Subfolder1

We are trying to find all top-level directories from it. Below is the desired result:

C:\Folder1

C:\Folder2\Subfolder1

Solution

We write the following SPL script p1.dfx within esProc:

A

1

=connect("mssql")

2

=A1.query@x("SELECT * FROM TBLFOLDERS")

3

=A2.select((x=#,y=#1,!A2.select@1(#!=x && pos(y,#1)==1)))

Explanation:

A1   Connect to database mssql.

A2   Return a table sequence and auto-close the database connection when execution is finished.

A3  Find strings that are not contained in all the other strings.

Refer to How to Call an SPL SCript in Java to learn about how to integrate the SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/64125262/how-do-i-use-sql-to-select-only-top-level-folders-from-a-table-of-folders