Has anyone successfully wrote a sql query that will display all parts of an assembly along with , quantity, version, and state?
Here is a Query I used in the past.
This was initial query I wrote which makes use of lot of temp tables.
Thanks for the reply and some ideas. I have tried many different approaches to this and here is what I have so far.
I am struggling on how I am going to get this done for all child assemblies under an assembly because there could be 1 or more child assemblies under the parent, so I can't just repeat same query for a set number of times.
This is a great starting point Ravi, thank you. As Earl is pointing out, a problem with it is that it stops one level down in the reference tree.
How much more work is it, to make it able to run through all levels in a large assembly structure?
Does anyone know, or does anyone have this already?
Sadly, my query language knowledge falls short here.
This query is more complicated than most think due to what Earl has pointed out with levels. One thing that might help you is a query provided by SOLIDWORKS on their Knowledge Base (if you dig deep enough) on how they generate the Contains tab. That solution number is S-059636 and the query to that solution should be attached to this thread.
What's useful with that query is that it uses a while loop to increment the level number, and adds the references to a table variable. From this query you could potentially relation the other data you need.
Thanks for sharing.
Also a great excuse to play around with recursive common table expressions:
with xrefs_rec as
(select DocumentID as TLA_DocumentID, DocumentID, XrefDocument, RevNr as TLA_RevNr, RevNr, XrefRevNr, XrefID, 0 as Level
from XRefs where XrType = 1
select xrr.TLA_DocumentID, xr.DocumentID, xr.XrefDocument, TLA_RevNr, xr.RevNr, xr.XrefRevNr, xr.XrefID, xrr.Level + 1 as Level
from XRefs xr
inner join xrefs_rec xrr on xrr.XRefDocument = xr.DocumentID and xrr.XRefRevNr = xr.RevNr
where XrType = 1)
select d1.filename as TLA, d2.filename as Parent, d3.filename as Child, Level from
inner join documents d1 on d1.DocumentID = xrr.TLA_DocumentID and d1.LatestRevisionNo = xrr.TLA_RevNr
inner join documents d2 on d2.DocumentID = xrr.DocumentID
inner join documents d3 on d3.DocumentID = xrr.XrefDocument
d1.documentid = 20001
Retrieving data ...