SQL Statement Manager
Reference Contents > Advanced Functions > SQL Statement Manager
The SQL Statement Manager window allows you to inspect and add SQL objects in the Extras/Statements.db database. This objects include Dynamic Queries, Sort Sets, Where Shortcuts, Join Shortcuts and Statement Shortcuts. They are are processed by Caliach Vision when running reprots for example and they are done in this way so they can be witten differently for alternative server engines to do the thing which may involve different syntax for different SQL dialects.
TIP: This window requires a large display to function . If you don’t have one, you should.
WARNING: This window does not operate in a typical manner with a Save and Revert button. Any changes are applied immediatly to Statements.db on leavind the field.
Common to all tab panes on the window are the following:
|
Field |
Description |
|---|---|
|
Show for Database |
Select to show statement entries for the selected Database Engine. When you change your selection some elements of the designs may change and/or fields will become available for edit. |
|
Button |
Action |
|---|---|
|
|
Add a duplicate of the object. You will be asked to enter a new unique name that can not start with Cal_ which is reserved for Caliach. |
|
|
Delete the object and all its components. |
|
|
Click to rename the object. You will be prompted for a new unique name that can not start with Cal_ which is reserved for Caliach. |
Add
Delete
RenameThe window has 3 tab panes.
Dynamic Queries
Sort Sets
Shortcuts
Dynamic Queries
Dynamic queries provide the main mechanism for constructing SQL select statements for collecting data from the database. They are typically used in selection windows.
|
Field |
Description |
|---|---|
|
Queries |
List of all defined Dynamic Queries. |
|
Name |
The name of the dynamic query. |
|
Description |
A description for the dynamic query. |
|
Disable message |
If a messege is entered the dynamic query will not be used. Instead, this message will be displayed. This is used to disable dynamic queries until problems are resolved. |
The following make up the Select Statement constructions.
|
Field |
Description |
|---|---|
|
Order |
List of all select statements in the query. A Query can contain any number of select statements that are performed in order. |
|
Select Distinct |
This checkbox determins if a SELECT or SELECT DISTINCT is performed. |
|
Schemas and Columns |
This grid contains elements that determin the columns returned in the select statement. |
|
Order |
The order of the schema or column. |
|
Schema |
A named schema class will use all columns defined by that schema. You can not have both a schema and column on the same line. |
|
Column |
A single named column or an expression, e.g. count(*). You can not have both a schema and column on the same line. |
|
Comments |
Programmer comments for column. |
|
Alias |
An alias name that will be used as the results column name. Not applicable to schemas. |
|
Use index |
If used with a schema and the Db allows, you can direct the optimiser to use particular indexes with the relevant table associated with the schema. |
|
Comments |
Programmer comments for use index. |
|
Description |
Description for the schema or column that can be helpful for troubleshooting. |
|
Message |
A disableing message that if entered prevents the schema or column being used and reports the message. This is used to disable dynamic queries until problems are resolved. |
|
From clause |
FROM clause (excluding the FROM keyword). This can be a Join Shortcut if expressed as =[JoinShortcutName]. |
|
Comments |
Programmer comments. |
|
Where initial |
WHERE initial clause (excluding the WHERE keyword). This can be a Where Shortcut if expressed as =[WhereShortcutName]. Where Parts will be appended to the WHERE initial clause. |
|
Where parts |
This grid contains elements that determin the where clause parts in the select statement, depending on user choices at runtime. |
|
Order |
The order of the where parts. |
|
Exclude Calculation |
The Omnis calculation if computed to kTrue will lead to the clause being ommitted from the where construction. |
|
Operator |
The pefixed operator for the clause, e.g. AND. |
|
Comments |
Programmer comments for the where clause part. |
|
Clause |
The WHERE clause making up the where part. |
|
Comments |
Programmer comments for operator. |
|
Message |
A disableing message that if entered prevents the where part being used and reports the message. This is used to disable dynamic queries until problems are resolved. |
|
Compound operator |
The operator used for compound select statements. |
|
Comments |
Programmer comments. |
|
Extra appended after Selects |
Extra SQL text appended to the single or compound Select Statements. |
|
Comments |
Programmer comments. |
|
Button |
Action |
|---|---|
|
Test Build and DB Prepare |
This tests the normal build process and then a prepare of the resulting SQL and reports the results. NOTE: This prepare is applied to current main database connection and NOT the database backend selected on the right. |
Sort Sets
Sort Sets are definitions for the ordering of data and it’s presentation on reports, etc.
|
Field |
Description |
|---|---|
|
Sort sets |
List of all defined Sort Sets, typically for report selection windows. |
|
Name |
The name of the sort sets. |
|
Description |
A description for the sort sets. |
|
Message |
If a messege is entered the sort set will not be used. Instead, this message will be displayed. This is used to disable sort sets until problems are resolved. |
|
Sort options list |
List of options for the selected sort set. Select to maintain details on the right. |
|
Sort Option Details |
Each sorting alternative has SQL parts to it and report sort parts to it so that the data is returned from the server in an appropriate order and that the report is set to recognise this for subtotalling. |
|
Description |
A description for the sort set option that will be used on reports. |
|
Order By clause |
The ORDER BY clause without the ORDER BY keyword. |
|
Comments |
Programmer comments. |
|
Order |
The order of the sort column. |
|
Column |
The sort column name. |
|
Descending |
In descending order by default. |
|
Uppercase |
Sort by uppercase characters. |
|
New page |
New page on reports when subtotal changes. |
|
Subtotal |
Subtotal on reports when column value changes. |
Shortcuts
Named standard shortcuts for where clauses, join clauses and full SQL statements.
There are 4 sections to this tab pane.
Where Shortcuts
|
Field |
Description |
|---|---|
|
Where Shortcuts |
List of all defined where clause shortcuts. |
|
Name |
The name of the where shortcut. |
|
Description |
A description for the where shortcut. |
|
Disable message |
If a messege is entered the where shortcut will not be used. Instead, this message will be displayed. This is used to disable where shortcuts until problems are resolved. |
|
Where clause |
SQL text clause for the where shorcut. |
|
Comments |
Programmer comments. |
Join Shortcuts
Join Shortcuts substitute into FROM clauses to a SQL statement.
|
Join Shortcuts |
List of all defined join clause shortcuts. |
|---|---|
|
Name |
The name of the join shortcut. |
|
Description |
A description for the join shortcut. |
|
Disable Message |
If a messege is entered the join shortcut will not be used. Instead, this message will be displayed. This is used to disable join shortcuts until problems are resolved. |
|
Join clause |
SQL text clause for the join shorcut. |
|
Comments |
Programmer comments. |
Statement Shortcuts
Statement Shortcuts are complete SQL statements.
|
Statement Shortcuts |
List of all defined Statement Shortcuts. |
|---|---|
|
Name |
The name of the statement shortcut. |
|
Description |
A description for the statement shortcut. |
|
Disable message |
If a messege is entered the statement shortcut will not be used. Instead, this message will be displayed. This is used to disable statement shortcuts until problems are resolved. |
|
Statement in full |
SQL text clause for the statement shorcut. |
|
Comments |
Programmer comments. |
|
Button |
Action |
|---|---|
|
Test Build and DB Prepare |
This tests the normal build process and then a prepare of the resulting SQL and reports the results. NOTE: This prepare is applied to current main database connection and NOT the database backend selected on the right. |
Special Functions
|
Field |
Description |
|---|---|
|
Start from date and time |
The starting date time that synchronisation data will be collected. |
|
Button |
Action |
|---|---|
|
Export |
Click to Export all data to an XML file. |
|
Re-Organise and Analyse |
Runs a routine to seek out lost orphans and remove them, check and correct synchronisation columns and then analyze the database. |
|
Build Sync Data |
Builds a syncronisation table from the date and time entered. |
Compiled in Program Version 5.10. Help data last modified 6 Jan 2015 06:46:48.00. Class wSqlStateMng last modified 2 Oct 2017 04:24:20.
Reference Contents > Advanced Functions > SQL Statement Manager
