Queries

Queries are components that contain statements written in the database language SQL. Opening a query by double clicking on it will open the query in an editing window to show the SQL statement it contains.

 

images\btn_proj_run.gif Clicking on the query to highlight it in the project pane and pressing the Run button will execute the query and will display the results in the opened window. If there are any errors in the query text (such as typographical errors or SQL syntax errors) the query text will be opened in a query window and the first error located will be highlighted.

 

Queries are usually used to create tables that contain records and fields from other tables in the project. The SQL statement specifies what the table should contain and how the contents should be displayed. The simplest form of query chooses columns and records from one table and displays them.

 

Queries are easy to create. Use File - Create - Query to create a new query and then edit it by right clicking on it and choosing Open. Enter the SQL statement desired and close the query. To make it easier for us to write queries, Manifold will automatically color different parts of the query text in different colors.

 

Whenever the query is run it will execute the SQL statement and will display in the resultant table window whatever table results from the SQL statement. The resultant table may be used like any other.

 

SQL is an industry standard database query language used to fetch records from tables and to present those records with the fields desired. The result of an SQL query is what appears to be a new table. SQL may also be used to create new tables by combining existing tables, and may even be used to alter the structure of the database and the data it contains.

 

Note: For very simple, one line queries with drawings an alternative to using queries is to use the Query toolbar .

 

To create a new query:

 

1. Select File - Create Query command within main menu, or

2. Press the Create button in the project pane toolbar and select Query, or

3. Right click within the project pane or on any component and select Create - Query.

4. Open the query for editing as described below.

5. Write the SQL statement desired. Changes are made directly to the query. Make a copy of the query if you wish to be able to abandon the changes.

6. Close the query window when done. Run the query as described below.

 

To edit a query:

 

1. Select the query component in the project pane and press the Open button on the project pane toolbar, or

2. Right click the component and select Open, or

3. Double click the component.

4. Make any changes to the SQL text contained in the query.

 

To execute (run) a query:

 

1. Select the query component in the project pane and press the Run button on the project pane toolbar, or

2. Right click the component and select Run, or

3. Open the query for editing and select Query - Run from main menu.

 

To export a query as a table:

 

1. Select the query component in the project pane and press the Run button on the project pane toolbar, or

2. Right click the component and select Run, or

3. Open the query for editing and select Query - Run from main menu.

4. Choose File - Export - Table to export as a table.

 

Example

 

Let's begin by importing the Orders table from the sample Nwind.mdb database.

 

images\sc_sql_intro_01.gif

 

The Orders table has many fields related to orders in the sample database. We will create a table that shows the country names for all orders where the freight is greater than 50.

 

Using File - Create - Query we insert a new query into the project and then open it for editing by double clicking it in the project. We will write a simple SQL query:

 

images\sc_sql_intro_02.gif

 

SQL is not case sensitive (except for string literals and string operations), nor does SQL care if we write our statements in one line or in multiple lines or if we use indents. Extra parentheses are fine as well. We could write our queries with the terms all jammed together in one line or we can write nice, neat, understandable SQL statements like the above. It is always wise to establish a regular style and stick to it to enhance readability of SQL both for our colleagues as well as for future review by us.

 

Manifold query windows will automatically color different parts of an SQL query with differently colored text. This helps avoid typographical errors. If we would like to change the colors used by Manifold to color query syntax, the Tools - Options - Colors page includes entries for Query Comment, Query Keyword, Query Number, Query Operator, Query String and Query Text colors. The Tools - Options - Fonts page restricts the choices for the Query font to fixed size fonts.

 

images\sc_sql_intro_03.gif

 

The query appears in the project pane using the standard Microsoft icon for queries (these are also called "views" in Access). If we select the query in the Project pane and click the Run button in the toolbar, the query opens as a table:

 

images\sc_sql_intro_04.gif

 

The table created by the query works just like a regular table. For example, if we right click onto the Ship Country column head and choose Sort Ascending from the context menu we can sort the table by this column:

 

images\sc_sql_intro_05.gif

 

We can also use many other table window commands such as the Best Fit commands.

 

SQL is a straightforward, easy-to-use query language.

 

images\sc_sql_intro_06.gif

 

If we would like to see additional fields in our table we can add them to the query as shown above.

 

images\sc_sql_intro_07.gif

 

The result of running the new query is a table with three fields instead of just one.

 

Types of Queries

 

There are two main types of queries: select queries and action queries:

 

