Creating Conditions with the Compound Values Editor¶

All the dialogs to create and edit derived views (join, union, minus, intersect, flatten and selection) have a Where Conditions tab to add WHERE conditions to the definition of the view. When using the Simple condition mode of this tab, you have two options to create constants of compound types (register or array):

1. Write the value directly in VQL syntax. E.g. { ROW( 'B78596011' ), ROW( 'B78596012' ) }

   See section Conditions with Compound Values of the VQL Guide for more details about this syntax.

2. Or, use the Compound values editor. To open it, click on beside the right operand (only available for register or array fields).

As an example, we explain how to create a new Selection view that has a WHERE condition with a compound value. But first, we need to create a new base view:

1. Open the Web Service data source sales (created in the section Importing SOAP Web Service Sources) and click Create base view.

2. Click Create base view beside the operation getSumRevenueByTaxIds.

3. Rename the new base view to RevenueSum.

4. Rename its input attribute to clients and the return one, to totalrevenue.

5. The clients attribute is an array of registers of the type getsumrevenuebyids_in0. Virtual DataPort generates this type automatically when it creates the view.

6. Each element in this array will be a register of the type getsumrevenuebyids_in0_string, which is also automatically generated by Virtual DataPort.

7. Rename the element of the clients array to taxId.

8. Click Save () to create the base view.

Now, we will create a selection view over the base view RevenueSum, to obtain the sum of the revenue of the clients with tax ids B78596011 and B78596012. The constant operand of the selection condition will be created using the “Compound values editor”.

Follow these steps:

1. Right-click on the view RevenueSum in the Server Explorer and click on Selection on the menu New. The view RevenueSum has been added to the “Model” tab.

2. Click the tab Where Condition.

3. Click on to add a condition.

4. Select the field CLIENTS and the operator =.

5. Click to open the Compound Values editor.

6. Click twice on beside “Value” to create two elements of the type getSumRevenuebyTaxIds_in0_string.

7. Click on the new elements to expand them and edit the values of each array element.

8. Enter the value B78596011 in the field taxId of the first register and B78596012 in the same field of the second register (like in the figure below)

9. Click Ok to close the editor.

10. Click Save () to create the selection view.

Creating a value of type getSumRevenuebyTaxIds_IN0¶

Parameters of Derived Views¶

There are scenarios where you may need to create a derived view with a Where condition, use Group by and you do not want the fields involved in the condition to be Group by fields. In this scenario, the Where condition has to be static and cannot be changed at runtime.

For example, let us say that we have two views:

1. A base view CLIENT with these fields: name, income and state.

2. And a view WEALTHY_CLIENT_BY_STATE defined as:

   CREATE VIEW WEALTHY_CLIENT_BY_STATE AS
   SELECT state, COUNT(*)
   FROM client
   WHERE income > 1000000
   GROUP BY state
   

There is a limitation in the second view: the limit of income to consider a client wealthy is static. Therefore, we have to know this limit before creating the view. If we wanted to change this limit at runtime, we could remove the WHERE condition and add the field income to the GROUP BY fields. But then, we would have to add this field to the GROUP BY clause and we might not want to do that.

To avoid this problem, you can add a “View parameter” to the view. View parameters are a special type of fields:

• They are added to the output schema of the view

• You can use them in the Where condition of the derived view, as any other field

At runtime, to set the value of a parameter you have to add its value in the WHERE clause of the query as if it was a regular field (i.e. VIEW_PARAMETER = <value>). By doing this, you are setting the value of the parameter.

Note that when you use parameters in the WHERE clause, the Server treats them differently because it does not use their value to filter the output. Only to set the value of the parameters.

Following our previous example, you can modify the derived view WEALTHY_CLIENT_BY_STATE to add a view parameter that allows you to modify at runtime the limit of income. To do this, edit the view and do the following:

• Click on the Model tab and click beside “View parameters”. In this dialog, click Add new parameter to add the new view parameter (see Parameters of Derived Views).

  Set the name of the parameter to wealthy_client_income_limit, select the type long and set the default value of the parameter to 1000000.

• Click on Where Conditions and set the condition income > wealthy_client_income_limit.

Adding a view parameter¶

The view will have a new output field wealthy_client_income_limit, which you can use to change the condition at runtime. For example,

SELECT *
FROM WEALTHY_CLIENT_BY_STATE
WHERE wealthy_client_income_limit = 250000

Will take into account clients with an income greater than 250,000.

As the parameter has a default value, if you execute

SELECT *
FROM WEALTHY_CLIENT_BY_STATE

The query will take into account clients with an income greater than 1,000,000.

For example, following the example above, let us say that you execute the following:

SELECT *
FROM WEALTHY_CLIENT_BY_STATE
WHERE wealthy_client_income_limit = (
   SELECT MAX(income_limit) FROM state_limit WHERE state = 'CA'
)

In this case, the result of the subquery (SELECT MAX(income_limit) FROM…) will not be assigned to the parameter wealthy_client_income_limit so the query will return the accounts with an income greater than 1,000,000, which is the default value of the parameter.

If the parameter did not have a default value, the query would return an error immediately.