GNA – Queries (Keywords)

Govern Keywords

Using Keywords

You can include a keyword in a query to retrieve or perform an action on a value in the current record, or the record most recently saved and still in memory.
To include a keyword, double-click in SQL Definition Setup form. This displays the following list of keywords (Table: VT_SY_BRULE):

Read More...Read Less...

Code	Keyword	Description
ar_id	Account Receivable ID	Unique identification number of the current Account Receivable record
key_counter	Activity ID	Identification number of the current Activity record
ac_id	Aircraft ID	Unique identification number of the current Aircraft Excise Tax record
br_id	Bankruptcy ID	Identification number of the current Bankruptcy record
bt_id	Boat ID	Unique identification number of the current Boat Excise Tax record
bldg_id	Building ID	Identification number of the current Building
bldg_seq	Building Sequence	Sequence number of the current Building
cc_id	Cash Collection ID	Identification number of the current Cash Collection record
co_id	Complaint ID	Identification number of the current Complaint record
*date	Current Date	The Current Date
dept	Department	The Department code
frozen_id	Frozen ID	Identification number of the current Frozen record
haz_id	Hazard ID	Identification number of the current Hazard record
h_id	Hearing ID	Identification number of the current Hearing record
in_id	Inspection ID	Identification number of the current Inspection record
land_id	Land ID	Unique identification number of the current Land
mb_id	Misc. Billing ID	Identification number of the current Miscellaneous Billing account
misc_id	Miscelleaneous ID	Identification number of the current Miscellaneous Billing account
mv_id	Motor Vehicule ID	Identification number of the current Motor Vehicle Account
na_id	Name ID	Unique identification number of the current Name
of_id	Offence ID	Unique identification number of the current Offense record
p_id	Parcel ID	Unique identification number of the current parcel
pm_id	Permit ID	Unique identification number of the current Building Permit, Electrical Permit, General Permit, Plumbing Permit, Permit to Name, Animal License, Business License, License to Name, Approval, Bond, Decision, Prosecution or Appeal record
pp_id	Personal Property ID	Identification number of the current Personal Property account
pp_det_id	PP Detail ID	Unique identification number of the current Personal Property Item
folio_id	Project Folio ID	Identification number of the current Project Folio
prj_id	Project ID	Identification number of the current Project record
tax_id	Real Estate ID	Identification number of the current Real Estate Tax record
sale_id	Sale ID	Identification number of the current Sale record
st_acct_id	Self Reported Tax Acct ID	Identification number of the current Self Reported Tax Account record
st_id	Self Reported Tax ID	Identification number of the current Self Reported Tax record
si_id	Site ID	Identification number of the current Site
sa_id	Special Assessment ID	Identification number of the current Special Assessment record
tax_map	Tax Map Number	Tax Map Number of the current record
usr_id	User ID	Unique identification number of the current user
ub_id	Utility Billing ID	Unique identification number of the current Utility Billing account
year_id	Year ID	Year identification of the current record

For example, in the following query, the parcel id, frozen id and year id keywords are used to retrieve the appraised property value from the MA_MASTER table, where the P_ID, FROZEN_ID and YEAR_ID fields match the values for the current record.

SELECT appraised_value FROM ma_master WHERE p_id=parcel id

and frozen_id=frozen id and year_id=year id

Query Types

Overview

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.

NOTE: On the SQL Definition Setup form, you must use SQL syntax for Microsoft® Access®.

Query Types

Read More...Read Less...

Selection Queries

See Selection Queries section of the page.

Action Queries

See Action Queries section of the page.

Update Queries

See Update Queries section of the page.

Insert Queries

See Insert Queries section of the page.

Delete Queries

See Delete Queries section of the page.

Special Cases

Obtaining a Value when No Govern ID is Present

SQL Queries are used for Calculated Fields, Formulas, and Logical Expressions. In the SQL Query Editor, users are provided with a list of Govern ID’s that are used as keywords in queries. A Keyword type was introduced in Release 4.7, 5.1, and Release 6.0. When building a query, instead of using Govern ID’s, the attribute name in a Business Entity (BE) can be used to read its value.
An advantage of the attribute keyword search syntax is that it can be used to obtain attribute values from Business Entities (BE’s) that do not use any Govern ID’s. As long as the attribute is specified explicitly, its value can be obtained from the associated BE.

