1. Home
  2. Advanced
  3. Advanced Functions: SQL Statement Manager

Advanced Functions: SQL Statement Manager

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 TIP: This window requires a large display to function . If you don’t have one, you should.

Warning 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 Add

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 Delete

Delete the object and all its components.

Rename Rename

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.

The window has 3 tab panes.

Dynamic Queries 
Sort Sets 
Shortcuts 

Dynamic Queries

SQL Statement Manager - Dynamic Queries tab pane left side

SQL Statement Manager - Dynamic Queries tab pane right side

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.

Back to top

Sort Sets

SQL Statement Manager - Sort Sets tab pane

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.

Back to top

Shortcuts

SQL Statement Manager - Sort Sets tab pane

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.

Back to top

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.

Updated on May 22, 2020

Was this article helpful?

Related Articles

Leave a Comment