Command Window - Query Builder

The Query Builder tab in the Command Window for SQL provides a handy set of features to help write queries faster.

 

 

The Query Builder facilitates writing query text in the Commands and Queries pane.    For additional discussion see the Command Window topic.

 

Basic Workflow:

 

  1. Drag and drop tables that will be used into the Tables pane.

  2. Find statements or other query parts in the Templates pane by scrolling the pane and by entering text into the filter box to reduce choices to those likely of interest.

  3. Double-click on the statement or other query part in the Templates pane to add to the query text.

  4. Highlight fields or other parts of the template and then double-click on items in the Tables pane or the Templates pane to add field names or other text to the query without manual keyboarding, and the risk of typographical errors.

  5. Use commands such as Insert Field List to insert an entire table's worth of field names at once or Insert Definition to create an entire table at once.

  6. Repeat the preceding steps as needed, using manual keyboarding as required to build the desired query.

 

Query Builder Commands

A context menu choice pops up when right-clicking on an item.  For example, right-clicking on an item in the Templates  pane will provide a context menu with the Insert Code choice.  Right-clicking on a table name in the Table pane will allow choosing Insert Field List.

 

Copy Component and Paste

Copying a component in the project pane and then pasting into the Commands and Queries pane generates a query that will create that component.   See the Example: Automatically Generating CREATE Queries topic.

Filter Boxes

Entering text into a filter box will show only those items within that filter box's pane that contain the text.  Case is not significant.

Drag and drop

Dragging and dropping a component into the Tables pane provides an entry for the component name and a list of fields and indexes in the table associated with that component. Most frequently used with tables but also works for drawings, images, labels and maps.  Dragging and dropping a component like a map that does not have a single table associated with that component will create an entry for the name of the component plus a place-holder [Result] computed field.

Double-click

Double-clicking on an item in the Templates pane or the Tables pane will add that item to the text in the Commands and Queries pane at the current position of the cursor in that pane or replacing highlighted text.

Insert Code

Add this template as text to the query. Equivalent to double-clicking the item to add it to the query text.   A context menu choice on Templates pane items.

Insert Definition

Insert SQL to create this table in the query.  A context menu choice for components in the  Tables pane items.  Choosing Insert Definition for a component other than a table will write SQL to create the table associated with that component, but using the name of the component.

Insert Field List

Insert a list of all fields in the table into the query.   A context menu choice for components in the  Tables pane items.   Convenient when you want many fields, but not all, in a SELECT statement.  Use Insert Field List and then just delete the fields not wanted.

Insert Name

Add this item from the Tables pane as text to the query. Equivalent to double-clicking the item to add it to the query text.   A context menu choice on Tables pane items.

Ctrl-A, Ctrl-C, Ctrl-V, Ctrl-X

Operate in the Commands and Queries pane, the usual Windows Select All, Copy, Paste and Cut keyboard shortcuts.

 

Commands within the Commands and Queries Pane

In addition to SQL the following commands work within the Commands and Queries pane.   See the Command Window topic for additional discussion.

 

!

Press the exclamation mark ! on the main menu to run the query.

Alt-Enter

Press Alt-Enter to run only the highlighted text in the Commands and Queries pane.

? expression

Evaluate the expression following the ? question mark and print the result in the Log.   For example, ? 1 + 2 when evaluated will result in the number 3 being printed to the log.   This capability may be used to prototype portions of text that will be incorporated into queries, for example, trying out a function or other construction.   Usually we enter the ? question mark followed by the expression we want to try out, we highlight the question mark and expression and then we press Alt-Enter to run just the highlighted text.   For example, ? SystemCpuCount() reports the number of CPU cores available in the system, giving a value of 8 given hyper-threading on a four-core processor.

!fullfetch

Toggle more verbose information reporting on/off in the Log to include the time required, number of records involved and size of data involved.  Again, we just highlight the !fullfetch text and press Alt-Enter to run just the highlighted text.   !fullfetch works both with Manifold queries as well as queries executed within an external query engine.

!manifold

For all subsequent statements use Manifold SQL and the Manifold query engine.

!native

For all subsequent statements use whatever is the native SQL and query engine used by the data source in which the command window was launched.   Encountering a $manifold$ directive within a statement will override the !native command. !native is the default.

 

Examples

The following examples show query builder commands.

Copy Component and Paste

