Queries

Select the parent table

Simply choose from the tables in the Select Parent Table drop down list. In the following figure, the CUSTOMERS table was selected as the parent table and the Has Child Tables checkbox has been checked thus all the other tables are on display.

Add child tables to the query

1. Tick the Has Child Tables checkbox to view all other tables in the project

2. Select the table from the All Tables window and click on >> button to add the table in the Child Tables window

3. Repeat step 2 until all child tables required in the query has been added to the Child Tables window.

Remove a Child Table

1. Select the table from the Child Tables window and click on the << button to remove the table. A confirmation dialog box will appear as shown below.

2. Click YES to continue with removing the child table.

To remove all the child tables simply untick the Has Child Tables checkbox..

Removing a child table will also remove any relationships and relationship keys associated with that table. Though the Output and Order Fields will have to be manually removed.

Change Parent Tables

When changing the Parent table of the query; this will reset all the data in the query, from the Tables to the Order Fields tab of the query.

Simply select the new table from the Select Parent Table drop down list and click Yes to the confirmation dialog box that will appear as shown below,

Establishing a Relationship

To establish a relationship, follow these simple steps,

1. Define the tables that are to have a relation in the Tables tab of the query i.e. parent and child tables .

2. Go to the Relationship tabs, select the source table from the Source Table combo box and the destination table from the Destination Table combo box. Note that once selected, the fields in the tables will appear in the Source and Destination Columns window respectively.

3. Select the common fields from the Source and Destination Columns window and click the >> button to add the Relationship Keys chosen. Repeat this step until all the common fields have been added as a Relationship Key.

The relationship between the two fields will be displayed in the Relationship Keys window, while the resulting relationship between the tables will be shown in the Relationships window.

The data contained in the relationship keys column will be the link between the tables and must therefore be of the same data type. When two columns are selected as a relationship key but are not of the same type, BrightBuilder will issue a warning shown below and will disallow the inclusion of this relationship key.

Removing Relationships

When removing a relationship from the Relationships window, all the Relationship Keys will also be deleted.

To do this, select the Relationship and click the << button in the Relationships section, notice that the Relationship and Relationship Keys have been deleted.

The Relationship Keys can also be deleted one at a time by selecting the Relationship Key fields and clicking the << button in the Relationship Keys section. Repeat this step until all unnecessary Rels-keys have been deleted.

Modelling Table Relationships for Synchronisation

When synchronising tables from the server to the client. It is best to make sure that the table joins have correct table relationships.

Table joins in Bright Software Mobility Suite are treated as a binary tree. The parent table is the root and the child tables are the leaves. The relationship traversal is from the parent to the child table. Only one-to-one and one-to-many relationships are supported. Many-to-many relationship must be decomposed in one-to-many relationships using a supplementary joint table.

In the illustration above, the parent table has a one-to-one relationship with the right-hand child table. The child table acts as a lookup table for the parent table. Each record in the parent table has a corresponding record in the child table and that these records are linked through a common column field in the two tables. The column involved in the relationship from the parent table does not need to be the tables primary key. In order for the one-to-one relationship to be enforced, the column involved in the relationship from the child table must be the tables primary key or a column with the unique constraint.

The most common type of relationship is the one-to-many relationship. In a one-to-many relationship, a record in the parent table can have many records in the child table, but a record in the child table has only one matching record in the parent table as shown from the illustration above. The tables are usually linked through the primary key from the parent table and the foreign key from the child table.

Inserting an Expression

1. To specify a condition for a field, first select the table from the Table drop down list. In the earlier example, select the CUSTOMERS table.

2. Select the table field from the Column drop down list and enter the expression in the Value cell for that field.

3. Then click on the Insert Expression button to include the expression in the Condition window, note that the structure of the condition is TableName.ColumnField = Value or TableName.ColumnField = ? if its a parameterised query.

The following image has a condition ITEMS.ITEM_ID = 1, note the change of text in the Insert Expression button.

Additional conditions can be entered for the same field or different fields. To be able to insert more expressions in the Condition window, the operator BrightBuilder will use to combine the conditions when the query is run must first be added. BrightBuilder combines expressions using either the AND or the OR operator.

Click on the Insert AND button to add an AND operator in the condition window, this will return records that meets all the criteria specified in the Condition window.

Click on the Insert OR button to add an OR operator in the query condition, this will return records that meets any of the criteria specified in the Condition window.

Expressions you inserted can also be blocked by selecting the expression line from the Condition window and clicking on the Block Expression button. Blocking expressions in BrightBuilder means grouping the expressions; this will place parentheses between the expressions and will also create expression precedence when the query is run.

A single expression or a group of expressions can be blocked. When blocking a group of expressions, it must have the correct condition format, meaning it has expressions and matching operators in between the expressions.

Follow the steps in inserting an expression in the Condition window to add more expressions to the query conditions.

• To unblock an expression , select the expression inside the parentheses and click on the Unblock Expression button. The Unblock Expression button will only be enabled if an expression or a nested expression inside the parentheses was selected, and no operators are present.

Parameters can also be used in conditions ; this will in turn create a Parameterised Query. A parameterised query is one in which the expression terms are variable and can therefore be specified at run time. The power of a parameterised query is that it allows the retrieval of different result sets depending on the value of the parameter passed to it.

