Applying constructed queries for SQL Data Sources

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 Sigma Studio:

1. Right-click the existing Connection and select the Create Data Source option. For detailed information on creating Connections, refer Create a Database Connection.
   
   
   
   The Create New Data Source page appears.
   
   
   
   

2. In the Data Source Name field, provide a unique data source name, e.g. ACCOUNT DETAILS.

   The Connection Name is a non-editable field.

   
   
   
   

3. Enable the Is SQL? Toggle option.
   
   
   
   

4. In the Data Query field, provide the appropriate constructed queries, as shown in the following code snippet:

   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.

   
   
   
   

5. 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.

   
   
   
   

6. 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.

   
   
   
   

7. To save the data source, click Save.
   
   
   
   
   A confirmation message, indicating a successful save, appears.
   
   

   Make sure to use the following query to pass more than one filter:

   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. */

   Add the below code in CT_READY.js file

   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;
   	});

8. Click Ok.