In the Project pane (shown undocked above the Command Window), copy a component with a Ctrl-C.

 

 

Paste with a Ctrl-V into the Commands and Queries pane and Manifold will write the SQL to create that table:

 

Filter Box

Enter the text intersect into the filter box for the Templates pane and only those templates that have that text in their names or within their hierarchies as an option will appear in the pane.

 

 

Only those functions which have the string intersect in their templates will appear.

 

We can enter any of the text shown in the query builder, and not just the name of the function, into the filter box.  We can enter argument types, or the result type if we want to filter by that.

 

 

For example, the result of a function is shown within angle < > brackets after a colon.  If we enter : <area that will filter down the list to only those functions which generate areas as their resulting geometry.

 

 

If we want to see all functions which generate tables as a result, we can enter : <tab into the filter box.    We might notice that some of the functions are coordinate conversion functions that take a <converter> as an argument, so to see which functions create a converter object we can search for that.

 

 

Enter : <conv into the filter box to see which functions can be used to create a converter object.       We could, of course, use the filter box to search for any of the arguments used, by filtering for the term in angle brackets.

Drag and drop

In the Project pane (shown undocked above the Command Window), drag and drop a component into the Tables pane.

 

 

The schema for that component's table will be added to the Tables pane, including the names of fields and indexes:

 

Insert Definition

Right-click the name of a table in the Tables pane and choose Insert Definition.

 

Manifold will add the SQL to the query required to create that table.  That is a convenient way of creating starting text that may be edited to create a similar, but differently-named table or with different fields.

 

Insert Field List

Right-click the name of a table in the Tables pane and choose Insert Field List.

 

Manifold adds the names of the fields in that table, separated by commas, in a form suitable for use within SELECT and similar statements.  The illustration below has had a new line entered to wrap the long list of fields to fit into the screenshot.

 

The Insert Field List command is very useful when we want to use many, but not all fields from a table with very many fields.  We simply add all the fields and then delete those we do not want.

 

Double-click Template

Double-clicking a template item adds it to the query.

The template text appears in a form that provides a reminder for the syntax for that statement.  We can then highlight portions and replace with further double-clicks in the Templates pane or Tables pane.

 

Double-click Field

We highlight text to be replace and double-click the name of a field in the Tables pane.

 

The double-clicked name of the field will replace the highlighted text.  If no text was highlighted, the name of the field will appear at the cursor position in the query.

 

Double-click Table Name

Double-click the name of a table to add it to the query.

 

The double-clicked name will replace any highlighted text.   If no text is highlighted the name will appear in the query at the cursor position.

 

 

Notes

Keyboard Shortcut  - Keyboarding the first few letters of an item in a query builder list selects it, scrolling the list to that item. Case is not significant.  In the tables list, the initial left bracket [ character can be omitted.   For example,  keyboarding pro will  successfully select [Provinces].

 

Why an Insert Code command?  - Experienced users will always just double-click on a Templates pane item to add it to the query text.  So why does Manifold provide a context menu command that does the same thing?  The primary reason is that users may not know or may have forgotten about double-clicking but many users from other applications will try a right-click to see what pops up, if anything, for choices of what to do with a given item.   Providing a context menu also creates a framework to which may be added additional commands in the future such as links to documentation for that item.

 

MFD_ is reserved - All names starting with mfd_ (case not significant) are reserved for use by the system.  Names for fields, indexes, constraints, tables, components other than tables, properties... everything. Do not name anything beginning with mfd_ or MFD_ or in any upper or lower case combination of those characters.   mfd_id is a reserved name for a field in tables, to be used only for the identity field that Manifold maintains as an automatically unique value field.

See Also

Tables

 

Queries

 

Scripts

 

Edit - Join

 

Command Window

 

Log Window

 

Example: Create and Run a Query -  See how the different parts of a command window operate when creating and running SQL queries.   Includes use of the Log tab as well as the ?expression and !fullfetch commands.

 

Example: Refer to a Table within a Data Source -  Within a query, how to refer to a table that is in a data source.

 

Example: Switching between Manifold and Native Query Engines - How to use the !manifold and !native commands to switch a query in the Command window from use the Manifold query engine to whatever query engine is provided by a data source.

 

Example: Automatically Generating CREATE Queries - How to use the Command window to automatically generate SQL in the form of CREATE queries that create a desired component.

 

SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.