Using Common Table Expressions In Teamcenter
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:
- Identify the Item Revisions and its Release status names. Make it 1 CTE.
- 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.
- Finally, apply the join from Item Revision to Item to get the
Item_Idinformation. This would be the query that works on CTE2.
A snippet of the same could be found below for reference.
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.