I guess that it is common issue if the object is check-outed. Then by your conditions you always get two result rows
PetrH
We tried the below query again today and it returned parent's version and iteration instead of children's:
SELECT parent_dm.CADName as Parent_Cadname,
parent_doc.idA2A2 AS ParentDocID,
parent_doc.versionIdA2versionInfo as Parent_Version,
parent_doc.iterationIdA2iterationInfo as Parent_iteration,
child_doc.versionIdA2versionInfo as Child_Version,
child_doc.iterationIdA2iterationInfo as Child_iteration,
ml.idA2A2 AS LinkID,
ml.idA3B5 AS ChildMasterID,
child_dm.CADName as Child_Cadname
FROM pdmlink.EPMDocument parent_doc
JOIN pdmlink.EPMDocumentMaster parent_dm
ON parent_doc.idA3masterReference = parent_dm.idA2A2
JOIN pdmlink.EPMMemberLink ml
ON ml.idA3A5 = parent_doc.idA2A2
JOIN pdmlink.EPMAsStoredMember asstored
ON asstored.idA3B5 = ml.idA3A5
JOIN pdmlink.EPMAsStoredConfig as config
ON config.idA2A2 = asstored.idA3A5
JOIN pdmlink.EPMDocumentMaster child_dm
ON ml.idA3B5 = child_dm.idA2A2
JOIN pdmlink.EPMDocument child_doc
ON asstored.idA3B5 = child_doc.idA2A2 --This gives parent's version and iteration which is incorrect--
--JOIN pdmlink.EPMDocument child_doc1
--ON child_doc1.idA3masterReference = child_dm.idA2A2 --This gives both iterations of child 2 which is incorrect--
WHERE parent_dm.CADName = '13_oct_assy_1.asm'
AND parent_doc.versionIdA2versionInfo = 'P1'
AND parent_doc.iterationIdA2iterationInfo = '2'
ORDER BY parent_dm.CADName;
--- We also tried to point the filter to children somehow using below subquery in between, but that did not work either:
AND child_doc.idA2A2 = (
SELECT child_doc.idA2A2
FROM pdmlink.EPMDocument
WHERE child_doc.idA3masterReference = ml.idA3B5
)
Could you please check and advise how to proceed?
In the Windchill GUI there is additional logic that filter the iterations.
I was able to retrieve almost correct as stored BOM but there is some issue that as stored configuration apply also for some newer iterations so your case is that the child has more iterations.
Based on the conditions in the query it returns more results then you expect but I guess that windchill gui contains some additional rule or condition that filter correct iteration. I guess it could be based on some additional information that is not know for us now.
here is my query that I have created.
parent number = PARENT.ASM and iteration is 10
select asstored.idA2A2,childMaster.documentNumber,child.iterationIdA2iterationInfo,* from wcuser.EPMDocument as parent
inner join wcuser.EPMMemberLink link on parent.idA2A2=link.idA3A5
inner join wcuser.EPMDocumentMaster parentMaster on parent.idA3masterReference=parentMaster.idA2A2
inner join wcuser.EPMDocumentMaster childMaster on link.idA3B5=childMaster.idA2A2
inner join wcuser.EPMDocument child on childMaster.idA2A2=child.idA3masterReference
inner join wcuser.EPMAsStoredMember asstored on parent.idA2A2=asstored.idA3B5
inner join wcuser.EPMAsStoredConfig config on config.idA2A2 = asstored.idA3A5
where parentMaster.documentNumber='PARENT.ASM' and parent.iterationIdA2iterationInfo=10 and config.createStampA2<child.updateStampA2 and parent.modifyStampA2>=child.modifyStampA2
PS> I tried to filter as stored BOM for a version A.10 and I can see that there are additional rows for newer iterations.
edited: unknown condition is the parent modification time has to be higher or same as the modification time of the child. I modified also the query.
PetrH
Hello @Vignesh_AJ_77
I forgot to use one very important condition 😄 that solves the issue with more newer rows.
The comparation of modify stamp of parent with child. Child modification stamp has to be lower or equal to the parent
then you get correct results
select asstored.idA2A2,childMaster.documentNumber,child.iterationIdA2iterationInfo,* from wcuser.EPMDocument as parent
inner join wcuser.EPMMemberLink link on parent.idA2A2=link.idA3A5
inner join wcuser.EPMDocumentMaster parentMaster on parent.idA3masterReference=parentMaster.idA2A2
inner join wcuser.EPMDocumentMaster childMaster on link.idA3B5=childMaster.idA2A2
inner join wcuser.EPMDocument child on childMaster.idA2A2=child.idA3masterReference
inner join wcuser.EPMAsStoredMember asstored on parent.idA2A2=asstored.idA3B5
inner join wcuser.EPMAsStoredConfig config on config.idA2A2 = asstored.idA3A5
where parentMaster.documentNumber='PARENT.ASM' and parent.iterationIdA2iterationInfo=10 and config.createStampA2<child.updateStampA2 and parent.modifyStampA2>=child.modifyStampA2
PetrH
Hi, Thanks again for the query. But unfortunately, it returns empty results:
Could you please check and advise.
Check your number. It is usually case sensitive and the number is usually uppercase. it seams that you insert filename
PetrH
try to change the condition "config.createStampA2" to "config.modifyStampA2"
btw play with the conditions. Also are you sure that iteration 2 has the as stored configuration?
PetrH
Yes, it has as stored config:
I changed the condition as well from "config.createStampA2" to "config.modifyStampA2", still no result:
btw, why are we trying to take columns from EPMdocument in the result? Ultimately, we need parent & child relationships in the output right. Could you please advise.
What is your real goal ?
The BOM relationship Parent to Child is based on a relation between specific parent version and PartMaster information.
If you do not care about the child specific version then you do not have to care about as stored configuration.
But I guess that there is some needs to see correct drawing or correct visualization and it is important.
Check the modification date of the parent and the children.
btw try to remove timestamp conditions. If you get the results, then play with the conditions - add them one by one and so on.
I did it and I got right results but I guess that in your case there is some mismatch with as stored condition and latest modification.
For example check-in parent outside Creo(CAD) Application.
PetrH
Something is wrong in your query
If I use your query, I will also get iteration number from parent instead of child in the column name Childiteration
I rewrite it and I have got correct results but I could not find an error in your select but it has to be there.
working query
select parentMaster.CADName as ParentCadname,
parent.idA2A2 AS ParentDocID,
parent.versionIdA2versionInfo as ParentVersion,
parent.iterationIdA2iterationInfo as Parentiteration,childMaster.CADName as ChildCadname,child.versionIdA2versionInfo as ChildVersion,child.iterationIdA2iterationInfo as Childiteration,link.idA2A2 AS LinkID,
link.idA3B5 AS ChildMasterID from wcuser.EPMDocument as parent
inner join wcuser.EPMMemberLink link on parent.idA2A2=link.idA3A5
inner join wcuser.EPMDocumentMaster parentMaster on parent.idA3masterReference=parentMaster.idA2A2
inner join wcuser.EPMDocumentMaster childMaster on link.idA3B5=childMaster.idA2A2
inner join wcuser.EPMDocument child on childMaster.idA2A2=child.idA3masterReference
inner join wcuser.EPMAsStoredMember asstored on parent.idA2A2=asstored.idA3B5
inner join wcuser.EPMAsStoredConfig config on config.idA2A2 = asstored.idA3A5
where parentMaster.CADName='00_zvedak.asm' and parent.iterationIdA2iterationInfo=10 and config.updateStampA2<child.updateStampA2 and parent.modifyStampA2>=child.modifyStampA2 and parent.versionIdA2versionInfo = 'A'
your not working query
SELECT parentdm.CADName as ParentCadname,
parentdoc.idA2A2 AS ParentDocID,
parentdoc.versionIdA2versionInfo as ParentVersion,
parentdoc.iterationIdA2iterationInfo as Parentiteration,
childdm.CADName as ChildCadname,
childdoc.versionIdA2versionInfo as ChildVersion,
childdoc.iterationIdA2iterationInfo as Childiteration,
ml.idA2A2 AS LinkID,
ml.idA3B5 AS ChildMasterID
FROM wcuser.EPMDocument parentdoc
INNER JOIN wcuser.EPMDocumentMaster parentdm
ON parentdoc.idA3masterReference = parentdm.idA2A2
INNER JOIN wcuser.EPMMemberLink ml
ON ml.idA3A5 = parentdoc.idA2A2
INNER JOIN wcuser.EPMAsStoredMember asstored
ON asstored.idA3B5 = ml.idA3A5
INNER JOIN wcuser.EPMAsStoredConfig as config
ON config.idA2A2 = asstored.idA3A5
INNER JOIN wcuser.EPMDocumentMaster childdm
ON ml.idA3B5 = childdm.idA2A2
INNER JOIN wcuser.EPMDocument childdoc
ON asstored.idA3B5 = childdoc.idA2A2
WHERE parentdm.CADName = '00_zvedak.asm'
AND parentdoc.versionIdA2versionInfo = 'A'
AND parentdoc.iterationIdA2iterationInfo = '10'
ORDER BY parentdm.CADName;PetrH
I've found it.
Your joins are not correct
The as stored member could not be the parent one.
asstored.idA3B5 points to the parent not to the child.
INNER JOIN wcuser.EPMDocument childdoc ON asstored.idA3B5 = childdoc.idA2A2 That is wrong.
PetrH
Thanks for the updated query. unfortunately this also did not work.
And also we have observed As stored member idA3B5 has both idA2A2 of Child & Parent.
