To create a Query in Opus, you must use the Database Query action. A Query is simply a method of extracting particular records from a database that you want to display within an Opus publication.
A standard method of creating a query is to write an SQL statement. SQL stands for Structured Query Language, it is a standardized language that selects and rejects records from a database depending on whether they match the criteria you outline in the statement. SQL statements can be complicated to construct so Opus has provided an SQL Wizard that will generate a query for you by simply filling in boxes.
The SQL Wizard can only be found in the Database Query action – it is used to select records from a Database.
Note:
The Database Query action is the lynchpin in connecting to an external database in Opus Pro – see Database Query action for more information. This section only describes how to use the SQL wizard.
On the first page of the wizard tick the Select data for Updating/Deleting option if you want to change the contents of an existing record in the database. If you only want to read information from a database and display it in Opus, leave this option unticked. Click on the Next button to continue.
Note:
If you want to use the Update Current Record action or Delete Current Record action, then this option must be ticked. The Insert New Record action will work if this option is ticked or not ticked.
On the second page of the wizard click on the down arrow next to the Table box to list all of the tables in the database. Select the table that contains the records you want to use in your publication. When you select a table, the table’s field names are automatically displayed in the Field panel below the Table box.
Note:
If you ticked the Select data for Updating/Deleting option on the previous page of this wizard, some tables within the database may not appear in the popup list. The reason for this is that the table or tables that do not appear have no primary key, or what is often termed, a ‘unique constraint’. In other words, none of the fields within the table have been set to uniquely identify a record; this means that two records in a table could contain the same data. You should ensure that at least one field in a table is set as a primary key; this means that two records cannot have the same value in that field and is therefore uniquely identifiable.
Tick the checkbox beside the name of each field in the Field panel you want to use in the Opus publication, and then click the Next button to continue. By default, all checkboxes are ticked.
Note:
If your publication does not require information held in a field, either because you don’t want to display it to the user or the information is irrelevant for this publication, untick the checkbox beside the relevant field name.
The third page of the wizard is used to set the selection criteria of your query i.e. which records from the table will be part of the Query.
To select ALL the records in the current table simply leave the Where Field box set to [None], then click the Next button to continue. When you select All the records, the fourth screen in the Wizard will not be displayed.
If you only want to show particular records in your database, based on some type of selection criteria, use the Where Field box to enter the name of the field in the database that you want to use as the comparison field. In other words, the data contained in the selected field for each record is compared with the data entered in the Compare To panel and records are selected or rejected, based on whether they matched your criteria.
Click on the down arrow next to the Where Field option to list all of the fields in the table.
Note:
The list will contain all the field names from the table whether you ticked them or not on the previous page of the wizard (see point 4 above) – this is because you may want to use a field as a comparison but not show the field in your publication.
Use the Operator panel to select the required Operator from the list. By default, the Is Equal to operator is selected.
Note:
An Operator is a method of comparing the value of the Where Field with the value of Compare To, to test if it should be included within the current Query. Most of the operators are self-explanatory, for example: (i) Is Equal to would be used when you wanted the contents of the records to exactly match the value you enter in the Compare To panel, such as finding all the records with the department name Support; (ii) the Is Greater Than operator can be used to find records above a certain value, such as all ages that are greater than 20.
The Is Like and Is NOT Like operators need a little more explanation – see Using the LIKE Operator for more information.
Use the Value option in the Compare To panel if you want to type the value you want to compare with the value contained in the field entered in the Where Field box.
Note:
You can type in the Value box any letter, number, word or phrase e.g. 23, SMITH, John Doe, etc.
Use the Variable option in the Compare To panel if you want the value contained in a variable to be compared with the value contained in the field entered in the Where Field box. Click on the down arrow next to the Variable box to list all of the variables in your current publication.
Note:
It is the contents of the variable that is compared e.g. if a user variable named User_Name is entered, the value contained in the variable is compared with the value contained inside the field entered in the Where Field box.
Once you have set up the Where Field, Operator and Compare To options, click on the Next button to continue.
The fourth page in the wizard allows you to add another selection criteria to your query. For example, you may want to find all prices above £30 AND all prices below £100; alternatively, you may want to find all employees in the Sales department OR the Marketing department.
Note:
This page will NOT appear if you set [None] in the Where Field box of the previous page (see point 6 above).
Select the No More Comparisons, AND or OR option on the fourth page of the wizard, click on the Next button to continue.
Note:
No More Comparisons – means that you do not want to add another query to your SQL statement. Selecting this option and pressing the Next button will move immediately to the fifth page of the wizard.
AND – means that you want to add another query to your SQL statement. Selecting this option and clicking the Next button will return to the previous page of the wizard where you can then add another selection criteria (i.e. points 5 to 13 above). When you use the AND comparison, it means that in order for a record to be added to the Select List, the record must match both the first selection criteria AND the second.
OR – also means that you want to add another query to your SQL statement. Again, by selecting this option and clicking the Next button, you will return to the previous page of the wizard where you can then add another selection criteria (i.e. points 5 to 13 above). How this differs from the AND comparison is that the record will be added to the Select List if it matches either the first selection criteria OR the second criteria.
The fifth and final page in the wizard allows you to set the sort order for your query.
Use the Sort By panel to select name of the field in the table by which you want to sort the records. Click on the down arrow next to the Field box to list all of the fields in the table. By default, the [None] option is selected – this will create a Select List based on the order in which records that matched your selection criteria were added.
Use the Ascending option if you want the list arranged alphabetically from A to Z. Alternatively, use the Descending option if you want the list arranged alphabetically from Z to A. By default, the Ascending option is selected.
Use the Then By panels if you want to sort the records by more than one field. For example, records that are initially sorted by a surname field could then be sorted by a forename field.
Note:
Records can be sorted by a maximum of three fields.
Click on the Finish button to apply your changes – the SQL wizard will close automatically and you will be returned to the Database Query action.