Query Wizard - Examples

This article provides practical examples and step-by-step instructions on how to use some of the Query wizard features.

Copy/Move

Scenario: The SalesTable table must be added to the Sales Confirmation Query. The SalesTable table must be located between the CustConfirmJour and DlvMode tables.

You can simply add SalesTable to CustConfirmJour. However, this would result in a structure in which child tables are at the same level as the parent table, which could cause issues regarding joins and Ranges.

A better approach is to have a nested structure.

Click the DlvMode table and move it by using the Copy/Move button.

Group By

To enable the Group by function, follow these steps:

• Navigate to Lasernet > Reports and select your query report.

• Open the Query wizard and select Next to navigate to the Tables form.

• Select the Query or SubQuery you would like to use Group by on, expand the PROPERTIES menu, and select Yes for the Group by field.

When you enable the Group by function on a Query, each field in your Query or SubQuery will require a Group by function.

Ensure you remove the RecId fields from your tables when using Group by. This is because using Group by on the RecId will cause the Connector to create a new group for every RecId it encounters in the data. Since RecIds are meant to be unique, you will get every line returned, and grouping will not occur properly.

Group by Function

The following custom Query will return information on the inventory transactions in the system, their corresponding inventory dimensions, such as color and size, and their display order.

The Group by function is applied to the following fields:

• InventTrans.ItemId

• InventDim.InventColorId

• InventDim.InventSizeId

• EcoResSize.DisplayOrder

The detail records for the InventTrans table will be grouped for each unique combination of the GroupBy fields: ItemId, InventColorID, InventSizeID, and DisplayOrder.

In the following example, multiple detail records with ItemId M0030, color White, size 6, and display order 106 on the left-hand side are grouped into a single detail record on the right-hand side.

Sum Function

The Sum function is applied to group InventTrans.Qty.

This will calculate the sum of the field Qty for each ItemId group.

Using the example where multiple detail records with ItemId M0030, color White, size 6, and display order 106 are grouped into a single detail record, the Qty fields for the two records on the left-hand side are summed in the Qty field on the right-hand side.

Row Number Function

RowNumber returns the number of rows within a partition. Row numbering begins with 1 and starts over again once InventDim.InventColorId changes (the OVER Partition by property). The row numbers are ordered by the OVER order by field, InventDim.InventColorId. This can be used for patterns in Lasernet Developer.

The RowNumber function is applied to the field InventDim.InventColorId.

The InventTrans.ActivityNumber field is used as a placeholder for the RowNumber function. A placeholder field does not return the field value, but the value defined by applying the Group by function.

RowNumber returns the number of rows within a partition. Row numbering begins with 1 and starts over again once InventDim.InventColorId changes (the OVER Partition by property). The row numbers are ordered by the OVER order by field, InventDim.InventColorId. This can be used for patterns in the Lasernet Developer application.

Using the example where a query is using Group by and contains two records with the ItemId M0030 and color White (as seen on the left-hand side), the RowNumber field provides a row number of 1 to the first record and 2 to the second (as seen on the right-hand side).