Table of Contents

IT:AD:Enterprise Architect:HowTo:Search:WellKnown SQL Tokens

Summary

As you're creating SQL statements for your canned Searches and Templates, you'll often be trying to craft your sql statements to work on the current element, current package, or all child packages.

You'll also need to make your SQL portable so that if you are working on a local *.mdf, the same templates will continue to work when you later import your sql to the organisation's sql server backed repository (different db means different sql sytax…)

Notes

Tokens I've found useful are:

Examples

<SearchTerm>: 

SELECT 
t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE,t_object.Name AS Name, t_object.Object_Type AS [Type], t_object.Stereotype, t_object.Scope,t_object.Status, t_object.Phase, t_object.CreatedDate, t_object.ModifiedDate
FROM
t_object
WHERE
t_object.Object_Type='Device' 
AND
t_object.Package_ID IN (#Branch#)
AND
t_object.Name LIKE '*<Search Term>*'

#WC#: * This stands for WildCard ('%' on some db servers, '' on others.) Below is the same query, but made portable: 

SELECT 
t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE,t_object.Name AS Name, t_object.Object_Type AS [Type], t_object.Stereotype, t_object.Scope,t_object.Status, t_object.Phase, t_object.CreatedDate, t_object.ModifiedDate
FROM
t_object
WHERE
t_object.Object_Type='Device' 
AND
t_object.Package_ID IN (#Branch#)
AND
t_object.Name LIKE '#WC#<Search Term>#WC#'

#PACKAGE#: * The currently selected Package. * I've not found much use for this one, as I generally prefer #BRANCH#. 

SELECT 
t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE,t_object.Name AS Object, t_object.Object_Type AS [Type], t_object.Stereotype, t_object.Scope,t_object.Status, t_object.Phase, t_object.CreatedDate, t_object.ModifiedDate
FROM
t_object
WHERE
t_object.Object_Type='Device' 
AND
t_object.Package_ID=#Package#

#BRANCH#: * The currently selected Package – and all Packages under it. * You have to use the IN clause with it as it's a comma separated list of numbers. 

SELECT 
t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE,t_object.Name AS Object, t_object.Object_Type AS [Type], t_object.Stereotype, t_object.Scope,t_object.Status, t_object.Phase, t_object.CreatedDate, t_object.ModifiedDate
FROM
t_object
WHERE
t_object.Object_Type='Device' 
AND
t_object.Package_ID IN (#Branch#)

#OBJECTID#: * The Element currently being processed. * Won't work within the Search (Ctrl-F) panel, but will work from within Fragment Templates. 

-- I've used this within a Device specific template in order to get the 
-- Connectors of the Device currently being processed.
SELECT t_object.ea_guid & t_object.Name as [ConnectorClassName-HyperLink] 
FROM t_object, t_connector
WHERE
t_connector.Start_Object_ID = #OBJECTID#
AND t_connector.Connector_Type = 'ConnectionPath'

#PACKAGEID#: This one is like #OBJECTID# in that it's meant to be invoked within Fragments.

Resources

* http://www.sparxsystems.com/enterprise_architect_user_guide/10/reporting/custom_query_fragments.html * http://www.sparxsystems.com/enterprise_architect_user_guide/9.0/navigate_search_and_trace/creating_filters.html