Queries limits and constraints

Query Rules

By default, the resolution level of a select query (i.e. the owner table of the result domain) is dependent on the data structure and the order in which the tables are specified in the FROM clause. Correct control over the resolution level can be obtained by use of the DISTINCTROW keyword.

The default resolution is the MANY table in a ONE to MANY relationship

Description	Rule	Example
Records returned from a query	Records from the resolution table that satisfy ALLlinks between resolution table and other tables specified in FROM clause.	SELECT # FROM T1, T2, T3, T4; Returns all records from T1 that are linked to T2, T3 ANDT4
Fields that can be returned from a query	For a field to be included in the SELECT clause of a query, the table that it comes from must be viewable at the resolution level of the query.	SELECT T1.*, T3.* FROM T1, T2, T3; Invalid. It is not possible to return records from T3 as it is not viewable at any other table level within this structure.
Default Resolution	The default resolution is the MANY table in the first 2 tables that are specified in the FROM clause. If the first 2 tables are linked by a ONE to ONE relationship, the default will be - The table containing the most records - The first table specified if the 2 tables are the same size	SELECT # FROM T1, T2, T3, T4; The resolution table will be T1 as the relationship between T1 and T2 is ONE to ONE and they both contain the same number of records. SELECT # FROM T1, T3, T2, T4; The resolution table will be T3 as T3 is the MANY side of the link between T1 and T3.
Specifying the Resolution Level	The resolution level of a query can be explicitly set using Distinctrow. It is good practice to use Distinctrow in multi-table queries.	SELECT Distinctrow T4.# FROM T1, T2, T3, T4; This will return the count of all records in T4 that are linked to all of the other tables.

Where Clause

Fields that are listed in the where clause must be linked to the resolution table of the query.

There is no theoretical limit on the number of ANDclauses that can be specified in the Where clause of a SQL statement. However, the length of the statement must not exceed the maximum length of an expression (64K)

GROUP BY Clause

A GROUP BY Clause can only be executed against the following intrinsic functions;

COUNT, SUM, MIN, RMEAN, MEAN, MAX, STDEV, STDEVS

This means that Select * cannot be used with Group By queries.

Up to 20 columns can be specified after the GROUP By clause. However, the number of discrete values on the resulting Y axis must not exceed 2 billion, and the maximum number of cells in the crosstab must not exceed the amount allowed by available memory.

(See Crosstabs lmits and constraints for more details)

PIVOT By Clause

The PIVOT By Clause can only be included after a GROUP BY Clause. Only one column can be specified for a Pivot By clause. Further columns will be ignored.

Multiple Distinct

Engine 3.0 and later supports Multiple Distinct where the combination of all the columns specified in the distinct clause is evaluated.

The following statement will return all unique combinations of Product and CustID:

Transactions Table

SELECT DISTINCT CUSTID, PRODUCT 
FROM TRANSACTIONS;		

Engine 3.0 results

The combined width of all the fields specified in the distinct clause must not exceed 1024.

IN Statement

There is no theoretical limit on the number of values that can be specified in an IN clause. The practical limit is governed by the size of the query or expression which must not exceed 64K.

Sub Selects

When using a sub-select in NSQL, the sub query must return:

• either a single value
• or a domain of a single column of less than 1024 discrete values.

 

If the sub-select returns a domain, the only operator that can be used with it is IN, and the IN operator can take a maximum of 1024 values.

If the sub-select domain returns more than 1024 discrete values, an "Invalid Definition" error will be raised.

The following query selects all records from the family table for towns that contain 5 bedroom houses. If the number of towns that contains 5 bedrooms exceeds 1024, the query will fail.

SELECT *

  FROM [DEMO].[FAMILY]

  WHERE [DEMO].[FAMILY].[TOWN]  

  IN
      (SELECT DISTINCT TOWN

          FROM  [DEMO].[FAMILY]

          WHERE BEDROOMS=”5”);