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