Read More...Read Less...

EXAMPLE
In the following example, we want to compare an NA_ID value in the NA_NAMES table with the values FREE_LINES_X, where X is a value of 1 to 6; these values are stored in the NA_LK_TO_NAME Business Entity.
In the query sample, using the SELECT and CASE statement, the values contained in FREE_LINE_1, FREE_LINE_2, FREE_LINE_3, FREE_LINE_4, FREE_LINE_5, and FREE_LINE_6 can be retrieved.
LTRIM – Removes any leading spaces; CAST – Casts the value as a variable character type

Query Fragment

SELECT (LTRIM(CAST((CASE WHEN NA_NAMES.FREE_LINE_1 IS NOT NULL
THEN NA_NAMES.FREE_LINE_1
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_2 IS NOT NULL
THEN CHAR(13) || CHAR(10) || NA_NAMES.FREE_LINE_2
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_3 IS NOT NULL
THEN CHAR(13) ||CHAR(10) || NA_NAMES.FREE_LINE_3
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_4 IS NOT NULL
THEN CHAR(13) ||CHAR(10) ||
NA_NAMES.FREE_LINE_4 ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_5 IS NOT NULL
THEN CHAR(13) ||CHAR(10) || NA_NAMES.FREE_LINE_5
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_6 IS NOT NULL
THEN CHAR(13) ||CHAR(10) || NA_NAMES.FREE_LINE_6
ELSE ” END)
AS VARCHAR(80)))) NAME_DESC
FROM NA_NAMES
WHERE NA_NAMES.NA_ID=attr@NA_LK_TO_NAME.LINK_NA_ID

Prior to the Entity/Attribute keyword, the FREE_LINES_X values could not be obtained in the traditional manner because it is not a Govern ID. With the keyword we are able to compare the value of FREE_LINE_X with our NA_ID.
To do a comparison with a SQL query, the syntax for our keyword (attr@EntityName.AttributeName) will specify the code from the entity and
the name of the required attribute.
In the last line of the query sample, the value in the NA_ID is compared to the value in the LINK_NA_ID in the NA_LK_TO_NAME Business Entity.
NA_NAMES.NA_ID=attr@NA_LK_TO_NAME.LINK_NA_ID
…where…
EntityName = NA_LK_TO_NAME and AttributeName = LINK_NA_ID

NOTE: Currently the entity and attribute names are entered manually, therefore caution should used when typing.

SQL Syntax Rules for GNA

Release 6.0 | Release 6.1

Govern Data Access Block (DAB)

The Data Access Block or DAB has several functions in the Govern system. One of these functions is to act as the intermediary between the .NET applications and the database. SQL queries that are used by Govern must go through the DAB. While SQL can be flexible with its syntax, due to its many functions, the DAB must be stricter with syntax. In short what may work in SQL, may have to be modified for the DAB. In fact some of the rules that the DAB requires you to follow are good practises for SQL as well.

The following guidelines and examples have been collected to increase your level of success with Govern SQL queries.

Basic SQL Query Guidelines

A. Field in Table is Always on the Left Hand Side

When creating your query statement, the field that you are referencing in the table should always be on the left hand side (LHS), and the Dynamic Search object should always be on the right hand side (RHS).

Read More...Read Less...

For Example:

In the following SQL statement …

NOT VALID: WHERE @pmnumber_1=PM_MASTER.PM_NUMBER

NOTE: The above statement is valid in SQL, but it will fail in Govern. The Data Access Block (DAB) will not interpret it correctly.

Should be written as…

VALID: WHERE PM_MASTER.PM_NUMBER=@pmnumber_1

B. No Spaces Allowed after Logical Operators

OPERATORS
Symbol	Description
=	Equal To
<>	Not Equal To
>	Greater Than
<	Less Than
>=	Greater Than or Equal To
<=	Less Than or Equal To

For Example:

NOT VALID: WHERE PC_PARCEL.PID1 > PC_PARCEL.ID2

should be written as…

VALID: WHERE PC_PARCEL.PID1>PC_PARCEL.ID2

C. Space Before and After LIKE Operator