·      Select queries show results in tables and always begin with the SQL word select. Select queries choose data from one or more tables and display it in tabular form. Select queries do not change any data in the tables.

·      Action queries do not display data but rather perform a task when they are run. Action queries change data in existing tables by appending, updating or deleting records. Action queries can also create new tables. Action queries start with words other than select. For example, an update query starts with the word Update.

 

See the Simple Queries topic for an introduction to simple select queries that display data from one table.

 

See the Queries Using Multiple Tables topic for instructions on creating queries that combine fields from multiple tables.

 

See the Action Queries topic for information on queries other than select queries.

 

Dot Nomenclature

 

"Dot" nomenclature allows us to refer to exactly a desired field within a given table even when other tables in our project might use the same field name in a different way. Dot nomenclature is simply the table name followed by a period "." and then the field name:

 

Customers.ContactName

 

The above refers to the ContactName field in the Customers table. If we are writing simple queries that involve only one table we need not use dot nomenclature. We could simply write ContactName as in the examples above. However, if we are working with more than one table we can always use dot nomenclature to clear up any ambiguities about which field is intended.

 

Autocompletion

 

Manifold will offer to autocomplete SQL functions and other parts of the query, popping up lists of key words and other useful items, to help guide us in writing the query. See the Autocompletion in Queries topic.

 

Names in Brackets

 

Some database management systems (including Manifold tables) allow field names and table names to contain the space character and other non-alphanumeric characters. Such field names should be enclosed in [ ] square brackets when written in queries. Simple field names that do not contain spaces or other unusual characters need not be enclosed in brackets.

 

To preserve maximum capability to export data to a wide variety of database systems we suggest the use of field names that do not contain spaces or other unusual characters.

 

Dot nomenclature and names in brackets can be combined. For example, one could write

 

Select * from [Mexico Table]

where [Mexico Table].BUSES_1991 > 3000;

 

Case Sensitivity

 

SQL itself is not case-sensitive, but string literals and string operations are. Hence, comparing a pair of string values which only differ by case for equality will return false. If you want to compare string values ignoring their case, use the UCase function. 

 

Selection and SQL

 

Because many SQL queries begin with the word Select it is tempting to think of them as adding items to the selection. Although this is "selection" in the database sense of the word, the actual records and the objects with which they are associated may or may not be selected in the Manifold sense of the word depending on the setting of the Automatically select query records option in the Tools - Options - Miscellaneous dialog.

 

The default setting of this option is to automatically select records reported by a query. This makes it easy to use a query to select objects in a drawing. If automatic selection of query records is not desired, uncheck the option. If the option is not checked, records resulting from a query will simply be displayed in a table. We can then select them as desired. For example, we could choose Edit - Select All to select all the records in that table.

 

For reference information on SQL see the SQL Reference Guide information beginning with the SQL in Manifold System topic.

 

VBScript Style Operators and Functions

 

Manifold SQL supports use of numerous VBScript operators and functions. For example, LikeX is similar to the standard SQL Like operator but uses VBScript style syntax to match regular expressions.

 

The RegExp function returns values based on regular expressions. It transforms strings in a manner similar to the Manifold Edit - Replace dialog with the Regular Expressions checkbox turned on, so that:

 

RegExp("abc", "b", "d") returns "adc",

RegExp("abc", "[a-b]", "d") returns "ddc",

RegExp("abc", "[a-b]", "") returns "c",

RegExp("ab c", "([a-z]*) ([a-z]*)", "$1+$2") returns "ab+c",

RegExp("ab c", "([a-z]*) ([a-z]*)", "$2 $1") returns "c ab",

 

For more on VBScript style operators and functions see the Expressions topic in the SQL Reference Guide.

 

Manifold SQL and ANSI Syntax Option

 

Manifold's SQL is designed to be compatible with Microsoft SQL as implemented in the Microsoft "Jet" database engine. Manifold SQL is intended to be a superset of Jet SQL with features supporting ANSI SQL as well. SQL syntax as documented within the Microsoft Jet SQL Reference for Access 2000 is a practical approach to Manifold SQL.

 

Manifold SQL has also been extended with special functions to use the spatial and geocoding capabilities of Manifold. Note that the geocoding extensions to SQL will be available only if either the Manifold US street address geocoding database is installed or if Microsoft MapPoint has been installed. See the Spatial Extensions and Geocoding Extensions and Raster Extensions topics for a list of extensions to SQL in Manifold that allow creation of SQL queries that make spatial comparisons, work with rasters or employ the geocoding engine for work with street addresses.

 

