Examples of SBM Pass-Through SQL

To use the following examples, you must know the database names for tables and fields in your SBM system. Contact your administrator for assistance.

Finding Active Items Owned by Deleted Users

Example of finding items that are active and owned by deleted users:

@WHERE TS_ACTIVEINACTIVE=0 and TS_OWNER in (select TS_ID from TS_USERS 
where TS_USERS.TS_STATUS=1)
Tip: When you use the @ modifier, SBM does not convert names to numbers or logical database names to actual database names. The statement must contain the WHERE clause, as shown in the example above.

Finding Active Items Owned by Members of a Group

Example of finding items that are active and owned by users of a particular group called "Management:"

@WHERE TS_ACTIVEINACTIVE=0 and TS_OWNER in (select TS_MEMBERS.TS_USERID
from TS_MEMBERS, TS_GROUPS where TS_MEMBERS.TS_GROUPID = 
TS_GROUPS.TS_ID and TS_GROUPS.TS_NAME ='group name')

Finding Items Submitted by Members of a Group

Example for finding incidents submitted by the users of a particular group:

@WHERE tablename.TS_SUBMITTER IN (SELECT TS_ID FROM TS_USERS WHERE
TS_USERS.TS_ID IN (SELECT TS_USERID FROM TS_MEMBERS WHERE
TS_MEMBERS.TS_GROUPID IN (SELECT TS_ID FROM TS_GROUPS WHERE
TS_GROUPS.TS_NAME = 'Technical Support'))) 

Finding Items Closed Within 14 Days of Submittal

Example of finding items that were closed within two weeks (14 days) of submittal (it is possible that these items are reopened because TS_ACTIVEINACTIVE is not part of the clause):

@WHERE ( (TS_CLOSEDATE - TS_SUBMITDATE) < (14) )
Note: TS_CLOSEDATE and TS_SUBMITEDATE are both native date data types, therefore you can also use Oracle and MS SQL Server date functions.

Finding Items in the New State

Example of finding items currently in the "New" state (the sub-query can accept one unqualified TS_PROJECTID by correlating it to the table in the main selection, and SBM usually selects the project ID when you query primary tables):

@WHERE
TS_STATE in (select TS_ID from TS_STATES where TS_NAME = 'New'
and TS_STATES.TS_PROJECTID = TS_PROJECTID)

Finding Items That Have Attachments

Example of finding issues in the Issues table that have attachments:

@WHERE tablename.TS_ID IN (SELECT TS_CASEID FROM
TS_ATTACHMENTS WHERE TS_SRCTABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE
TS_DBNAME = 'tablename'))

Finding Items You Owned in the Past

Example for finding items you owned in the past:

@WHERE
tablename.TS_ID IN (SELECT TS_CASEID FROM TS_CHANGES WHERE TS_TABLEID IN
(SELECT TS_ID FROM TS_TABLES WHERE TS_DBNAME LIKE 'tablename' )
AND TS_FLDID IN (SELECT TS_ID FROM TS_FIELDS WHERE TS_SYSCODE = 12 AND
TS_TABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE TS_DBNAME LIKE
'tablename' ) ) AND TS_NEWINT IN (SELECT TS_ID FROM TS_USERS
WHERE TS_NAME LIKE 'username' ) )

You may want to append a date range on this report.

@WHERE tablename.TS_ID IN (SELECT TS_CASEID FROM TS_CHANGES WHERE
TS_TABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE TS_DBNAME LIKE
'tablename' ) AND TS_FLDID IN (SELECT TS_ID FROM TS_FIELDS WHERE
TS_SYSCODE = 12 AND TS_TABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE
TS_DBNAME LIKE 'tablename' )) AND TS_NEWINT IN (SELECT TS_ID FROM
TS_USERS WHERE TS_NAME LIKE 'username' ) AND TS_TIME >
lowerdaterange AND TS_TIME < upperdaterange )

Finding Items In Which a User Has Changed

Example for finding items in which a User field recently changed to a particular user:

@WHERE
tablename.TS_ID IN (SELECT tablename.TS_ID FROM tablename, TS_CHANGES,
TS_FIELDS, TS_TABLES, TS_USERS WHERE tablename.TS_ID = TS_CHANGES.TS_CASEID
AND TS_FIELDS.TS_NAME LIKE 'fieldname' AND TS_CHANGES.TS_FLDID =
TS_FIELDS.TS_ID AND TS_TABLES.TS_DBNAME LIKE 'tablename' AND
TS_CHANGES.TS_TABLEID =TS_TABLES.TS_ID AND TS_USERS.TS_LOGINID LIKE
'username' AND TS_CHANGES.TS_NEWINT=TS_USERS.TS_ID AND
TS_CHANGES.TS_TIME > date)

Using a Sub-query to Find Information in Relational Fields

Example of using a sub-query to obtain information from Relational fields selected for the report:

@where TS_CONTACTID in (select TS_ID from TS_CONTACTS where
TS_CONTACTS.TS_STATE = 'CO')
Tip: Correlated sub-queries can sometimes have poor performance. Use a join instead of sub-query for the example above. If you select "Contact" for an Incidents report, then the pass-through SQL can be written as, @TS_CONTACTS where TS_CONTACTID = TS_CONTACTS.TS_ID and TS_CONTACTS.TS_STATE = 'CO'. TS_CONTACTS may already be joined for the report, but the SBM SQL will be using an alias, so you must add the table name yourself before the "where".

Related Topics

About Pass-Through SQL

Considerations for Using Pass-Through SQL

Examples of Using Joins in Pass-Through SQL

Common SQL Operators