ELMA SQL

Description

To create queries in report data sources, you can use the text generation language. It allows including syntax structures that simplify the work with system parameters and objects.

Enumerations

To get the constant value of an enumeration in an SQL query, regardless of the database language this structure is used:
{#EnumSQL.%EnumerationName%.%EnumerationValueName%} 
To get the constant value of an enumeration in an HQL query, this structure is used:
{#Enum.%EnumerationName%.%EnumerationValueName%} 

For example, to select all the locked users, you can execute the following query:

SELECT *
FROM [User]
WHERE [User].Status = {#EnumSQL.UserStatus.Blocked}

If the enumeration value is a UID, the text generator will automatically convert it to the form applicable in the current database language.

Entity types

To specify UIDs of entity types in queries, you can use the following structure:
{#Type.%EntityTypeName%} 
When executing the query it will be replaced with the entity UID, converted in accordance with the current database language. 

For example, to select the task history, you can execute the following query:

SELECT *
FROM [EntityActionHistory]
WHERE [EntityActionHistory].ActionObjectUid = {#Type.Task}

Entity Actions

To specify UIDs of entity actions in a query, use the following structure:
{#Action.%EntityTypeName%.%EventName%} 
When executing the query, it will be replaced with the entity action UID converted in accordance with the current database language. 

For example, to select the history of user tasks you can execute the following query:

SELECT *
FROM [EntityActionHistory]
WHERE [EntityActionHistory].ActionObjectUid = {#Type.Task} AND [EntityActionHistory].ActionTypeUid = {#Action.Task.Complete}

When using the #Action structure, keep in mind that an entity action and its child elements have the same UID. For example, {#Action.TaskBase.Complete}, {#Action.Task.Complete} and {#Action.ProjectTask.Complete} will return the same value.
Parameters

To reference simple report parameters in queries, you can use this structure:
{$%ParameterName%}
To reference report parameter properties in queries, you can use this structure:
{$%ParameterName%.%PropertyName%}

For example, to generate a script depending on the selected script method and user, you can execute the following query:

SELECT CAST(’javascript: {$Stroka}({$Poljzovatelj.Id})’ as varchar(100)) UsersLinks
FROM "User"
WHERE "User".ID = {$Poljzovatelj.Id}

To create a query, you can use if-else switch statements:
{if %Condition%} %ActionIfConditionIsTrue% {end if}
or
{if %Condition%} %ActionIfConditionIsTrue% {else} %ActionIfConditionIsFalse% {end if}

For example, to select a user's full name or login depending on the specified parameter you can execute the following query:

{if {$Check} = True}
SELECT "User".FullName ShowName
FROM "User"
{else}
SELECT "User".UserName ShowName
FROM "User"
{end if}