Skip to main content
rbabu-21-Visitor
1-Visitor
January 25, 2017
Question

A query that will list all WTParts in the system along with the folder they are stored in

Hi All,

Can some one provide me an SQL query that will list all WTParts in the system along with the folder they are stored in.

for example a test part: 0000000123 is stored in GOLF_CART / New_Folder/New_Folder2/New_Folder3 location.

Query result   Part Number      Location

                     0000000123       GOLF_CART / New_Folder/New_Folder2/New_Folder3

5 replies

cgadre2-Explorer
2-Explorer
January 25, 2017

Please import the report attached below and use it to get the result that you need

Translate
    rbabu-21-VisitorAuthor
    1-Visitor
    January 25, 2017

    Hi Chinmay, Thank you for the QML report. I have the similar report. Could you let me know if we can export the similar data from SQL Server/Oracle by using queries.

    Translate
    BineshKumar12-Explorer
    2-Explorer
    January 25, 2017

    I spent some time a while back on an SQL, i think a PL/SQL procedure would be the best way to go.

    This is what you have to do

    • Connect WTPartMaster to Control branch, with wtpartmaster.ida2a2=controlbranch.ida3b5 and get ControlBranch's ida2a2
    • Connect Controlbranch's ida2a2 to iterfoldermemberlink's branchida3b5 and get iterfoldermemberlink's ida3a5
    • Connect iterfoldermemberlink's ida3a5 to subfolder's ida2a2, this will give you the name of the subfolder where the part is in.

    Then to handle recursive folders

    • You need to write a recursive hierarchical query on SUBFOLDERLINK, ida3a5 left join on ida3b5, this will give you parent child relationship for recursive subfolders.

    This is what I have in my notes, I remember putting together a procedure, If I am able to find it I will post it here.

    Translate
    Terms & ConditionsAccessibility statement