The LIKE pattern matching operator is used to select only rows that are “like” what you specify. When using it ensure that there is a space before and a space after the operator.

For Example:

NOT VALID: WHERE PC_PARCEL.PID1LIKEPC_PARCEL.P_ID

should be written as…

VALID: WHERE PC_PARCEL.PID1 LIKE PC_PARCEL.P_ID

D. Always Specify Tables and Fields in Statements

When writing statements, always specify Tables then Fields (TABLE.FIELD). This applies even in cases where you are referring to a single table and there would be no ambiguity.

For Example:

The following statement…

SELECT NA_ID, INDEX, DESC FROM NA_NAMES WHERE NA_ID=@nanameid_0

is the same as ….

SELECT NA_NAMES.NA_ID, NA_NAMES.INDEX, NA_NAMES.DESC FROM NA_NAMES WHERE NA_NAMES.NA_ID=@nanameid_0

In the first statement we are only specifying the columns in the NA_NAMES table. This would not pose as a problem if we are only dealing with one table. Realistically, we will often have to interact with multiple tables, and many tables have the same column names; this would create an ambiguity. The system would not know which table is being referred to.

Release 6.0

What’s New

Related current changes to the working of Queries in Release 6.0 will be indicated below.

Release 6.1

What’s New

The following are current changes to the working of Queries in Release 6.1.

Nomenclature

As of Release 6.1, it is mandatory that query names must begin with a letter. [Release 6.1.1506.82]

Overview

A GovernAction is a link between a method (InsertData) and a report. GovernActions take the form of a report object with a name that has a ‘GovernAction’ prefix, and value format. The process is similar to an Insert or Update action in the database. GovernActions are executed just prior to Displaying, Exporting, or Printing a report. Users should note that there is no Log, or indication of the running of the GovernAction. If the Govern Action triggers an exception, execution of the report stops, and the error is displayed but it is not handled. The GovernAction is designed to be triggered from any Govern report, but currently the action is only configured through Crystal Reports.
Govern Actions are an efficient way to…

Perform an audit on parcels that have been accessed using Crystal Report.
Provides a secure, and controlled method of inserting data in tables.

Have a report display information on multiple accounts, the report can then generate history information in the table and show new data that has been inserted; this new data is inserted in the (Table: ST_EVENT_HIST)

Read More...Read Less...

Documentation

User

Click below to display or download the user documentation
100-reporting-GovernActions.pdf

Technical

Click below to display or download the technical documentation.
100-reporting-GovernActions-TECH.pdf

What’s New!

Changes Due to Refactoring

NEW! As a result of recent refactoring, the following two (2) changes have been made to the Govern Actions.

Placement of Govern Actions in Crystal Reports

Govern Actions no longer need to be placed exclusively in the Details section in a Crystal Report; Actions may now be placed in any of the main sections available to Crystal, i.e. Report Header, Page Header, Details, Report Footer, and Page Footer.

Grouping Allowed

A Crystal Report report can now have grouping and Govern Actions at the same time. For example there could grouping under an ST_ID. The methodology employed involves parsing the exported XML file and identifying required keywords.

NOTE: There is a limitation to the size of the XML file that is parsed; file size should not exceed 2 GB.

100-reporting-GovernActions

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

OpenForms Reference

Overview

The following is listed for information only. When there is a discrepancy, refer to the List of Changes Tables section of the Govern Database Diagrams page for the latest information and changes in the database structure or forms.

EMT Tool (Import Export)

XML files are located in the system library under:

FormReference.xml
DynamicSearch.xml

List of OpenForms Standard Forms

The following are links to the list of OpenForms standard forms that are found in the OpenForms release reference database.

Changes

Read More...Read Less...

Changes made to the OpenForms supported items (forms, models, system tables, etc.) will be listed by date and version on this page (100-ofr-new). We suggest you setup a notification if you are in an implementation project or just to be informed.

Tables deleted or obsolete

OFR Product Catalog Codes

