query must contain a select statement followed by one or more fields. For
[fields] FROM [tables] WHERE [...]
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
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.
values entered by a user are substituted into the body of the anonymous block
in place of the corresponding parameter names.
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.
Datediff() function in a Where clause causes performance issues.
There can be
no unnamed fields in an SQL query. Each field needs an alias.
must be unique.
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
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.
stored procedure parameter names are prefixed with the 'at' character:
Keywords for the SQL Parser (with Sample Queries)
ALL (SQL Server
or Informix)—SELECT ALL CallTypeID from Call_Type_Interval
Server or Informix)—SELECT DISTINCT CallTypeID from Call_Type_Interval
Server)—SELECT TOP 5 CallTypeID from Call_Type_Interval
(Informix)—SELECT FIRST 5 ID FROM CUICDATASETINFO
(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]
GROUP BY [...]
HAVING [...] optional
ORDER BY [...] optional
select CallTypeID, TimeZone, sum(CallsHandled) as total, avg(CallsHandled) as average
where TimeZone = 240
group by CallTypeID, TimeZone
having sum(CallsHandled) in(3, 5, 13) and avg(CallsHandled) > 0
order by CallTypeID