Property Editor: Extended Query

The properties of an Extended query object are the same as the properties of a simple query object. However, when using a query to derive datasets for use with a data view, or with the Graphic Reporter, the records retrieved by the query will often be retrieved based upon a relationship between:

• A Master dataset which usually retrieves a single record at a time (e.g. the drillhole header table) and...
• A Detail dataset which retrieves more than one record at a time and which is usually related to a master dataset (eg. a lithology table which is related to the drillhole header table)

Note on Scripts

When creating an object with two datasets, it is recommended to always declare a variable at the start.

By associating all substitution parameters and or parent-child parameters with a declared SQL variable at the start of the SQL statement, it is much easier to test SQL scripts in tools such as SQL Server Management Studio, and makes it easier for future users to understand your script.

Master dataset

A master/detail relationship between two tables is based on common key fields (a foreign key relationship). In order to establish a master-detail relationship between two tables, a query makes use of Substitution Parameters in the SQL syntax in order to establish a link with the master dataset.

Name and Description

The Dataset Name and Description attributes are used to identify the query in the datasets list. Dataset names must be unique. Choose a short but descriptive name which includes the dataset type and data category, for example, QuerySeams or QueryAssays.

Do not use spaces or special characters in dataset names.

When a query (that returns a result set) is executed successfully, a Query Dataset object is created dynamically and added to the datasets list in the property editor.

Read Only

Select the Read only option if you want to disallow editing of the SQL result set.

Run on Server

Running on the PC requires that all data is stored in memory. This can cause crashes when dealing with large datasets (e.g. over 1 Gbyte)

The "Run on Server" option allows the data to be broken into manageable chunks. Each chunk of data is verified as it is received, so the process is more reliable.

There is an increased "per record" fetch time, so tasks like importing and exporting data will run slower.

Treat as Dynamic dataset

This is used if the Query may return different column names each time it is run (This may apply to Assay results shown for a set of downhole intervals).

Treat as a stored procedure

The Treat as stored procedure option must be enabled whenever a detail query is used to execute a stored procedure.

Micromine Geobank does not provide native support for datasets returned by stored procedures. However, it is possible to generate a result set by calling a procedure via SQL, for example:

exec dbo.uspValidateLithologyDepths 'MyProject','MySiteID','M'

Ideally, it should also be possible to use a stored procedure as a detail dataset with the arguments passed as parameterised links, for example:

exec dbo.uspValidateLithologyDepths :PROJECT,:SITE_ID,'M'

Treat as dynamic dataset

If the Treat as dynamic dataset option is enabled, the structure of the detail dataset will be dynamically adjusted to fit the data that is retrieved, rather then be displayed in a static grid of columns. If only two columns of data are retrieved for example, only two columns of data will be displayed for that dataset.

Configuration

To select or build the SQL query, click on the Configuration tab. See: Query: Configuration.