System	Module	Type			Version	Name	OFR
101	ma	ofr	1		[v6.0]	CAMA Building Drawing	ma001
101	ma	ofr	14		[v6.0]	CAMA Property Info	ma014
101	ma	ofr	15		[v6.0]	CAMA Sale info	ma015
101	ma	ofr	17		[v6.0]	CAMA Land	ma017
101	ma	ofr	21		[v6.0]	CAMA Value Override	ma026
101	ma	ofr	26		[v6.0]	CAMA Buildings (contains BSE)	ma026
101	ma	ofr	71		[v6.0]	Combined CAMA Prop Info (014) + PC Area (017) + PC Legal (018)
101	na	ofr	1		[v6.0]	Name & Adress	na001
101	na	ofr	2	dash	[v6.0]	Name Summary	na_sum
101	na	ofr	35		[v6.0]	Name Linking	na035
101	pc	ofr	1		[v6.0]	Property Parcel Information	pc001
101	pc	ofr	2	dash	[v6.0]	Property Summary	pc_sum
101	pc	ofr	17		[v6.0]	Property Area	pc017
101	pc	ofr	18		[v6.0]	Legal	pc018
101	pc	ofr	38		[v6.0]	Deed	pc038
101	pc	ofr	39		[v6.0]	Combined PC Parcel (012) + PC Property Location (004)

100-ofr

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

100-bp-Execution

Standard Batch Process – Execution & Connection

Version 6.0 and Version 6.1 (In Development)

Overview

The following explains the type and mode of batch executions.

Transaction Type

A Govern batch process can run synchronously or asynchronously.

Synch

In synchronous batch processing, transactions are processed one after another in a sequence.
If there are multiple steps in the process, one step is completed before the next begins.

Asynch

In asynchronous processing, multiple operations are started and run at the same time. As soon as one finishes, the next begins.
Multiple steps can run at the same time; for example, steps one, two, three, and four can all begin at the same time.
For long batch processes, using asynchronous batch processing can speed up the transaction time, because multiple computer operations or threads are processed at the same time compared to synchronous batch processing, where one operation has to finish before the next can begin.

IMPORTANT: If a batch process has multiple steps that need to be performed in order; for example, if the results of the first step are required for a calculation in the second step, use synchronous batch processing.

Connection Pool

You can define a Maximum Connection Pool Size for asynchronous batch processing in the addition to the general Connection Pool Size. You can also set a Connection time out for batch processes.
These parameters are defined on the Connection Key General Parameters Management form in GNA.

The Connection Pool is the number of connections that can be open on the server at the same time. Connections are required for the asynchronous batch processes. They are used by the threads to perform operations for batch processing. They are also required for all other Govern operations from all deployments in your organization.

The Connection Time Out refers to the length of time that a batch process executes a query before a time out occurs. The default is zero (0). This means that there is no time out.

Transaction Mode

Select one of the following transaction modes for the process.
Break If One Transaction Failed
The process terminates when the transaction fails.

Roll Back If One Transaction Failed
The process terminates when a transaction fails. All the transactions that were completed are cancelled. All the computations are returned to the starting point.

IMPORTANT: The roll back can take a long time to execute if the error occurred at the end of the process.

Continue If One Transaction Failed
The process continues to run even if a transaction fails.

100-bp-Execution

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

Govern Keywords

Using Keywords

Related Topics

Query Types

Overview

Query Types

Selection Queries

Action Queries

Update Queries

Insert Queries

Delete Queries

Related Topics

Special Cases

Obtaining a Value when No Govern ID is Present

Query Fragment

Related Topics

SQL Syntax Rules for GNA

Govern Data Access Block (DAB)

Basic SQL Query Guidelines

A. Field in Table is Always on the Left Hand Side

For Example:

B. No Spaces Allowed after Logical Operators

For Example:

C. Space Before and After LIKE Operator

For Example:

D. Always Specify Tables and Fields in Statements

For Example:

Release 6.0

What’s New

Release 6.1

What’s New

Nomenclature

Related Topics

Reporting Govern Actions

Overview

Documentation

User

Technical

Related Topics

What’s New!

Changes Due to Refactoring

Placement of Govern Actions in Crystal Reports

Grouping Allowed

OpenForms Reference

Overview

EMT Tool (Import Export)

List of OpenForms Standard Forms

Changes

OFR Product Catalog Codes

Standard Batch Process – Execution & Connection

Overview

Transaction Type

Synch

Asynch

Connection Pool

Transaction Mode