6.9.2 The Query Builder Dialog Box
|Note: Query Builder is only available for Origin 32-bit. If you only have Origin 64-bit installed on your machine, you can only edit SQL and import database data in SQL Editor. You can run Add or Remove Files and choose Modify to install both 32-bit and 64 bit Origin.
Query Building Pane
Building queries is a complex skill often only available to a company specialist. Origin can make use of pre-built queries (such as in an Access database) or create them with the Query Builder dialog. If a pre-packaged query is available you can use the Add Object context menu of the Main pane and select the query from the Views tab. Note that 'Provider' and 'Dialect' (see Setting: Options) should be consistent for proper operation.
You can construct a new query by choosing the source objects and derived tables, creating links between them and selecting the data for output. This pane also allows you to edit the properties of links and objects. For example, you can define an alias for an object here.
Adding an object or derived table to this pane can be done by right-clicking on the empty area of the pane and choosing Add Object or Add Derived Table. If a derived table is added, a corresponding tab will be created. You can edit the sub-query related to the derived table visually in the same way as you edit the main query. Pressing the maximize button on the object representing this derived table will also bring you to the corresponding tab.
You can also drag Tables and Views (queries) from the right panel to the Main panel.
Check the box to the left of a field name, if you want to output the field. If all the fields of the object are desired to be included in the output, you should click the checkbox at the left of the asterisk item of the object. To remove a field from the output, just clear the box before the field name.
To edit the property of a source object, you can right-click on it and choose Properties from the short-cut menu. The editable properties of a source object vary, depending on the server. But you can always define and change the alias.
To join two objects, select a field in one object and drag it to the corresponding field of the other object. A line is drawn between the two fields. This creates an "inner join". To create other types of joins, right-click on the join line and choose Properties from the shortcut menu. To delete a join, choose Remove from the shortcut menu or click on the join line and press Delete.
Above the Query Building Area, there is a page control. You can use it to switch between main and subqueries freely.
Union Sub-Query Control
The icon near the top-right corner of the query building pane is the union sub-query handling control. With this control, you can add new union sub-queries and perform operations.
To add a new union sub-query, right-click on the control and choose New Union Sub-query from the short-cut menu. Then you will see one more icon and an Uniting control between them, as shown below:
When there is more than one sub-query, this short-cut menu also allows you to:
- Enclose a sub-query with brackets.
- Move a sub-query or bracket forward or backward
To configure the uniting operation, you may right-click on the Uniting icon. Then you will see another short-cut menu with which you can choose the uniting operator from one of the following:
In the Column pane, you can select the fields to be output, set the aliases for the fields, and setup the sorting and grouping.
There is a check box before each field name. If it is checked, the corresponding field will be included in the query result. Therefore, if you want to remove a field from the query output, you may just clear the checkbox before the field name. This is especially useful when you want to keep a field in the column pane merely for the purpose of setting up grouping or sorting, but exclude it from output.
The Expression column shows the name of the fields which have been added to the current query. You can always use the drop-down list to select a field and add it into this pane. In addition, you can create a sub-query by typing "(Select)" in this column.
To define aliases for the output fields, you can type the aliases in the Alias column.
The Aggregate column allows you to select an aggregate functions (Avg, Count, Sum, ...;) for the field.
The Sort Type column and Sort Order column are used to setup the sorting of the query result. Sort type could be either Ascending or Descending. The sort order column is used when there is more than one filed to be sorted. It specifies the order in which the fields are sorted.
The Group column assists you in setting up the grouping. If the check box in column is selected, the current field will be added to the "Group By" expression. Also, the Criteria for column will be shown. If you wish to set up criteria for the grouped data, you can select "For Groups" in the Criteria for column, and then enter the criteria in the Criteria column.
In the Criteria column, you can define the criteria which is used with the Where expression (when either the Group check box is cleared or Criteria for is set to "For Values" while the Group check box is selected) or the Having expression that used in combination with Group By (when the Group check box is selected and Criteria for is set to "For Groups"). Note that the expression (the name of the field) should be omitted. Suppose that you want to use the following criterion in the query:
- where (field>10)
You type the following in the Criteria column:
This column also allows you to create sub-queries. Right-click on this column, and choose Insert Sub-Query. A sub-query will be added, and you can switch to it by clicking the tab of the sub-query added to the Query Building pane.
If you want to specify several criteria, use the Or...; columns. The criteria will be connected using the OR operator in the query statement. This will allow you to set up compound condition.
Query Tree View Pane
In the Query Tree Pane, you can view the entire query organized like an outline. With it, you can switch to any part of the query rapidly.
This pane allows you to add any source objects, which could be tables, views or procedures, to the current query. To add a source object, you can either double-click on it or drag it to the Query Building pane.
The preview pane allows you to preview the result and assess your current query. You can toggle this pane on and off by the Show Preview button or No Preview button.
With the Preview pane on, clicking the Preview button will update the data in the Preview pane.
You may click this button to view the SQL query string. A dialog will pop up to display the string. You can also modify the string and apply the changes with the dialog.
By clicking this button, you can apply the query and import the result into the active worksheet