2

I'm trying to query a WindChill database to get all child parts of a given part (assembly). What I have right now is this:

-- this is how sql server management studio rewrites an oracle join
SELECT    M1.WTPARTNUMBER AS COMPONENT, M2.WTPARTNUMBER AS ASSEMBLY, MAX(WTPARTUSAGELINK.AMOUNTA7) AS AMOUNT
FROM      WTPART, WTPARTMASTER M2, WTPARTUSAGELINK, WTPARTMASTER M1
WHERE     WTPART.IDA3MASTERREFERENCE = M2.IDA2A2 
                   AND WTPART.IDA2A2 = WTPARTUSAGELINK.IDA3A5
                   AND WTPARTUSAGELINK.IDA3B5 = M1.IDA2A2
GROUP BY  M2.WTPARTNUMBER, M1.WTPARTNUMBER
order by  M2.WTPARTNUMBER, M1.WTPARTNUMBER

It works in the sense that I get a list of every assembly and its parts, which I can later filter when building my tree. This is perfectly fine, there's like 1k assemblies total.

The problem is that I also get lines I deleted from WindChill (they don't show in the WindChill web app). I realize this is a long shot, but is there another table I need to join or something to get whether or not to actually include a potential child part?

skaffman
  • 398,947
  • 96
  • 818
  • 769
Blindy
  • 65,249
  • 10
  • 91
  • 131
  • Very much a longshot as I have no experience with WindChill, but I would guess that these tables have something like a "deleted" column that gets set to Yes (or status set to Deleted, etc) when you deleted them from the web app (as opposed to actually deleting the data). If that is the case, you would just need to add the deleted = 'Yes' criteria (or whatever it turns out to be) to your query. – Craig Feb 09 '11 at 22:19
  • They do indeed, but it's set to false for the wrong rows too. – Blindy Feb 09 '11 at 23:19
  • can someone share if this type of query is future proof within the context of the windchill product line https://stackoverflow.com/questions/54712891/querying-database-of-windchill-using-sql – ffejrekaburb Feb 20 '19 at 18:35

2 Answers2

4

I believe you should be using the specific version of the part, not the part master.

The way BOMs and assemblies are handled within windchill, are that snapshots of the related parts are taken with each revision of the part. If a particular part 'version' is not referenced, then you will get 'all'.

I don't know the SQL to do that because, I've always used info engine tasks to achieve the same results, avoiding direct contact with the db. There is a built-in webject called 'Query-Tree' that, given an object, will return all related child objects. So if I wanted a full Bill of Materials on a part, I just run the webject and pass it that part object.

Dortz
  • 177
  • 1
  • 9
0

just adding condition LATESTITERATIONINFO = 1 in WTPART

AlexPalla
  • 191
  • 3
  • 4