SQL Query Editor Parameters
- Query Name – Enter a name or code to identify the query.
For the queries that retrieve data and display a value, you can add the following characters to the beginning of the name. These characters format the value in the result:
| Character |
Format Type |
Example |
| $ |
Currency with decimals |
$2,000.25 |
| @ |
Currency without decimals |
$2,000 |
| #! |
Numeric with decimals |
2,000.25 |
| . |
Numeric without decimals |
2,000 |
For example, the following query retrieves the appraised value of the property and formats it as the Currency without decimals type:
| Query Name: |
@GetApp |
| Query: |
SELECT appraised_value FROM MA_MASTER WHERE p_id = parcel id and frozen_id = frozen id and year_id = year id |
| Result: |
$288,000 |
- English Short Description – Enter a short description to identify the department. This is useful for fast data entry and look-ups if space is limited on the forms.
- English Long Description – Enter a long description to identify the department. This will be displayed for look-ups on forms and will be normally used for reporting.
Second Language Fields
When there is a 2nd language, or multiple languages, ensure that these description fields are also completed.
- Is System – This flag / option is reserved for constants that are designated as Govern.NET system data.
About System Reserved Values
Only users with Super-User access will be able to select and deselect the Is System option. In addition Super Users can also create new values and flag them for Govern.NET system use.
NOTE: System constants are reserved for use by the Govern.NET system and as such should not be modified or deleted without a full understanding of the implications. Deletions of system values can damage the Govern.NET system, rendering it inoperable. Modifications that are made to System values should always be noted. When a system wide update is performed, these modifications may be overwritten.
- Warning – Enter a message to be displayed before the query is executed, in this optional field. For example, if you are linking a query to the Delete button of a function, create a message to notify the user that one or more records will be deleted. The user can confirm or cancel the action.
- Alternate Connection Key – Database connections are usually set in the General Settings form, they can be overridden if required. An alternate Connection Key can be specified.
- Query (SQL) – Enter the new SQL Query statement or modify an existing one, in this edit box.
Tutorial
Creating Queries
Create a Simple SQL Query
For this example we will create a basic SQL query that will retrieve lot size information froma Govern table (Table: MA_LAND).
The query will need to get the lot size entry under the LOT_SIZE column in the MA_LAND table. The query should look like the following:
SELECT MA_LAND.LOT_SIZE
This means that we need the LOT_SIZE column…
FROM MA_LAND
…from the MA_LAND table…
WHERE MA_LAND.YEAR_ID = Year ID AND MA_LAND.FROZEN_ID = Frozen ID AND MA_LAND.LAND_ID = Land ID
…the YEAR_ID is the same as the current Year ID, the FROZEN_ID is the same as the current Frozen ID, and the LAND_ID is the same as the current Land ID.
To create a simple query…
1. In the GNA ribbon, click the Editors tab: select SQL Query Editor…
2. Click Create a New Item to start a new query.
3. Enter LOTSIZE2 in the Query Name field (2)
4. In the Short Description field enter Lot Size (3)
5. Click into the Long Description field (4), the Short Description will be copied into the Long Description, add any additional information to the name.
6. Click in the Query (Sql) field (5); begin typing in the query created earlier.
SELECT MA_LAND.LOT_SIZE FROM MA_LAND
WHERE MA_LAND.YEAR_ID=Year ID AND
MA_LAND.FROZEN_ID=Frozen ID AND
MA_LAND.LAND_ID=Land ID
NOTE: When entering the query, ensure that there are no spaces before and after the “=” sign. For other syntax rules, see SQL Syntax Rules for GNA.
7. To save the new query, click Save (6); if an error exists within the query, the system will not allow it to be saved. You will be presented with a dialog box like the following; click OK and make any necessary corrections to the syntax of the query.
NOTE: To minimize the chances of errors when composing your queries, refer to SQL Syntax Rules for GNA.
The new query will appear under the SQL Definition List (A) on the left hand side.
The query will appear on the left hand side under the SQL Definition List. When saved this query will be accessible for use in formulas and logical expressions.
Query Types
You can compose and store both Action Queries and Selection Queries in the SQL Query Editor. To retrieve or to use a value from the current record; for example, while running a query from a function or formula, you can include a keyword. See Using Keywords for details.
NOTE: Keywords can be included on the SQL Definition Setup form, only; on this form you must use SQL syntax for Microsoft® Access®.
Selection Queries
Selection Queries are used to retrieve records from one or more specified tables, according to the selection criteria.
The syntax is as follows:
SELECT [column] FROM [table] WHERE [criteria]
For example, the following query, retrieves the City District name from the PC_AREA table for the record matching the current parcel ID, frozen ID and year.
SELECT DIST_CITY FROM PC_AREA WHERE p_id=parcel id and frozen_id=frozen id and year_id=year id
Monitoring Tables for Changes
Available in Rel. 6.1.1803
NEW! Observable user queries are user queries where the result is automatically refreshed whenever one of the parameters or the underlying data changes. This is the case for:
- View queries
- Calculated fields
- Lookup lists
- Etc.
In order to update itself when the underlying data changes we must listen for the table changed event. As long as the table name is in the query, it will be refreshed.
However, when using a View or an Alias instead of the actual table name, then issues will arise as you will not know that the query must be re-executed. In such instances, the table name can be specified explicitly by using the hint monitortables:
–monitortables(NA_NAMES, PC_PARCEL)SELECT *
FROM v_view1 v1 INNER JOIN v_view2 v2 ON v1.na_id=v2.p_id
In the previous example, v_view1 is actually a view over the table NA_NAMES, while v_view2 is a view over PC_PARCEL. By using the hint monitortables users can explicitly define which tables are monitored for changes.
Action Queries
Action Queries are used to perform actions, such as updating records in one or more tables, adding records to a table, deleting records from a table or creating a new table or index.
This section provides simple examples of Update, Append and Delete Queries. Since the SQL Definition Setup form is used for queries that are run multiple times, it is better to compose and run a Create Query from outside Govern.
Update Queries
Update Queries are used to modify records in one or more tables, by changing values in specified tables and fields, according to certain criteria.
The syntax is as follows:
UPDATE table.* SET value WHERE criteria
For example, the following query enters the value abc in the Fire District field of the PC_AREA table, wherever the value for this field is null.
UPDATE pc_area SET dist_fire = ‘abc’ WHERE dist_fire IS NULL
Update Queries are typically used when you need to change multiple records in a table or records in multiple tables.
TIP: You do not obtain a list of results from running this type of query. To verify which records will be changed, you can run a Selection Query
using the same parameters and criteria and view the Results screen. |
Insert Queries
Insert Queries are used to add a single record to one or more table. You need to specify the fields to which you are adding values and the value for each field. Otherwise, the default value or Null is entered.
The syntax is as follows:
INSERT INTO table (column list) VALUES (value list)
For example, the following query adds a record to the VT_USR_NAMECODE table, entering the values ENG, LD, ITR and In Trust in the language, department, code, short description fields.
INSERT INTO vt_usr_namecode (language,dept,code,short_desc)
VALUES (‘ENG’,’ ’,’ITR’,’In Trust’)
You can also append multiple records to a table, by first selecting the records in another table. For this, a Selection Query is added to the Append Query.
Delete Queries
Delete Queries are used to remove records from one or more tables.
The syntax is as follows:
DELETE [table.*] WHERE criteria
For example, the following query, add records containing the year 2004 are deleted from the PC_AREA table.
DELETE FROM pc_area WHERE year_id = 2004
| TIP: The Delete Query deletes more than the specified fields, it deletes the entire record. To delete data from specific fields only, create an update query that changes the values to Null. |
Monitoring Tables
Observable user queries are user queries where the result is automatically refreshed whenever one of the parameters or the underlying data changes. Queries of this sort are used in the following:
- View Queries
- Calculated Fields
- Lookup Lists
and so on…
In the above examples, data changes due to user interaction may occur with the above types of queries. In order to update when the underlying data changes you can “listen” for the table changed event. As long as the table name is in the query, the data will be refreshed. Issues arise when using views
As a view is the result set of a stored query on the data
However, if using a view or an alias instead of the actual table name then we won’t know that the query must be re-executed. In this case you can specify explicitly the table name by using the monitortables hint :
–monitortables(NA_NAMES, PC_PARCEL)SELECT *
FROM v_view1 v1 INNER JOIN v_view2 v2 ON v1.na_id=v2.p_id
In the previous example, v_view1 is actually a view over the table NA_NAMES, while v_view2 is a view over PC_PARCEL. By using the hint monitortables users can explicitly define which tables are monitored for changes.
See Also
Formula Editor
Logical Expression Editor
SQL Syntax Rules for GNA
Query Types
Advanced SQL Queries
Keywords
Best Practices for SQL Queries
Special Cases
100-Queries
Loading...
