How to Use Like Operator in Dynamic Query and When to Use it?
Scenario:
User wants to filter all the records from the SalesTable that contain the word “Contoso” when they run the runnable job.
Note: SysQuery::valueLike() is used for filter all the records which matches the given condition/word.
High level resolution steps
Create a new query and adding the datasource in a query.
Use the addRange method to add a range on the SalesName field in SalesTable and set the range value using “Contoso” which filters all the records from the SalesTable that contain the word “Contoso” in the query.
Note: instead of hardcode value you can give your own parameter.
Create a QueryRun object to execute the query and loop each record from the SalesTable that matches the query.
Create a runnable class and run the job.
Detailed resolution steps:
Step 1: Create a new query and adding the datasource in a query.
| Query query = new Query(); QueryBuildDatasource qbdsSalesTable; qbdsSalesTable = query.addDataSource(tableNum(SalesTable)); |
Step 2: Use the addRange method to add a range on the SalesName field in SalesTable and set the range value using “Contoso” which filters all the records from the SalesTable that contain the word “Contoso” in the query.
Set filter for a Query:
| QueryBuildRange qbrSalesNameRange; str searchTxt = “Contoso”; qbrSalesStatusRange.value(SysQuery::valueLike(searchTxt) |
Step 3: Create a QueryRun object to execute the query and loop each record from the SalesTable that matches the query.
Query Execution:
| Query query; QueryRun queryRun; SalesTable salesTable; query = new Query(); queryRun = new QueryRun(query); while (queryRun.next()) { salesTable = queryRun.get(tableNum(SalesTable)); Info(strFmt("SalesId - %1, CustAccount - %2, SalesStatus - %3, SalesType - %4, SalesName - %5", salesTable.SalesId, salesTable.CustAccount, salesTable.SalesStatus, salesTable.SalesType,salesTable.SalesName));} |
Step4: Create a runnable class and write the code below.
Code Snippet:
class SCCSalesOrderNameService
{
///
/// Class entry point. The system will call this method when a designated menu
/// is selected or when execution starts and this class is set as the startup class.
///
/// The specified arguments.
public static void main(Args _args)
{
SCCSalesOrderNameService sccSalesOrderNameService = new SCCSalesOrderNameService();
sccSalesOrderNameService.searchSalesOrdersByName('Contoso');
}
public static void searchSalesOrdersByName(str _searchTxt)
{
Query query = new Query();
QueryBuildDatasource qbdsSalesTable;;
QueryBuildRange qbrSalesNameRange;
QueryRun queryRun;
SalesTable salesTable;
qbdsSalesTable = query.addDataSource(tableNum(SalesTable));
qbrSalesStatusRange.value(SysQuery::valueLike(_searchTxt));
queryRun = new QueryRun(query);
while (queryRun.next())
{
salesTable = queryRun.get(tableNum(SalesTable));
Info(strFmt("SalesId - %1, CustAccount - %2, SalesStatus - %3, SalesType - %4, SalesName - %5",
salesTable.SalesId,
salesTable.CustAccount,
salesTable.SalesStatus,
salesTable.SalesType,
salesTable.SalesName));
}
}
}
Code Explanation:
This code is used to filter all the records from the SalesTable that contain the word “Contoso” when the user runs the job.
Output:
When the user runs the job, filtering all the records from the SalesTable that contain the word “Contoso” based on the input value searchTxt.
