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.
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.
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. |
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. |
The following examples show query builder commands.
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:
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.
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:
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.
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-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.
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 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.
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.
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.
Example: Run JavaScript in the Command Window - How to run a simple V8 JavaScript script in the Command window.
SQL Example: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas operation by cutting and pasting from what the Edit Query button automatically generates.