• To add a parameter, simply select the Table and Column Name the expression will be based upon and tick the Parameterised checkbox to enable the Name and Description fields. Type an appropriate parameter name and description and click Insert Expression.

Note that the expression line format is Table.Column Name = ?, where ? is the value assigned in to the query parameter. Query parameters can be assigned through user input in the forms or through form expressions. A valid parameter name begins with a letter and may be followed by letters, digits, and the underscore sign ( _ ), and must not contain more than 64 characters.

If an invalid character is typed in the parameter name, the cell border will become red and the Insert Expression button will not be enabled until a valid name is entered.

In entering values for the date time format, it will accept the system date time format of the users location, for example Australian date time format is dd-MM-yy.

Table column fields can also be checked for null values by ticking the NULL checkbox and using the equal or not equal operator to compare the column values with null. The image below is a query condition that checks if the DESCRIPTION field of ITEMS table is equal to null.

Updating an Expression

To change the Column Name or Value or Parameter Name of the expression,

1. Highlight the expression; notice that the Insert Expression button becomes the Update Expression button.

2. Make the necessary changes in the Expression group box and click Update Expression.

Deleting an Expression

To delete an expression or a block of expression in the Condition window, simply select the expression and click the Delete button.

To select multiple lines of expression, whether for blocking or deleting, simply highlight the lines by selecting the first line then press SHFT+down_arrow or SHFT+mouse_click until the last line to delete or block.

Column Functions

The following are several column functions that are available in BrightBuilder numeric query output fields. Numeric fields are table columns that have int and double as its data type. The column functions will not be enabled unless the column is a numeric data type.

• Average - arithmetic mean of the column values.

• Sum - total of all column values.

• Min - the minimum value within the column values.

• Max - the maximum value within the column values.

To specify a column function,

1. Simply select the numeric column field and tick the function to be used on that column.

2. Enter an alias for this column function in the Column Alias field.

When using the Column Functions it is necessary to always provide an alias to the column name, BrightBuilder will issue the following message if no alias was provided for the column name.

Click OK to return command to BrightBuilder, the column field with the column function but no column alias will not be added to the query output fields.

Column Alias

It is possible to specify a custom name for an output column by using the Column Alias field. Aliases are used to provide more descriptive names or to shorten the column names or to avoid conflict with the same column names already included in the output fields. It does not change the name of the column, it only specifies another name that can be used to refer to the column.

To create an alias for the output column field,

1. Highlight the column name in the Query Table Columns window

2. Enter the alias in the Column Alias field.

Alias names are also subject to the object-naming rules for BrightBuilder. If an invalid alias name was entered, BrightBuilder will issue the following warning message and will discontinue addition of that column to the query output fields.

Reserved words are acceptable as aliases, though it is not recommended as reserved words may create conflicts in the SQL statements used for database creation and access. The following message will be displayed when a reserved word is used as an alias.

Simply click OK or close the dialog box to return command to BrightBuilder.

Adding the column to the Output Fields window

Once it has been determined which columns to use as output to a query, after specifying the column functions and the column alias, if there is any, the user can now click on to add the highlighted column name to the query Output Fields window. Use to remove a column field. Use to add ALL column fields of the query table to the Output Fields window or use to remove ALL columns from the Output Fields window.

Including Count

Another feature of the query Output Fields tab is the ability to include a count of the query record set.

1. Tick the Include Count checkbox to invoke the following dialog box,

2. Enter the alias name for the count and click OK. Output Fields window will include an entry, COUNT(*)(<alias name>)

Returning Distinct Records

Queries can also return distinct records. Checking the Distinct Records Only checkbox does this. To understand the concept of distinct records, given the following table example below,

The MOVIE_SESSION table shown is a list of movies showing on Friday at Joes Cinema Complex.

A query to return all the movie names showing for that day, would return the following result set.

But if the Distinct Records Only feature of the query was used, the result set will be,

Moving Output columns

The order of the output columns can be changed to set the order in which the columns will be retrieved and displayed.

Simply select the Output Column fields and click Move Up to move the column up or Move Down to move it down until the output fields are in the order desired.

State Order for Output Fields

The column outputs selected from the Output Fields tab will be displayed in the Order Fields tab. The Order Fields tab is where to specify the sort mode of the records to be displayed. Records can be sorted in ascending or descending order depending on the column specified in the Order By Fields window. Columns are ordered from top to bottom.

BrightBuilder allows complex sorts, meaning it is possible to sort records in ascending order by some fields and in descending order by others.

The order in which the columns are sorted determines how the data will be organized. After the output fields have been inserted in the order fields with its corresponding sort mode, the fields can be moved to reconfigure the sort order. Simply highlight the order field and click the Move Up or Move Down to position the order fields.

To remove the order field, simply select it from the Order By Fields window and click on the button. This will return the column name to the Output Fields window.

To add output columns to the Order by Fields window,

1. Highlight the column from the Output Fields window in the Order Fields tab, shown below,

    

2. Select the sort mode from the Sort radio boxes; default value is Ascending.

3. Click to add the output column to the Order By Fields window.

4. Repeat the above steps until all the output columns required in the sort have been added.

Using the Order Fields tab of the query will not alter the physical structure of the table, it only specifies the way the records will be retrieved and displayed on a form.