Combining Basic Conditions and Advanced SQL Conditions

You can optionally use basic conditions in combination with advanced SQL to define your search filter. This enables you to specify simple query by example conditions without having to specify them as part an advanced SQL condition. This simplifies your advanced SQL statement. Additionally, you can define basic conditions using Query At Runtime parameters and use them with advanced SQL conditions.

To find active items owned by Joe that have a file attachment, you can define part of your search filter using basic conditions:

Active/Inactive = Active
and
Owner contains any Joe Manager

And you can use pass-through SQL for the remainder:

@WHERE UBG_ISSUES.TS_ID IN (
  SELECT a.TS_CASEID FROM TS_ATTACHMENTS a
  JOIN TS_TABLES t ON a.TS_SRCTABLEID = t.TS_ID AND t.TS_DBNAME = 'UBG_ISSUES'
  WHERE a.TS_BASETYPE = 16
)

In the example above, 16 represents a file attachment.

To return all active items owned by Laura that have been through the Fix transition:

Active/Inactive = Active
and
Owner contains any Laura Engineer

And use the following pass-through SQL for the remainder:

@WHERE UBG_ISSUES.TS_ID IN (
  SELECT ca.TS_ITEMID FROM TS_CHANGEACTIONS ca
  JOIN TS_TABLES t ON ca.TS_TABLEID = t.TS_ID AND t.TS_DBNAME = 'UBG_ISSUES'
  JOIN TS_TRANSITIONS tr ON ca.TS_TRANSITIONID = tr.TS_ID AND 
  tr.TS_INTERNALNAME = 'IDM.FIX'
)

Note the following:

• A designer can find the Fix transition's internal name by editing the transition in Composer.
• This SQL can also be extended to filter on the user who ran the transition and/or the time period when the transition occurred. For example, in SQL Server, add the following before the closing parenthesis to return only items that were transitioned since the beginning of this month:

  WHERE ca.TS_TIME>DATEDIFF(s,'1/1/1970', DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0))

• To avoid performance issues using the SQL above, your administrator should create an index on TS_CHANGEACTIONS with keys on the TS_TRANSITIONID, TS_TABLEID, and TS_ITEMID columns (in that order). If the SQL is extended to filter on user or time, add the TS_USERID and/or TS_TIME columns to the index.
• To avoid potential performance issues in SQL Server, add the NOLOCK hint for each table in the advanced SQL.