// Borland Code VOzohili
// Date: 15 April 2009

// Determine which Test Definitions are associated with a specified attribute
// Return the Test Definition, the Attribute Name and the Attribute value

// Create a new advanced query report in SCTM, copy and paste the SQL query below into the report, or
// Open SCTM database using SQL Enterprise Manager or equivalent
// Run the SQL query below against SCTM database


SELECT     SCC_Projects.ProjectName AS [Project Name], TM_TestContainerName.Name AS [Test Container], TM_TestDefName.Name AS [Folder/Next Hierarchy], 
                      TM_TestPlanNodes.Name AS [Test Definition], TM_Attributes.Name AS [Attribute Name], TM_AttributeOptions.OptionValue AS [Attribute Value]
FROM         TM_AttributeOptions INNER JOIN
                      TM_NodeAttributeOptions ON TM_AttributeOptions.OptionID_pk = TM_NodeAttributeOptions.OptionID_pk_fk INNER JOIN
                      TM_TestDefinitions INNER JOIN
                      TM_TestPlanNodes ON TM_TestDefinitions.TestPlanNodeID_pk_fk = TM_TestPlanNodes.NodeID_pk INNER JOIN
                      TM_PlanTreePaths AS TM_PlanTreePaths_1 ON TM_TestPlanNodes.NodeID_pk = TM_PlanTreePaths_1.NodeID_pk_fk INNER JOIN
                      TM_TestContainers AS TM_TestContainers_1 ON TM_PlanTreePaths_1.ParentNodeID_pk_fk = TM_TestContainers_1.TestContainerID_pk_fk INNER JOIN
                      SCC_Projects ON TM_TestContainers_1.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
                      TM_TestPlanNodes AS TM_TestDefName ON TM_TestPlanNodes.ParentNodeID_fk = TM_TestDefName.NodeID_pk INNER JOIN
                      TM_TestPlanNodes AS TM_TestContainerName ON TM_PlanTreePaths_1.ParentNodeID_pk_fk = TM_TestContainerName.NodeID_pk INNER JOIN
                      LQM_Tests ON TM_TestPlanNodes.NodeID_pk = LQM_Tests.TestID_pk INNER JOIN
                      TM_Attributes ON SCC_Projects.ProjectID_pk = TM_Attributes.ProjectID_pk_fk ON 
                      TM_AttributeOptions.AttributeID_pk_fk = TM_Attributes.AttributeID_pk AND 
                      TM_NodeAttributeOptions.NodeID_pk_fk = TM_TestPlanNodes.NodeID_pk
WHERE     (TM_Attributes.Name LIKE 'A%')