Using Common Table Expressions In Teamcenter - pawanit17/Exploring-Teamcenter-The-SQL-Way GitHub Wiki

A Common Table Expression, also referred to as a CTE, is a temporary result set which can be further used in an SQL.

I personally found CTEs very useful. I was able to confidently explore data more easily with a CTE. For instance, if you are to write a query to find all the Item Revisions that have more than 1 release status on it, then you can achieve it easily through CTE. All you need to do is below:

  1. Identify the Item Revisions and its Release status names. Make it 1 CTE.
  2. Apply a group-by clause on the above CTE and get the ones that have more than 1 result status on them. This would be the second CTE.
  3. Finally, apply the join from Item Revision to Item to get the Item_Id information. This would be the query that works on CTE2.

A snippet of the same could be found below for reference.

CTE Example

Note that you can achieve the same via a single CTE instead of two CTEs as well.

I personally liked this specific web page on CTE explaining things pretty well.