Page Comparison

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

...

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. TRANSACTION ACCOUNT DETAILS.

   Note
   The Connection Name is a non-editable field.

   
   Image RemovedImage Added
   
   

3. Enable the Is SQL? Toggle option.
   Image Removed
   Image Added
   
   

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

   Code Block
   language sql
   SELECT * FROM DEMO_ACCOUNT_SUMMARY_DATA where {{applyWhen({ "parameterId" : "transactionTypeACCOUNT_TYPE", "EMPTY" : "TXNACCOUNT_TYPE is null", "SAVINGS" : "TXN_TYPE = 'SAVINGS'", "DEFAULT" : "TXNACCOUNT_TYPE = #{transactionTypeACCOUNT_TYPE}" }) }}

   
   

   Note

   In the aforementioned code snippet, SAVINGS serves the ACCOUNT_TYPE serves as the parameter of TXN_TYPE (Transaction Type)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. SAVINGS 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. SAVINGS 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.

   
   Image RemovedImage Added
   
   

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:

   Code Block
   language sql
   SELECT * FROM DEMO_ACCOUNT_SUMMARY_DATA where {{applyWhenAnd({ "parameterId" : "transactionTypeACCOUNT_TYPE", "EMPTY" : "TXNACCOUNT_TYPE is null", "SAVINGSDEFAULT" : "TXNACCOUNT_TYPE = #{TXNACCOUNT_TYPE}", "DEFAULT" : "TXN_TYPE = #{transactionType}") }})

   
   

   Note
   In the aforementioned code snippet, the TXNWhere Clause for the ACCOUNT_TYPE (Transaction Type)  parameter is specified as null with the AND operator, e.g. AND TXNACCOUNT_TYPE is null.

   
   Image RemovedImage Added
   
   

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:

   Code Block
   language sql
   SELECT * FROM DEMO_ACCOUNT_SUMMARY_DATA where {{applyWhenOr({ "parameterId" : "transactionTypeACCOUNT_TYPE", "EMPTY" : "TXNACCOUNT_TYPE is null", "SAVINGSDEFAULT" : "TXNACCOUNT_TYPE = #{TXNACCOUNT_TYPE}", "DEFAULT" : "TXN_TYPE = #{transactionType}" }) }) }}

   
   

   Note
   In the aforementioned code snippet, the TXNWhere Clause for the ACCOUNT_TYPE (Transaction Type)  parameter is specified as null with the OR operator, e.g. OR TXNACCOUNT_TYPE is null.

   
   Image RemovedImage Added
   
   

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

   
   

   Note

   Make sure to use the following query to pass more than one filter: Code Block language java 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 Code Block language js 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.