A DB query enables you to query the records of a table displayed in Authentic View. A query is made for an individual table, and only one query can be made for each table. You can make a query at any time while editing. If you have unsaved changes in your Authentic View document at the time you submit the query, you will be prompted about whether you wish to save all changes made in the document or discard all changes. Note that even changes made in other tables will be saved/discarded. After you submit the query, the table is reloaded using the query conditions.
|Note:||If you get a message saying that too many tables are open, then you can reduce the number of tables that are open by using a query to filter out some tables.|
To create and submit a query:
1.Click the Query button for the required table in order to open the Edit Database Query dialog (see screenshot). This button typically appears at the top of each DB table or below it. If a Query button is not present for any table, the designer of the StyleVision Power Stylesheet has not enabled the DB Query feature for that table.
2.Click the Append AND or Append OR button. This appends an empty criterion for the query (shown below).
3.Enter the expression for the criterion. An expression consists of: (i) a field name (available from the associated combo-box); (ii) an operator (available from the associated combo-box); and (iii) a value (to be entered directly). For details of how to construct expressions see the Expressions in criteria section.
4.If you wish to add another criterion, click the Append AND or Append OR button according to which logical operator (AND or OR) you wish to use to join the two criteria. Then add the new criterion. For details about the logical operators, see the section Re-ordering criteria in DB Queries.
Expressions in DB Query criteria consist of a field name, an operator, and a value. The available field names are the child elements of the selected top-level data table; the names of these fields are listed in a combo-box (see screenshot above). The operators you can use are listed below:
Not equal to
Less than or equal to
Greater than or equal to
Phonetically not alike
Is not empty
If IS NULL or NOT NULL is selected, the Value field is disabled. Values must be entered without quotes (or any other delimiter). Values must also have the same formatting as that of the corresponding DB field; otherwise the expression will evaluate to FALSE. For example, if a criterion for a field of the date datatype in an MS Access DB has an expression StartDate=25/05/2004, the expression will evaluate to FALSE because the date datatype in an MS Access DB has a format of YYYY-MM-DD.
Using parameters with DB Queries
You can enter the name of a parameter as the value of an expression when creating queries. Parameters are variables that can be used instead of literal values in queries. When you enter it in an expression, its value is used in the expression. Parameters that are available have been defined by the SPS designer in the SPS and can be viewed in the View Parameters dialog (see screenshot below). Parameters have been assigned a default value in the SPS, which can be overridden by passing a value to the parameter via the command line (if and when the output document is compiled via the command line).
To view the parameters defined for the SPS, click the Parameters button in the Edit Database Query dialog. This opens the View Parameters dialog (see screenshot).
The View Parameters dialog contains all the parameters that have been defined for the stylesheet in the SPS and parameters must be edited in the stylesheet design.
The logical structure of the DB Query and the relationship between any two criteria or sets of criteria is indicated graphically. Each level of the logical structure is indicated by a square bracket. Two adjacent criteria or sets of criteria indicate the AND operator, whereas if two criteria are separated by the word OR then the OR operator is indicated. The criteria are also appropriately indented to provide a clear overview of the logical structure of the DB Query.
The DB Query shown in the screenshot above may be represented in text as:
State=CA AND (City=Los Angeles OR City=San Diego OR (City=San Francisco AND CustomerNr=25))
You can re-order the DB Query by moving a criterion or set of criteria up or down relative to the other criteria in the DB Query. To move a criterion or set of criteria, do the following:
1.Select the criterion by clicking on it, or select an entire level by clicking on the bracket that represents that level.
2.Click the Up or Down arrow button in the dialog.
The following points should be noted:
•If the adjacent criterion in the direction of movement is at the same level, the two criteria exchange places.
•A set of criteria (i.e. criterion within a bracket) changes position within the same level; it does not change levels.
•An individual criterion changes position within the same level. If the adjacent criterion is further outward/inward (i.e. not on the same level), then the selected criterion will move outward/inward, one level at a time.
To delete a criterion in a DB Query, select the criterion and click Delete.
Modifying a DB Query
To modify a DB Query:
1.Click the Query button . The Edit Database Query dialog box opens. You can now edit the expressions in any of the listed criteria, add new criteria, re-order criteria, or delete criteria in the DB Query.
2.Click OK. The data from the DB is automatically re-loaded into Authentic View so as to reflect the modifications to the DB Query.