SQL Example: Using Selection Query Functions

 

SQL Example: Using Selection Query Functions

In this example we illustrate use of SQL functions for selection.   If you have teleported into this topic without reading the basics, selection in Manifold is a different thing than use of the SELECT statement in SQL.    Please review the main Selection topic before proceeding.

 

SQL functions for selections come in two versions for each function.   One version of the function takes a table as an argument, and is used for selections in  tables and components created from tables.    Each selection function has a second version, with "window" in the name of the function, that takes a window name as an argument as well as a table name.   This latter form is typically use for selections in queries and components created from queries, where each individual window can have its own selection.

 

The example data we use in this topic is a drawing of Mexico that shows provinces as areas.   The drawing is called Mexico and it is based on a table called Mexico Table.   

 

il_selection02_10.png

 

In this example, the drawing in use in an open drawing window, also called Mexico, where the drawing is the only layer.  

 

 

il_selection02_11.png

 

The drawing is also in use in an open map window, called Map, where the drawing is one of two layers.  

 

il_selection02_09.png

 

We also have the drawing's table open in a table window, called Mexico Table.

 

To illustrate the use of functions we will interactively hack away, writing snippets of SQL into the Command Window and then running them by pressing the ! run button on the main toolbar to see what happens.

 

il_selection02_12.png

Our first example uses the Selection function, which returns a table of all records that are selected (if the boolean argument is True) or unselected (if the boolean argument is False) in the table.

 

? CALL Selection([Mexico Table], True)

 

The ? command in the Command Window is a command to evaluate the following expression.  It provides a quick way of evaluating snippets of SQL, to see what they do, without writing full SQL queries.   We must use CALL because the function we use returns a table.    There are four selected records in the table, so that is what the results table returns.

 

il_selection02_13.png

 

? CALL Selection([Mexico Table], False)

 

Changing the boolean argument to False returns a results table of all unselected records in the table.

 

il_selection02_14.png

The analogous "window" version of the function is called SelectionWindow.

 

? CALL SelectionWindow([Mexico], 'Map', 'Mexico', True)

 

When using SelectionWindow we must specify the name of the desired window as well as the name of the desired layer in the window, since windows can have more than one layer.   In the above example we also use the name of the drawing, since the functions can take either the name of the table or the name of the component based on that table.    We use the name of the drawing in the example above to show that either can be used.

 

In SelectionWindow the name of the drawing is in square [ ] brackets as [Mexico] because it is the name of a component, like a table.   The names of the window and the layer are simply string arguments, so those are given using single ' ' quotes, as 'Map' and 'Mexico' as any string literal would be in SQL.   As a boolean type, the last argument is simply True with no need to be set off by quotes or brackets.

 

il_selection02_15.png

This example shows a variation where we use SelectionWindow with the drawing window.    Recall that the drawing window is called Mexico.

 

? CALL SelectionWindow([Mexico Table],'Mexico', '', True)

 

In this example we use the name of the table within square [ ] brackets, [Mexico Table].  The drawing window has only one layer, the drawing, so when using the SelectionWindow function with windows other than map windows we enter the layer name as an empty string, denoted by two single quote ' characters with no space between, as ''.   This may look like a double quote character but in actuality it is two single quote characters with no space character in between them.

 

il_selection02_16.png

 

The SelectionKeys function returns a table with values of the key field for selected records.

 

? CALL SelectionKeys([Mexico Table])

 

In our table the key field is the usual mfd_id field used as a key field by default in most Manifold drawings.   The results table reports the value of mfd_id for each of the selected records.

 

il_selection02_17.png

 

We haven't inverted the selection in this table, so as expected...

 

? SelectionIsInverted([Mexico Table])

 

...returns False.   Since the SelectionIsInverted function does not return a table, we simply use the ? command with no need to use CALL.

 

il_selection02_18.png

 

Likewise, the SelectionIsEmpty function returns a boolean, not a table, so we do not need to use CALL with this function either.

 

? SelectionIsEmpty([Mexico Table])

 

The selection is not empty, so the above also returns False.

 

il_selection02_19.png

 

So far we have been using selection functions to view the status of the selection.   We can use them to edit content as well.

 

UPDATE CALL Selection([Mexico Table], True)

   SET [Place_name] = [Place_name] & ' - OK';

 

In the above we take the table of selected records returned by the Selection function and we concatenate the string - OK to the place names of each selected record.

 

il_selection02_20.png

The Command Window reports that 4 records have been updated, and the open table window is instantaneously updated as well.  

Live Edits

What follows is not really specific to SQL functions for selection, but it does show how they can be combined with other capabilities.

 

il_selection02_21.png

 

EXECUTE CALL Selection([Mexico Table], True);

 

The results table generated by the above query is fully read/write since it includes the mfd_id field which hosts the index for the table.   We can double-click into cells to edit the contents, for example, changing the Place_name value for the Sonora record from SONORA - OK to SONORA - Maybe.  

 

 

il_selection02_22.png

 

The edit takes immediate effect and the table window is updated with the change.

 

il_selection02_23.png

 

Other facilities typical of table-style, grid displays in Manifold also work.   For example, we can click on the SQKM field column header in the results table to sort the results table by that field.

Other Options

In the above examples we have seen how to use SQL functions in the Command Window to pick out only the selected records in a table, and to then edit and sort the results table.   Using snippets of SQL in the Command Window provides vast, endless power and flexibility but it is not the only way.   If all we want to do is see only selected records in a table we can simply open the table and use Filters.   

 

il_selection02_22.png

 

We click open the table with the selection in it.

 

il_selection02_24.png

 

With the focus on the table we choose View - Filter and choose the Selected option.

 

il_selection02_25.png

 

That filters the table to show only selected records.   We can then click on the SQKM column header to sort by that field.

 

See Also

Selection

 

Contents Pane

 

Contents - Select

 

Tables

 

Filters

 

SQL Functions

 

Example: Select a Range of Records in a Table - We can select or deselect a range of records between any two records in a table window using fast and easy mouse commands.

 

Example: Mouse Selection in a Drawing Window - Using the mouse to select objects in a drawing window.   This could be a drawing layer in a map or simply in a drawing window.

 

Example: Combining Selections using the Select Panel - How to use selections to solve a one-off problem casually that would be more difficult to solve rigorously with SQL.   The objective in the example's scenario is to visit a point of tourist interest while travelling.    We will use the Transform panel as well as the Select panel.

 

Example: Using the Select Panel Template Tab -  A sequence of actions using Select panel templates.

 

Example: Construct JSON String using Select and Transform - Use the Select panel and the Transform panel to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.