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

[rbabu-2]

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

[cgadre]

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

[rbabu-2]

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.

[BineshKumar1]

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.

[rbabu-2]

Hi Binesh, Thank you for the details explanation. This information helped me a lot.

[SvenVanDroogenb]

This is what I have and it works if you know the folder structure.  Would like to know though how the recursive hierarchical query looks like.

select pm.wtpartnumber, pm.name, p.versionida2versioninfo, p.iterationida2iterationinfo, plp.namecontainerinfo, sf.name

from wtpart p, wtpartmaster pm,  subfolderlink sfl, subfolder sf, pdmlinkproduct plp

where pm.ida2a2=p.ida3masterreference and sfl.IDA3B5=p.ida3b2folderinginfo and sfl.ida3b5=sf.ida2a2 and pm.ida3containerreference=plp.ida2a2

order by pm.wtpartnumber, p.versionida2versioninfo, p.iterationida2iterationinfo;