DB Queries

Previous Top Next

DB Queries

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.

Please 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:

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.

dlg_edit_db_query

2.	Click the Append AND or Append OR button. This appends an empty criterion for the query (shown below).

dlg_edit_db_query_2

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.

5.	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 criteria

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:

=	Equal to
<>	Not equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
LIKE	Phonetically alike
NOT LIKE	Phonetically not alike
IS NULL	Is empty
NOT NULL	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. You first declare the parameter and its value, and then use the parameter in expressions. This causes the value of the parameter to be used as the value of that expression. The parameters that you add in the Edit Parameters dialog can be parameters that have already been declared for the stylesheet. In this case, the new value overrides the value in the stylesheet.

Parameters are useful if you wish to use a single value in multiple expressions.

Declaring parameters from the Edit DB Query dialog

To declare parameters:

1.	Click the Parameters... button in the Edit Database Query dialog. This opens the Edit Parameters dialog (see screenshot).

dlg_edit_params2

2.	Click Append or Insert .

3.	Type in the name and value of the parameter in the appropriate fields.

Please note: The Edit Parameters dialog contains all the parameters that have been defined for the stylesheet. While it is an error to use an undeclared parameter in the StyleVision Power Stylesheet, it is not an error to declare a parameter and not use it.

Using parameters in queries

To enter the name of a parameter as the value of an expression:

•	Type $ into the value input field followed (without any intervening space) by the name of the parameter in the Edit Database Query dialog.

Please note: If the parameter has already been declared, then the entry will be colored green. If the parameter has not been declared, the entry will be red, and you must declare it.

Re-ordering criteria in DB Queries

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.

dlg_edit_db_query_move

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 StyleVision so as to reflect the modifications to the DB Query.

Previous Top Next