CreateEditorByQuery()

Summary

Creates an editor displaying the results of an SQL query.

Syntax

actual_name = CreateEditorByQuery(string editor_name, string sql_query, array options).

Argument Contents
editor_name The desired name of the editor
sql_query The SQL query (see below)
Option Type Contents
Activate Macro String The name of a macro that is called whenever the window is activated
Background Menu String The context menu associated with a selected window outside the data range
Code Field Callback String The name of a macro to use when a new value is entered in a coded field, and the code cannot be uniquely determined from the value. The macro gets an array of possible codes and should return the desired value.
Cell Menu String The context menu when a cell is selected
Close Macro String The name of a macro that is called when the user closes the window from the system menu
Column Menu String The context menu associated with a selected column
Done Macro String The name of a macro that is called after the window is closed
Font String The font in which data are displayed
Grid Boolean Whether the editor displays a grid between cells
Highlight Macro String The name of a macro that is called whenever the user changes cells that are highlighted in the editor (by clicking on a cell or dragging over a range of cells)
Locked Boolean If "True" the Close option does not appear in the system menu; if "False" the Close option does appear
Menu String The menu that is associated with the window
Position 2 integers The screen location of the window (x, y)
Range Menu String The context menu associated with a selected range of cells (but not a column or row)
Read Only Boolean Whether the data are read-only
Row Labels String Number of columns (fields) that should be locked in place at the left edge of the editor window; this option must be specified as a string (e.g., "2")
Row Menu String The context menu associated with a selected row
Selection Macro String The name of a macro that is called whenever the user clicks in a selection set column
Show Sets Boolean Whether the editor includes columns that indicate which records belong to selection sets
Size 2 integers The screen size of the window (x, y) as percentages of the frame window
Title String The title of the window
Toolbar String The toolbar that is associated with the window
Trans Head Width String The width of the head column in a transposed editor, in number of characters
Trans Data Width String The width of each data column in a transposed editor, in number of characters

Returns

A string indicating the actual name of the editor.

Notes

  • CreateEditorByQuery() takes a full SQL query, as compared to SelectByQuery(), which uses only the "where" portion of the query syntax. A full SQL query has the form:

Select field, field, field...
From table, table, table...
Where condition
Group by field
Having condition
Order by field, field, field...

  • The From, Group by, Having, and Order by clauses are optional. The Having clause is only allowed with the Group by clause. If the Group by clause is included, the Where clause can be omitted.

  • Each field in the Order by clause can optionally be followed by the string "Asc" or "Desc" to specify Ascending or Descending.

  • CreateEditorByQuery() performs the following steps:

    • If the From clause is present, finds the portions of the Where clause that indicate how the tables are related and creates an appropriate joined view (e.g. Counties.[State Name] = States.Name).

    • Selects records from the joined view based on the remainder of the Where clause (e.g. County.Population > 100000).

    • If there is a Group by clause, aggregates records in the joined view based on the grouping fields.

    • If there is a Having clause, selects records from the aggregate view.

    • If there is an Order by clause, sorts the records of the resulting view.

    • Creates an editor showing the resulting view, set, and the fields specified in the Select clause.

Example

SetLayer("County")
CreateEditorByQuery("High Pop",
"Select Name, Pop Where Pop > 100000 Order by Pop Desc",
editor_options)
SetLayer("County")
CreateEditorByQuery("High Pop States",
"Select [GroupedBy(State)], [Sum(Pop)] " +
"Group by State " +
"Having [Sum(Pop)] > 8000000 " +
"Order by [Sum(Pop)] Desc",
editor_options)
CreateEditorByQuery("State Pop",
"Select State.ID, [Sum(County.Pop)] " +
"From State, County " +
"Where State.ID = County.State",
editor_options)

Error Codes

Error Type Meaning
NotFound One or more of the specified fields or views does not exist
Error The query syntax is incorrect

See Also

Function Summary
SelectByQuery() Selects records based on an SQL query
CreateEditor() Creates a new editor window