Example: Create and Run a Query

In this example we see how the different parts of a Command Window operate when working with queries, including use of the built-in query builder to assist writing queries.

 

Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query.  When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system.  See the THREADS command for more info.

 

We base our example on the mfd_meta system table which is guaranteed to be in every Manifold project.    We begin by launching Manifold with a new, blank project.    Choose View - New Command Window - SQL.  A command window opens up with the expected display of three panes.

 

 

We open the System Data folder in the Project pane and drag and drop the  mfd_meta table into the tables pane.   Note that the tables pane now shows the structure of the table, including names of fields and indexes.

 

 

We can write SQL into the command pane free hand or we can use templates to help.   For tips on editing query text, see the Editing Queries, Scripts and Comments topic.

 

To reduce the very long list of templates to a list of only those templates of interest, we enter select into the filter box above the templates pane.   That will restrict the templates pane to only those templates that have the character string select in their names (case does not matter).

 

 

We then double-click onto the SELECT statement to add it to the query.

 

 

 

Double-clicking the template loads the text for it into the command pane.  

 

The text entered is just a template, a guide for writing a correct SELECT statement. As entered so far it is not yet a correct SELECT statement.   We can now edit that text to make it a functioning SQL statement with correct syntax using the names of fields and tables in our project.   We will use automatic text entry of the names of fields and tables to avoid keyboarding errors.

 

 

In the command pane we highlight the text we would like to replace, for example, with the name of a field from the table we will use.

 

 

We can then double-click on the desired field name in the tables pane and the name of the field will automatically replace the highlighted text.    This is the preferred way of entering complex field names to ensure avoiding typographic errors.

 

 

To replace the generic template <table> with the name of the table we want to use we first highlight the text to be replaced and then in the tables pane we can then double-click on the name of the table to be used.   

 

 

The table name replaces the highlighted text.  

 

At this point we have written a fully-functioning query.    If we intend to have more than one SQL statement in the command pane we should add a semi-colon character ; to terminate the SQL statement we have created.   But if there is only one statement we can be slackers and leave the semi-colon off and the query will still run OK.

 

To run the query we press the  !  button in the main toolbar.  We can also press the F5 key or choose View - Run.

 

 

The query runs and the display automatically switches to the Results tab to show the table that results from the query.

 

 

If we have any doubts that the query ran correctly we can double-click on the mfd_meta table in the Project pane to open it and see that there are indeed four records in that table and the values of the Name field for those records are indeed as reported in the query results.

 

If we have prior SQL experience we know that the "table" produced by the SELECT statement we wrote is basically just a report - it is not an actual table created anywhere in the project and it cannot be edited.   If we want to create an actual table that contains one field, Name, with values for the four records from the mfd_meta table we could have written a SELECT ... INTO statement to take the results of the query and put them INTO a newly-created table under some name.

Example: Using the Log Tab

Continuing on from the previous example, let's take a look at what the Log tab does.  

 

 

In the prior example, we had run the query and saw the results of that query in the Results tab.

 

 

Clicking the Log tab shows us the log of activity, which is like a history of what we've done.   

 

 

 

In the display above it shows what is currently in the command pane and in the log pane below it shows the startup banner and the history of activity, in this case just the one SQL statement we ran.

 

The startup banner at the beginning of the log tells us we are executing manifold SQL and reminds us we can use the ?expression and !fullfetch options.

Using ?expression

The ?expression option simply evaluates whatever expression follows the question mark ?.     If we replace the query currently in the command pane with the expression

 

? 5+Sin(2)

 

and then we press the ! button the system will evaluate the expression and report the result in the log pane.

 

 

We normally would use the ?expression option to try out expressions involving functions or more complex operators before incorporating them into queries but the facility nonetheless works just fine for simple arithmetic as well.

Using !fullfetch

To see what !fullfetch does we will first Copy the SELECT query from the log pane and then Paste it back into the command pane to replace the expression we tested.   That is a very handy feature when we want to repeat a previously used query or expression.

 

We will then enter and highlight the !fullfetch command.

 

With the !fullfetch command highlighted we press Alt-Enter  to run it.

 

 

The log window reports that fullfetch mode has been turned on.   From now on what we do in this session of the Command window will be logged with fullfetch mode turned on.

 

We can now delete the !fullfetch command so we can just run the query by itself without having to highlight it and run it separately to avoid also running the !fullfetch command again.

 

We run the query again by pressing the ! button, and then we press the Log tab to see the log report.

 

 

When we run the query again we see that now the log pane provides a more verbose report giving the time required, the number of records involved and the size of the data involved.

 

To turn fullfetch off, we can once more execute the !fullfetch command in the command pane.   Executing the !fullfetch command toggles fullfetch mode on and off.

 

Notes

CPU Parallelization - Manifold automatically runs parallel for internal Manifold tasks and for Transform pane templates and similar operations, using all of the CPU cores available in your system in parallel.   When writing queries manually using the Command Window make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in your system.

See Also

Tables

 

Queries

 

Edit - Join

 

Editing Queries, Scripts and Comments

 

Command Window

 

Command Window - Query Builder

 

Example: Export the Results of a Query - How to export the results table of a query for use in other applications.

 

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: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.