Reporting → Report Search Criteria → 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:
WHERE ca.TS_TIME>DATEDIFF(s,'1/1/1970', DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0))
Copyright © 2013–2019 Micro Focus or one of its affiliates. All rights reserved.