Sparx Systems Forum

Enterprise Architect => Suggestions and Requests => Topic started by: Mike Hatch on February 27, 2015, 10:36:34 am

Title: Allow Common Table Expressions in custom search
Post by: Mike Hatch on February 27, 2015, 10:36:34 am

When defining a custom search using SQL, it would be useful to be able to use a Common Table Expression (CTE) when using SQL Server.  Currently the tool does not throw an error, but does not execute a statement that begins with a CTE.  Here is an example  query that would build a path to the current object by using a recursive CTE to find the Parents of the object.

Code: [Select]

;With Hierarchy (Object_ID,Name,ParentID,Path)
AS
(
--Anchor query defines the first non-recursive query
SELECT p.Object_ID,p.Name,p.ParentID, CAST(p.Name AS varchar(max)) as Path
FROM t_object p
LEFT JOIN t_object t
ON t.ParentID = p.Object_ID
WHERE p.ParentID = 0
UNION ALL
--Recursive query
SELECT t2.Object_ID,t2.Name,t2.ParentID, CAST(h.Path + '->' + t2.Name AS varchar(max))
FROM Hierarchy h
INNER JOIN t_object t2
ON t2.ParentID = h.Object_ID
)
--output query using the CTE defined above
SELECT distinct Object_ID, Name,ParentID, Path
FROM Hierarchy where ParentID <> 0
OPTION (MAXRECURSION 0)

Title: Re: Allow Common Table Expressions in custom searc
Post by: Geert Bellekens on March 07, 2015, 01:36:40 am

+1