Applying constructed queries for SQL Data Sources

eMACH-Sigma now enables you to apply constructed queries for SQL data sources. The constructed queries should initiate the dynamic binding of specified parameters, along with the applied functions in a streamlined manner. While defining parameters in constructed queries, the defined parameters vary in accordance with the applied filters, as specified by the end user. To nullify the unintended variation in parameters, new functions have been introduced to initiate the dynamic binding of parameters, with precisely constructed queries. The three new functions, applyWhen, applyWhenAnd and applyWhenOr can be used effectively to apply constructed queries for SQL data sources.

To apply constructed queries for SQL data sources, perform the following steps in eMACH-Sigma Studio:

Click the Data Source icon on an existing database connection. For detailed information on creating Connections, refer Create a Database Connection.

The Create Datasource screen appears.
In the Data Source Name field, provide a unique data source name, e.g. ACCOUNT DETAILS.

The Connection Name is a non-editable field.
Enable the Is SQL? toggle option.
In the Data Query field, provide the appropriate constructed queries, as shown in the following code snippet:

When writing SQL statements in the Data Query field, avoid giving aliases for column names. Use the Add Labels option to give aliases for the columns.
```
SELECT * FROM DEMO_ACCOUNT_SUMMARY_DATA where 
	{{applyWhen({
				"parameterId" : "ACCOUNT_TYPE", 
				"EMPTY" : "ACCOUNT_TYPE is null", 
				"DEFAULT" : "ACCOUNT_TYPE = #{ACCOUNT_TYPE}"
				})
	 }}
```
In the aforementioned code snippet, the ACCOUNT_TYPE serves as the parameter in the defined Where Clause of the constructed query. To define a switch case for the constructed query string, specify the parameter value and provide the possible cases for defining the constructed queries. The applyWhen function initiates the dynamic binding of the specified parameter, e.g. ACCOUNT_TYPE, thereby ensuring that constructed queries are defined in a precise manner. Alternatively, if the parameter is not specified or defined in the constructed queries, then you can make use of the EMPTY case in the applyWhen function, whereas if the specified parameter is available in the list of cases, defined as constructed queries, then you can make use of the the specified parameter case, e.g. ACCOUNT_TYPE, in the applyWhen function. If the parameter is not available in the listed case of the constructed queries, then you can make use of the DEFAULT case to specify the default parameter value in the applyWhen function. To apply parameter configurations for SQL data sources, refer Applying parameters for SQL Data Sources.
To make use of the AND operator, specify the applyWhenAnd function as a prefix to the constructed query string, as shown in the following code snippet:
```
SELECT * FROM DEMO_ACCOUNT_SUMMARY_DATA where 
	{{applyWhenAnd({
				"parameterId" : "ACCOUNT_TYPE", 
				"EMPTY" : "ACCOUNT_TYPE is null", 
				"DEFAULT" : "ACCOUNT_TYPE = #{ACCOUNT_TYPE}"
				   })
	 }}
```
In the aforementioned code snippet, the Where Clause for the ACCOUNT_TYPE parameter is specified as null with the AND operator, e.g. AND ACCOUNT_TYPE is null.

To make use of the OR operator, specify the applyWhenOR function as a prefix to the constructed query string, as shown in the following code snippet:

SELECT * FROM DEMO_ACCOUNT_SUMMARY_DATA where 
	{{applyWhenOr({
				"parameterId" : "ACCOUNT_TYPE", 
				"EMPTY" : "ACCOUNT_TYPE is null", 
				"DEFAULT" : "ACCOUNT_TYPE = #{ACCOUNT_TYPE}"
				  })
	 }}

In the aforementioned code snippet, the Where Clause for the ACCOUNT_TYPE parameter is specified as null with the OR operator, e.g. OR ACCOUNT_TYPE is null.

Make sure to use the following query to pass more than one filter for the applied constructed query while configuring the SQL data source:

SELECT * FROM test_filter_table WHERE 1=1 {{applyListFilter({"colID":"COLUMN_NAME","filterValueKey":"FILTER_KEY"})}};

/* COLUMN_NAME is name of the column from table test_filter_table.
FILTER_KEY value can be of your choice but should have the same value as EXTRA_PARAMS_HDLR. */

Specify the appropriate additional view filters in the CT_READY.js file on the Studio Application WAR folder (D:\Canvas\apache-tomcat-8.5.9\webapps\expertctstudio\javascript\impljs\combine), as shown in the following sample code snippet

CWEH.registerHandler('WIDGET_ID', CWEC.EXTRA_PARAMS_HDLR, function(config) {
		var filterObj = {
		  "ADDL_VIEW_FILTERS": ct.encode({ "FILTER_KEY": ["services","goods","all"]})
		};
		ct.apply(config, filterObj);
		return config;
	});

To save the data source, click Save.

A confirmation message, indicating a successful save, appears.
Click Ok.