Guidelines

• You cannot use comments in an SQL query.

• A database query must contain a select statement followed by one or more fields. For example: SELECT [fields] FROM [tables] WHERE [...]

  This sample query: select CallTypeID, TimeZone from Call_Type_Interval where TimeZone = 240 creates fields CallTypeID and TimeZone.

• You should not use SELECT*, instead you must list all the fields you want to be returned in a SQL query

• An Anonymous Block must be a valid SQL statement that returns a result set. It may contain parameters named :[paramName], where a colon is always the first character of the parameter name and [paramName] is a remaining part of the parameter name.

  The parameter values entered by a user are substituted into the body of the anonymous block in place of the corresponding parameter names.

• Informix and SQL Server Stored Procedures are supported. Stored Procedures must return a result set. For Stored Procedures, parameters are used to pass the values when making a stored procedure call to the database to obtain the result set.

• Using the Datediff() function in a Where clause causes performance issues.

• There can be no unnamed fields in an SQL query. Each field needs an alias.

• Alias names must be unique.

• Informix stored procedures must contain a returning statement, and for each data type in the returning statement, there must be a corresponding alias specified with the letters AS.

  For example: RETURNING CHAR(32) AS returnID, CHAR(32) AS returnName, INTEGER AS returnRefreshrate, BOOLEAN as returnHistorical; And not: RETURNING CHAR(32, CHAR(32, INTEGER, BOOLEAN; If a user fails to provide an alias, the field name will just be fieldN, where N is the index of unnamed field, such as field1, field2, and so on.

• Informix stored procedure parameter names are prefixed with the 'at' character: @param1, @param2 ...

Supported Data Types for Fields and Parameters

• BIGINT, DECIMAL, DOUBLE, FLOAT, INTEGER, NUMERIC, SMALLINT, REAL, TINYINT

• CHAR , LONGNVARCHAR, LONGVARCHAR, NCHAR, NVARCHAR, VARCHAR

• DATETIME

• BOOLEAN, BIT

Special Keywords for the SQL Parser (with Sample Queries)

• ALL (SQL Server or Informix)—SELECT ALL CallTypeID from Call_Type_Interval

• DISTINCT (SQL Server or Informix)—SELECT DISTINCT CallTypeID from Call_Type_Interval

• TOP (SQL Server)—SELECT TOP 5 CallTypeID from Call_Type_Interval

• FIRST (Informix)—SELECT FIRST 5 ID FROM CUICDATASETINFO

• UNIQUE (Informix)—SELECT UNIQUE NAME FROM CUICGRID

Unified Intelligence Center supports these aggregate functions for both Informix and SQL Server: SUM, COUNT, MIN, MAX, and AVG.

In cases where a report definition field is an aggregate function (such as sum(CallsHandled), and that field is a key criteria field or an advanced filter, the supported syntax is:

SELECT (fields)FROM [tables]
WHERE [...]
GROUP BY [...]
HAVING [...] optional
ORDER BY [...] optional

Sample query:

select CallTypeID, TimeZone, sum(CallsHandled) as total, avg(CallsHandled) as average
from Call_Type_Interval
where TimeZone = 240
group by CallTypeID, TimeZone
having sum(CallsHandled) in(3, 5, 13) and avg(CallsHandled) > 0
order by CallTypeID