 |
|
|
 | Table Designer |  | Database Objects Designers | Domain Designer |  |
Queries can select data according to specified conditions from one or more tables. A cursor is created as the result of a query. You can work with a cursor in the same way as with a table (e.g. you can open it in a grid, export data, etc.).
More information about queries can be found in the SQL language chapter.
Fixed queries are stored in the database (unlike variable queries, which are created during a client runtime using the API functions Open_cursor_direct). Between fixed and variable queries are dynamic queries. These are fixed queries that have a reference to some client language variable at some point in their definition. Fixed and dynamic queries can be created with the query designer.
Queries are created and modified using the interactive query designer. You can open the designer by selecting the Queries folder in the Control Panel and executing the Edit or New action. Users that find it easier to enter the query definition directly in text form (e.g. enter the SELECT SQL expression), use the action Edit in separate window or New Query in SQL. You can switch between the text and interactive designer at anytime. If the query design is saved with an error or it is too complicated for analysis, only the text editor will be used.
The query designer window is composed of three parts:
The designer also contains several auxiliary windows. When selecting a table to the FROM part of the query, the Add Table window appears. This offers a list of tables, queries, system tables and system queries. All of these object types are available for query construction.
The Lexical Elements window offers operators, predicates and other SQL expressions. If you want to use them, Drag & Drop the item on the grid window.
The Query Designer Properties window allows modifying the designer behaviour according to the user needs. The Statement format tab specifies whether the query definition (SELECT) will wrap a line, and the Options tab defines mainly prefixes of column and table names.
The query designer can be used by novices and advanced users alike. You need some experience with SQL queries to be able to properly use this tool. This manual cannot replace experience. The best way to learn the designer is to modify some of the example queries or create new queries (see the tutorial).
Basic actions for creating queries are presented on the following page.
A context pop-up menu for each tree item contains commands that can be called on each level.
Query root: The tree root contains these commands: Command Add ORDER BY: Adds the ORDER BY clause at the end of the query (cannot be used in other place, e.g. a subquery). Command Add LIMIT: Adds the LIMIT clause at the end of the query (the designer does not allow entering a LIMIT to a subqueries, even when it makes sense, e.g. in the WHERE part), Add query: Inserts another query to the design on the same level as the basic query and sets the relationship (UNION, INTERSECT or EXCEPT) between these queries (BEWARE: This is not a subquery).
SELECT: Contains only one command Add subquery: You can insert a subquery as one column (it must have one column and one row).
FROM: The data source may either be a simple table or a fixed query (command Add table), or a subquery formed by one query (e.g. query enclosed in parenthesis, command Add subquery) or more queries connected by a relationship (UNION, etc., command Add query expression) and also a JOIN of two tables (command Add join).
WHERE: The Add subquery inserts a subquery to the WHERE clause, but the subquery usually does not create a condition itself, you must then add it to the subquery in the grid of the Expression column.
List of topics:
| Table Designer | | Database Objects Designers | Domain Designer | |