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 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 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¶

A view can have View Parameters in addition to regular columns. View parameters are useful when you want to build a view containing a where condition but you do not want to specify a fixed filter value on the view definition, as you want it to be dependent on the query instead.

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

1. A 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, add a “View parameter” to the view. When you add a view parameter to a view, that parameter will be part of the output schema and you can use it in the same places you use a regular field.

Using view parameters, you can modify our previous example so you can specify the limit of income at runtime. To do this, edit the view and follow these steps:

1. Click the Model tab and then, click beside “View parameters”. In this dialog, click Add new parameter to add the new view parameter.

   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.

2. Click 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.

CREATE VIEW items_return_details AS
SELECT p.prod_name, p.prod_category, r.reason_cat, r.comments
FROM item p LEFT JOIN returns r ON(p.product_id = r.product_id)

SELECT prod_name, prod_category, reason_cat, comments
FROM items_return_details
WHERE return_date >= addmonth(CURRENT_TIMESTAMP, -12)

SELECT p.prod_name, p.prod_category, r.reason_cat, r.comments
FROM PRODUCT p LEFT JOIN
(SELECT * from returns WHERE return_date >= addmonth(CURRENT_TIMESTAMP, -12)) r
ON(p.product_id = r.product_id)

SELECT prod_name, prod_category, reason_cat, comments
FROM items_return_details
WHERE num_months = 12

CREATE VIEW view_with_parameter AS
SELECT * FROM other_view
WHERE other_view.param = param
USING PARAMETERS(param : text)