January 15th, 2020 by inflectra
custom reporting custom graphs spira
One of the maxims I always tell developers is that regardless of what you build, customers will never be satisfied with the reports you offer or the integration that you provide. In fact, the two most underestimated tasks in software development are data feeds and reporting. So one of the nice features in our Spira platform is the ability to do custom graphing, so that you are not limited to just the graphs that ship with the system. This article is the third in a series that explains how to use these powerful custom graphing features, which continues on from our second article that provided the basics of writing ESQL queries. In this article we'll discuss some of the differences and limitations between ESQL and traditional database SQL.
Database SQL supports the unqualified * syntax as an alias for the entire row, and the qualified * syntax (t.*) as a shortcut for the fields of that table. In addition, database SQL allows for a special count(*)
aggregate, which includes nulls.
Entity SQL does not support the * construct. Database SQL queries of the form:
select * from T
and
select T1.* from T1, T2...
can be expressed in Entity SQL as
select value t from T as t
and
select value t1 from T1 as t1, T2 as t2...
,
respectively.
Additionally, these constructs handle inheritance (value substitutability), while the select *
variants are restricted to top-level properties of the declared type. Entity SQL does not support the count(*)
aggregate. Use count(0)
instead.
Entity SQL supports aliasing of group by
keys. Expressions in the select
clause and having
clause must refer to the group by
keys via these aliases. For example, this Entity SQL syntax:
SELECT k1, count(t.a), sum(t.a)
FROM T AS t
GROUP BY t.b + t.c AS k1
...is equivalent to the following database SQL:
SELECT b + c, count(*), sum(a)
FROM T
GROUP BY b + c
Entity SQL supports two kinds of aggregates.
Collection-based aggregates operate on collections and produce the aggregated result. These can appear anywhere in the query, and do not require a group by
clause. For example:
SELECT t.a AS a, count({1,2,3}) AS b FROM T AS t
Entity SQL also supports SQL-style aggregates. For example:
SELECT a, sum(t.b) FROM T AS t GROUP BY t.a AS a
Database SQL allows ORDER BY
clauses to be specified only in the topmost SELECT .. FROM .. WHERE
block. In Entity SQL you can use a nested ORDER BY
expression and it can be placed anywhere in the query, but ordering in a nested query is not preserved.
-- The following query will order the results by the last name
SELECT C1.FirstName, C1.LastName
FROM AdventureWorks.Contact AS C1
ORDER BY C1.LastName
-- In the following query ordering of the nested query is ignored.
SELECT C2.FirstName, C2.LastName
FROM (SELECT C1.FirstName, C1.LastName
FROM AdventureWorks.Contact as C1
ORDER BY C1.LastName) as C2
In database SQL, identifier comparison is based on the settings of the current database and the database platform being used (SQL Server, Oracle, MySQL, etc.). In Entity SQL, identifiers are always case insensitive and accent sensitive (that is, Entity SQL distinguishes between accented and unaccented characters; for example, 'a' is not equal to 'αΊ₯'). Entity SQL treats versions of letters that appear the same but are from different code pages as different characters.
Entity SQL also imposes additional restrictions on queries involving group by
clauses. Expressions in the select
clause and having
clause of such queries may only refer to the group by
keys via their aliases. The following construct is valid in most database SQL variants but are not in Entity SQL:
SELECT t.x + t.y FROM T AS t group BY t.x + t.y
To do this in Entity SQL:
SELECT k FROM T AS t GROUP BY (t.x + t.y) AS k
All column references in Entity SQL must be qualified with the table alias. The following construct (assuming that a
is a valid column of table T
) is valid in database SQL but not in Entity SQL.
SELECT a FROM T
The Entity SQL form is
SELECT t.a AS A FROM T AS t
The table aliases are optional in the from
clause. The name of the table is used as the implicit alias. Entity SQL allows the following form as well:
SELECT Tab.a FROM Tab
Database SQL uses the "." notation for referencing columns of (a row of) a table. Entity SQL extends this notation (borrowed from programming languages) to support navigation through properties of an object.
For example, if p
is an expression of type Person, the following is the Entity SQL syntax for referencing the city of the address of this person.
p.Address.City
In database SQL, if you want to refer to a collection of possible values, you would use an IN clause together with a set of values contained within parenthesis:
SQL
SELECT t.a FROM T as t WHERE t.b IN (1,2,3) In Entity SQL, the syntax for a collection of values is based on braces / curly brackets instead:
ESQL
select t.a from T as t where t.b in { 1,2,3 }
There are some differences between how literal values and types are represented in Entity SQL vs. Database SQL:
DATETIME '2006-12-25 01:01:00.000'
N'hello'
.
(cast(null as Int16))
The following database SQL functionality is not available in Entity SQL.
Now that we have discussed the differences between traditional database SQL and Entity SQL, in our last article in this series, we shall cover come more advanced queries and functions that customers typically will want to use when creating custom graphs with Spira.
And if you have any questions, please email or call us at +1 (202) 558-6885