In addition to being Microsoft Jet-like, Manifold's SQL engine may be switched into an alternate mode where it parses SQL queries using pure ANSI syntax. To do so, open the query and in the View - Properties dialog check the Use ANSI-compatible syntax box. It is also possible to automatically set the Use ANSI-compatible syntax option for all new queries by checking the Make new queries ANSI-compatible option in the Tools - Options dialog.

 

When the ANSI option is turned off, line comments start after an apostrophe (') or double dashes (--), the names of tables and columns can be enclosed in square brackets ([ ]) or backward apostrophes (`), strings are enclosed in quotes (") and times are enclosed in quotes (") or hashes (#).

 

When the ANSI option is turned on, line comments start after double dashes (--), the names of tables and columns can be enclosed in square brackets ([ ]) or quotes (") or backward apostrophes (`), strings are enclosed in apostrophes (') and times are enclosed in apostrophes (') or hashes (#).

 

Clearly, the safest course given that users might change the settings is to use double dashes (--) to mark line comments and to enclose the names of tables and columns in square brackets ([ ]) and to enclose times with hashes (#).. The main difference between having the ANSI option off or on is that with it off strings are enclosed in quotes (") and with the ANSI option on strings are enclosed in apostrophes (').

 

Lookup Values and CStr and CAST

 

Invoking CStr on a lookup value or using CAST to convert the value to a string returns the descriptive name of the lookup value.

 

Example

 

We have a table T with a lookup column Region with values East and West. We want to select all records with the value of Region being East. We can do this with the following query:

 

SELECT * FROM [T] WHERE CStr([Region]) = "East";

 

Example

 

We have a drawing D and we want to select all areas it contains. We can do this with the following query:

 

SELECT * FROM [D] WHERE CAST([Type (I)] AS TEXT) = "Area";

 

Use cache Option

 

In the View - Properties dialog for a query the Use cache option allows us to control the caching behavior of queries.

 

The Use cache option applies to table queries, such as those using SELECT and TRANSFORM. When this option is turned on, the query may cache the resulting table between invocations provided its text and parameters have not changed. This is obviously good for performance when lots of data is involved in the query.

 

When the Use cache option is turned off, the query forcefully re-computes itself (re-computing any dependent queries and refreshing any linked tables) each time it is run from the project pane or from the query window. By default, the option is turned off.

 

The default setting of the Use cache option may be specified in the Tools - Options - Miscellaneous dialog via the Make new queries cached option.

 

Queries and Linked Components

 

In addition to using queries as dynamically refreshed tables, one can also import or link queries as drawings, images or surfaces to create linked components. See the Linked Drawings topic as well as the Queries and Images or Surfaces topic for details.

 

Queries and Virtual Tables for Images and Surfaces

 

Queries may use data from images or surfaces by using virtual tables for these components. See the Virtual Tables for Images and Surfaces topic for details.

 

Tech Tip

 

images\btn_run_query.gif We may simultaneously open a query for editing with Open in one window and also run it in another window with the Run button. Any changes to the query's SQL statement made in the editing window will take effect in the opened viewing window when we next press Run. Note that for the Run button to work the query component must be highlighted in the project pane.

 

images\sc_queries_dual_01.gif

 

Suppose we create a query using data from Mexico Table, the table associated with our sample map of Mexican provinces. We can open the query in an editing window. We can also press the Run button to execute the query and open the results in a table window. The screen shot above shows the editing window, the table window and the project pane. (Windows have been resized to very small size to fit into the screen shot).

 

images\sc_queries_dual_02.gif

 

We can change the 3000 value to 4000 in the SQL statement and then press the Run button. The table window will be updated with the new results of the query.

 

images\sc_queries_dual_03.gif

 

If we change the 4000 to 5000 we can press the Run button again to update the table once more. Note that there is no need to "save" the SQL statement in the editing window. Changes to the query in the editing window take effect whenever the Run button is pressed.

 

As a convenience for users, if a query is open at the same time as an editable query window and also as a table window reporting the results of the query being run, the Windows dialog will append the postfix (Table) to the latter. For example, if we have a query called MyQuery and it is open both as an editable query window and also as a table window reporting the results of running the query, the Windows dialog will show two windows open, one called MyQuery and the other called MyQuery (Table).

 

Keyboard Shortcuts for Editing Queries

 

In addition to the usual Windows keyboard shortcuts query windows support key combinations for doing advanced syntax-aware editing tasks:

 

CTRL-J

Display a list of auto-complete suggestions for the context word.

CTRL-K

Comment selected text.

CTRL-SHIFT-K

Uncomment selected text.

CTRL-M

Convert selected text to lower case.

CTRL-SHIFT-M

Convert selected text to upper case.

CTRL-Y

Multi-level Redo.

CTRL-Z

Multi-level Undo.

CTRL-]

Check the character near the cursor and, if it is a bracket character, jump to the matching bracket.

CTRL-SHIFT-]

Check the character near the cursor and, if it is a bracket character select the text between the bracket and the matching bracket.

CTRL-Space

Auto-complete the context word.

 

Recognized bracket combinations are ( and ), [ and ], { and }, ' and ', " and " and # and # (used to delimit dates in queries).

 

Query windows are unusual in that they support multi-level Undo / Redo. One can CTRL-Z (Undo) backwards through many changes and CTRL-Y (Redo) forwards to redo many Undo operations.

 

Formatting Columns

 

Queries also use the Formatting Columns capabilities of table windows, inheriting the format style used for the column in the table. Styles should be chosen to provide clarity of results.

 

Users are sometimes surprised to see numbers they expect to be values like 1.1 appear in a query or other table as 1.09999942779541, even if a floating point type is used. That is a result of the fundamental architecture of computers combined with the choice of format style used.

 

Floating-point values have finite precision. Some values, such as 1, 2, or 0.25 can be represented precisely, but most can not. For example, neither 12.6 nor 13.7 can be represented precisely. To see what really hides behind a value shown as 12.6, set the format style of the relevant column to 12.34 and increase the number of decimal digits to 10. Try the same for 13.7.

 

If we subtract one approximation from another, as in the case of an SQL query, we get an approximation of the result so that an expected result such as 1.1 may appear as 1.09999942779541.

 

The default format style uses a Microsoft Windows API which makes some approximations, such as 12.6000..., look round but leaves others unaffected. There is some logic behind that API, but that logic is obviously not applicable to all cases. To provide fine control over desired appearance is one reason why Manifold allows setting the format style and the number of decimal digits explicitly.

 

Notes

 

See the Query Templates and Sample Queries example topics for samples of queries.

 

For very simple, one line queries with drawings an alternative to using queries is to use the Query toolbar .

 

The idea of relational databases originated in a 1969 IBM research report written by Dr. E.F. "Ted" Codd, who invented the relational model of databases and introduced it to the world. SQL originated as SEQUEL (for "Structured English Query Language") in 1974 at the IBM San Jose Research Laboratory in a project led by Donald Chamberlin.

 

Just about everyone in computing thinks that “SQL” is an acronym for “Structured Query Language.” That’s not true, despite the historical origin of the language, as the ANSI standard defining SQL officially names it “Database Language SQL.” Experts will also point out that SQL is not structured, it is used for more purposes than just asking about things (the conventional meaning of the English word “query”) and it is technically not a “language” in the Turing sense of the word.

 

According to ANSI, “SQL” should be pronounced by saying the letters as in “ess cue ell.” However, many people prefer to pronounce the term as the English word “sequel,” especially when referring to Microsoft’s SQL Server DBMS product since “sequel server” is more alliterative and easier to say than “ess cue ell server.” It is very common to use both pronunciations, even in the same sentence, as in “This ess-cue-ell works in Sequel Server but not in Oracle.”

 

When running SQL within Manifold queries, one is using the Manifold SQL engine. When executing SQL within the Database Console one is using whatever SQL is the native SQL of the external database system. One should be aware that SQL implementations in various databases systems can contain bugs. If an SQL bug occurs within the Database Console, the bug should be tracked down with the vendor of the external database system being used.

 

Queries made with query components as well as queries made with the Query toolbar execute within Manifold on the local machine and thus must fetch the content of involved tables from their respective data sources. This frees the user from the hassles of different dialects of SQL as supported by different drivers, allows querying data sources that do not support any SQL at all and allows using spatial, geocoding and other extensions implemented within Manifold.

 

To make a query execute on a database server using that server's own SQL, create a view on that server and link that view to the Manifold project as you would link a table.

 

Queries made within the Database Console run within the database server.

 

See Also

 

Autocompletion in Queries

SQL in Manifold System

SQL Reserved Words / Index

Expressions

View - Refresh Data

Spatial Extensions

Geocoding Extensions

Using SQL to Select Map Objects

Sample Queries

Query Templates