Saltar al contenido principal
Version: 4.6.0

Query definition

The AWE query engine is used for querying data on external systems. It works as an interface.

engine
Note: All queries are defined in the queries.xml file at global folder. View project structure for more info. :::

SQL query

This section describes how database queries are handled with the AWE query engine.

XML sql structure

The complete sql query structure is the following:

<!-- Example sql query -->

<query id="[Query Id]" cacheable="[Cacheable]" distinct="Distinct" managed-pagination="Pagination" post-process="Post processed">
<table id="[Table id]" schema="[Schema name]" alias="[Table alias] query="[Subquery]"/>
<field id="[Field id]" table="[Table field]" alias="[Alias field]"/>
...
<field id="[Field id]" table="[Table field]" alias="[Alias field]"/>
<field variable="[Variable id]"/>
<constant value="[Constant value]" type="INTEGER"/>
<computed format="[Format]" alias="[Alias] transform="[Transform]"/>
...
<computed format="[Format]" alias="[Alias] transform="[Transform]"/>
<compound alias="[Compoun alias]">
<computed format="[Format]" alias="[Alias]"/>
...
<computed format="[Format]" alias="[Alias]"/>
</compound>
<join type="[Type join]">
<table id="[Table id]" alias="[Table alias]"/>
<and>
<filter left-field="[Field]" left-table="[Table]" condition="[Condition]" right-field="[Counterfield]"
right-table="[Countertable]"/>
...
<filter left-field="[Field]" left-table="[Table]" condition="[Condition]" right-variable="[Variable]"/>
</and>
</join>
...
<union type="[union_type]" query="[Union subquery]"/>
<where>
<and>
<filter left-field="[Filter field]" table="[Filter table]" condition="[Condition]" right-variable="[Variable]" optional="[Filter Optional]"/>
<filter left-field="[Field 1]" table="[Filter table]" condition="[Condition]"
right-field="[Field 2]" right-table="[Table 2]" ignorecase="[Ignorecase]" trim="[Trim]"/>
<filter left-field="[Field]" table="[Filter table]" condition="[Condition]" right-query="[Subquery]"/>
<or>
... (more filters or filter groups)
</or>
... (more filters or filter groups)
</and>
<or>
... (more filters or filter groups)
</or>
</where>
<variable id="[Variable id]" type="[Variable Type]" name="[Variable name]" optional="[Optional]"/>
... (More <variable>)
<group_by field="[Group field]" table="[Group table]" />
... (More <group_by>)
<order-by field="[Order field]" table="[Order table]" type="[Order type]"/>
... (More <order-by >)
<totalize function="[Totalize function]" label="[Label]" field="[Totalize field]" style="[Totalize style]">
<totalize_by field="[Totalize by field]"/>
... (more totalize by fields)
<totalize_field field="[Totalize field]"/>
... (more totalized fields)
</totalize>
</query>

Global Sql query structure

To simplify the development of queries, not all elements are required.

ElementoUsoVarias instanciasDescripción
queryObligatorioNoOutlines the query. Also describes the kind of query (service, queue, etc)
tableObligatorioSiThe table or table list over which the query is performed
fieldObligatorioSiDescribes the column of the table
computedOpcionalSiComputed elements are used for retrieving query fields from other columns (field)
compoundOpcionalSiCompound elements are a computed list. Used for retrieving complex structures
joinOpcionalSiUsed to make joins between tables
unionOpcionalSiUsed to make unions between tables
whereOpcionalNoWhere clause in sql query. It has the condition list of fields
havingOpcionalNoHaving clause in sql query. It has the condition list of fields for functions
group-byOpcionalSiGroup by clause in sql query
order-byOpcionalSiOrder by clause in sql query
totalizeOpcionalSiUsed to totalize the query result
variableOpcionalSiParameters passed from screens to query

Query element

The query element has the following attributes:

AtributoUsoTipoDescripciónValores
idObligatorioStringQuery identifierNote: The id name must be unique
distinctOpcionalBooleanUsed to return only distinct (different) valuesBy default is false
cacheableOpcionalBooleanUsed to set a query as cacheable (in order to save data in memory and avoid executing the query again)By default is false. Note: If you set a query as cacheable and there are subqueries, you must define on it the same variables that all subqueries have.
managed-paginationOpcionalBooleanTo set a query as paginated (to load just the data in that specific page, not all query registries). Used to achieve high performance in queries with a very high number of records.By default is false. Note: Use this parameter only in queries without totalize.
post-processOpcionalBooleanLaunch or not the processing of the datalist that AWE performs, in order to to allow developers to do their own post processingBy default is true. Note: Only apply in service queries. For more info. see service query
publicOpcionalBooleanAllows the query to run without the need to be loggedBy default is false
enumeratedOpcionalStringThe name of enumerate to fill the queryNote: Only applies in enumerated queries. For more info. see enumerated query
serviceOpcionalStringThe name of service to fill the queryNote: Only applies in service queries. For more info. see service query
queueOpcionalStringThe name of queue to fill the queryNote: Only applies in queue queries. For more info. see queue query

Table element

The table element has the following attributes:

AtributoUsoTipoDescripciónValores
idObligatorioStringName of the tableNote: Actual table name in data base
schemaOpcionalStringSchema of the table. It is used to set the user owner of tableNote: It is the actuala schema (user) name in data base
aliasOpcionalStringAlias of the table. It is used to temporarily rename a table or a column heading
queryOpcionalStringId of a subquery to be used as data source

Subquery example


<query id="GetProcessedAccounting" distinct="true">
<field id="Name" alias="nameCol" table="a" />
<field id="Created_Date" alias="createdDateCol" table="a"/>
<field id="Type" alias="typeCol" table="a"/>
<field id="ddo.OperationsCol" />
<table id="ACCOUNTING" alias="a" />
<join type="LEFT">
<!-- Use Subquery -->
<table alias="ddo" query="GetProcessedAccountingDirectDebit"/>
<and>
<filter left-field="IdCol" left-table="ddo" condition="eq" right-field="Id" right-table="a" />
</and>
</join>
</query>

<!-- Define Subquery -->
<query id="GetProcessedAccountingDirectDebit">
<field id="Id" alias="idCol" table="a" />
<field id="Id" alias="OperationsCol" function="COUNT" table="a"/>
<table id="ACCOUNTING" alias="a" />
<join type="LEFT">
<table id="ADEUDO_OPERACION" alias="o"/>
<and>
<filter left-field="Accounting_Id" left-table="o" condition="eq" right-field="Id" right-table="a" />
</and>
</join>
<where>
<and>
<filter left-field="Accounting_Id" left-table="o" condition="is not null" />
</and>
</where>
<group-by field="Id" table="a"/>
</query>

Field element

The field element has the following attributes:

AtributoUsoTipoDescripciónValores
idOpcionalStringName of fieldNote: Actual column name of table in data base
tableOpcionalStringTable of the field
aliasOpcionalStringAlias of the field. Used to describe the field
noprintOpcionalBooleanUsed to set a field as no printable. (Field value won't be loaded in the resultSet)
transformOpcionalStringUsed to format the field valueCheck out this for more info about transform attribute.
patternOpcionalStringUsed in a number type field, defines the pattern to format the numberCheck out this page for more info
translateOpcionalStringTranslates the output with an enumerated group identifierNote: If the field value is equal to an enumerated value, outputs the enumerated label
functionOpcionalStringTo apply an sql function to fieldThe possible values are defined in field functions
castOpcionalStringChanges the field formatThe possible values are STRING, INTEGER, LONG, FLOAT and DOUBLE
queryOpcionalStringQuery identifier to do a subqueryNote: The query id must exist, and table and id attributes will be ignored
variableOpcionalStringA variable identifier to be used as field valueNote: If variable attribute is defined, table and id attributes will be ignored

Note: The order in attribute reading for fields is the following: 1. query 2. variable 3. id (and table if defined)

At least one of the previous attributes is required in a field.

Field functions

  • ABS: Absolute value
  • AVG: Values average
  • CNT: Count values
  • CNT_DISTINCT: Count distinct values
  • MAX: Max value
  • MIN: Min value
  • SUM: Sum values
  • ROW_NUMBER: Row number
  • RANK: Aggregation rank
  • TRUNCDATE (not standard): Truncate date
  • YEAR: Retrieve year from date
  • MONTH: Retrieve month from date
  • DAY: Retrieve day from date
  • HOUR: Retrieve hours from date
  • MINUTE: Retrieve minutes from date
  • SECOND: Retrieve seconds from date
  • TRIM: Remove all spaces from both sides of the string

Transform attribute

These are the possible values for the transform attribute:

  • DATE: Transforms the output field (Date/String) into a web date field (dd/MM/yyyy)
  • DATE_MS: Transforms the output field (Date/String) into a java date in milliseconds (for chart datetime axes)
  • TIME: Transforms the output field (Date/String) into a web time field (HH:mm:ss)
  • TIMESTAMP: Transforms the output field (Date/String) into a web timestamp field (dd/MM/yyyy HH:mm:ss)
  • TIMESTAMP_MS: Transforms the output field (Date/String) into a web timestamp field with milliseconds (dd/MM/yyyy HH:mm:ss.SSS)
  • JS_DATE: Transforms the output field (Date/String) into a javascript date field (for chart axes) (MM/dd/yyyy)
  • JS_TIMESTAMP: Transforms the output field (Date/String) into a javascript timestamp field (MM/dd/yyyy HH:mm:ss)
  • GENERIC_DATE: Transforms the output field (String) from a date format defined on format-from to a date format defined on format-to
  • DATE_RDB: Transforms the output field (String) from an English RDF format (dd-MMM-yyyy) to a web date field (dd/MM/yyyy)
  • ELAPSED_TIME: Transforms the output field (Long) from a long millisecond value to a localized string indicating the elapsed time (12h)
  • DATE_SINCE: Transforms the output field (Date) into a localized string with the time difference from now (5 min ago)
  • NUMBER: Transforms the output field as a number with a pattern. IMPORTANT:
  • When using this transform, the associated pattern has to have thousands separator. For instance: ###,###.00
  • NEVER use this transform if the retrieved data is for a numeric component
  • This transform is normally used when we want to show a numeric value in a visualization grid (columns without component)
  • NUMBER_PLAIN: Transforms the output field as a number with a raw pattern (without thousand separator). IMPORTANT:
  • When using this transform, the associated pattern must not have a thousands separator (e.g: ###.00)
  • It can be used for numeric components and elements that have no component.
  • This transform is normally used when we want to print numeric components and specify the number of decimals we want to see in the pdf file. Usually, the number of decimals of the pattern will match the "precision" defined in the number-format attribute of the numeric component.
  • BOOLEAN: Transforms the output field as a boolean value (true/false):
  • TEXT_HTML: Transforms the output field into HTML text (to be showed in a HTML page)
  • TEXT_PLAIN: Transforms the output field into plain text (to be showed inside a document)
  • TEXT_UNILINE: Transforms the output field into a plain text without line breaks
  • MARKDOWN_HTML: Transforms the output field from Markdown into HTML text (to be showed in a HTML page)
  • DECRYPT: Decrypts a column value which is encrypted in the database
  • ARRAY: Splits a string value with the string in pattern attribute

Constant element

The constant element has the following attributes:

AtributoUsoTipoDescripciónValores
aliasOpcionalStringAlias of the field. It used to describe the field
noprintOpcionalBooleanUsed to set a field as no printable. (Field value won't be loaded in resultSet)
transformOpcionalStringUsed to format the field valueRead this for more info about transform attribute.
patternOpcionalStringUsed in a number type field, defines the pattern to format the numberRead this page for more info
translateOpcionalStringTranslates the output with an enumerated group identifierNote: If the field value is equal to an enumerated value, output the enumerated label
functionOpcionalStringTo apply an sql function to fieldThe possible values are defined in field functions
castOpcionalStringChanges the field formatThe possible values are STRING, INTEGER, LONG, FLOAT and DOUBLE
valueObligatorioStringA static value to be used as field value
typeOpcionalStringType of the valueThe possible values are available here

Operation element

The operation element allows to define operations between fields and will be resolved as SQL clauses:

<operation operator="[operator]" alias="[alias]">
<constant value="[constant value]" />
<field id="[field name]" table="[field table]" />
...
</field>
AtributoUsoTipoDescripciónValores
operatorObligatorioStringOperator of the operationSee operator attribute
aliasOpcionalStringAlias of field. It used to describe the field
noprintOpcionalBooleanUsed to set a field as no print. (Field value isn't loaded in resultset)
transformOpcionalStringUsed to format the field valueSee this for more info about transform attribute.
patternOpcionalStringUsed in a number type value, defines the pattern to format the numberSee this page for more info
translateOpcionalStringTranslates the output with an enumerated group identifierNote: If the field value is equal to an enumerated value, output the enumerated label
functionOpcionalStringTo apply sql function to fieldThe possible values are defined in field functions
castOpcionalStringChange the field formatThe possible values are STRING, INTEGER, LONG, FLOAT and DOUBLE

Operator attribute

These are the possible values for the operator attribute:

  • CONCAT: Concats some string fields
  • REPLACE: Replace on the first field the string defined on second place with the string defined on third place
  • NULLIF: Sets null if equals to second operand
  • COALESCE: Given a set of fields, returns the first one which is NOT NULL
  • ADD: Sums two fields (+)
  • SUB: Substracts two fields (-)
  • MULT: Multiplies two fields (*)
  • DIV: Divides two fields (/)
  • MOD: Returns the remainder or signed remainder of a division, after one number is divided by another (called the modulus of the operation). (%)
  • POWER: The first field is raised to the second field power (^)
  • ROUND: Replacing a number with an approximate value
  • ADD_SECONDS: Adds seconds to a date field
  • ADD_MINUTES: Adds minutes to a date field
  • ADD_HOURS: Adds hours to a date field
  • ADD_DAYS: Adds days to a date field
  • ADD_WEEKS: Adds weeks to a date field
  • ADD_MONTHS: Adds months to a date field
  • ADD_YEARS: Adds years to a date field
  • DIFF_SECONDS: Calculates the difference in seconds between two dates
  • DIFF_MINUTES: Calculates the difference in minutes between two dates
  • DIFF_HOURS: Calculates the difference in hours between two dates
  • DIFF_DAYS: Calculates the difference in days between two dates
  • DIFF_WEEKS: Calculates the difference in weeks between two dates
  • DIFF_MONTHS: Calculates the difference in months between two dates
  • DIFF_YEARS: Calculates the difference in years between two dates
  • SUB_SECONDS: Substracts seconds from a date field
  • SUB_MINUTES: Substracts minutes from a date field
  • SUB_HOURS: Substracts hours from a date field
  • SUB_DAYS: Substracts days from a date field
  • SUB_WEEKS: Substracts weeks from a date field
  • SUB_MONTHS: Substracts months from a date field
  • SUB_YEARS: Substracts years from a date field

Operation examples

Concatenated field: ("Pro" + pro.Nam + "-Mod" + mod.Nam) as parent

<operation operator="CONCAT" alias="parent">
<constant value="Pro" />
<field id="Nam" table="pro" />
<constant value="-Mod" />
<field id="Nam" table="mod" />
</field>

Add 1 to a field: (pro.Nam + 1) as parent

<operation operator="ADD" alias="parent">
<field id="Nam" table="pro" />
<constant value="1" type="INTEGER"/>
</field>

Round field with 2 decimals: 'round(column, 2)'

<operation operator="ROUND" alias="roundField">
<field id="Rate" table="User" />
<constant value="2" type="INTEGER"/>
</field>

Case element

The case element allows to generate a list of when clauses inside a field element. An else clause must be defined at the end of the case clause. It has the same attributes than a filter element plus some extra features:

AtributoUsoTipoDescripciónValores
aliasOpcionalStringAlias of field. It used to describe the field
noprintOpcionalBooleanUsed to set a field as no printable. (Field value isn't loaded in resultset)
transformOpcionalStringUsed to format the field valueSee this for more info about transform attribute.
patternOpcionalStringUsed in a number type field, defines the pattern to format the numberSee this page for more info
translateOpcionalStringTranslates the output with an enumerated group identifierNote: If the field value is equal to an enumerated value, output the enumerated label
functionOpcionalStringTo apply sql function to fieldThe possible values are defined in field functions
castOpcionalStringChanges the field formatThe possible values are STRING, INTEGER, LONG, FLOAT and DOUBLE

NEW!: As described on filter element, left-operand and right-operand must contain a node of field, constant, operation or case as well. Same case for the then and else elements.

NEW!: You can use multiple filters inside when clause using and clause as described here.

Case examples

Case field:

CASE WHEN (Nam = "sunset") THEN 1 WHEN (Nam = "sunny") THEN 2 WHEN (Nam = "purple-hills") THEN 3 ELSE 0 END AS "value"

will be generated as:

<query id="testCaseWhenElse">
<table id="AweThm"/>
<case alias="value">
<when condition="eq">
<left-operand>
<field id="Nam"/>
</left-operand>
<right-operand>
<field variable="sunset"/>
</right-operand>
<then>
<constant value="1" type="INTEGER"/>
</then>
</when>
<when left-field="Nam" condition="eq" right-variable="sunny">
<then>
<constant value="2" type="INTEGER"/>
</then>
</when>
<when left-field="Nam" condition="eq" right-variable="purple-hills">
<then>
<constant value="3" type="INTEGER"/>
</then>
</when>
<else>
<constant value="0" type="INTEGER"/>
</else>
</case>
<variable id="sunset" type="STRING" value="sunset"/>
<variable id="sunny" type="STRING" value="sunny"/>
<variable id="purple-hills" type="STRING" value="purple-hills"/>
</query>

Case field:

CASE WHEN (Nam = "sunset" AND Thm = "summer") THEN 1 ELSE 0 END AS "value"

will be generated as:

<query id="testCaseWhenMultipleFilters">
<table id="AweThm"/>
<case alias="Nam">
<when>
<and>
<filter condition="eq">
<left-operand>
<field id="Nam"/>
</left-operand>
<right-operand>
<field variable="sunset"/>
</right-operand>
</filter>
<filter condition="eq">
<left-operand>
<field id="Thm"/>
</left-operand>
<right-operand>
<field variable="summer"/>
</right-operand>
</filter>
</and>
<then>
<constant value="1" type="INTEGER"/>
</then>
</when>
<else>
<constant value="0" type="INTEGER"/>
</else>
</case>
<variable id="sunset" type="STRING" value="sunset"/>
<variable id="summer" type="STRING" value="summer"/>
</query>

Over element

The over element allows to modelate SQL window functions. This element contains a field clause (field, constant, operation or case) and some of partition-by or order-by clauses.

AtributoUsoTipoDescripciónValores
aliasOpcionalStringAlias of the field. Used to describe the field
noprintOpcionalBooleanUsed to set a field as no printable. (Field value won't be loaded in resultSet)
transformOpcionalStringUsed to format the field valueSee this for more info about transform attribute.
patternOpcionalStringUsed in a number type value, defines the pattern to format the numberSee this page for more info
translateOpcionalStringTranslates the output with an enumerated group identifierNote: If the field value is equal to an enumerated value, output the enumerated label
functionOpcionalStringTo apply an sql function to fieldThe possible values are defined in over functions
castOpcionalStringChanges the field formatThe possible values are STRING, INTEGER, LONG, FLOAT and DOUBLE

Over functions

  • AVG: Values average
  • CNT: Counts values
  • CNT_DISTINCT: Counts distinct values
  • MAX: Max value
  • MIN: Min value
  • SUM: Sum values
  • FIRST_VALUE: First value
  • LAST_VALUE: Last value
  • LAG: Lag
  • ROW_NUMBER: Row number
  • TRUNCDATE (not standard): Truncates date

Over examples

Over field:

SELECT MAX(date) OVER (PARTITION BY name ORDER BY position ASC) as `maxValue` FROM tableId

will be generated as:

<query id="testOver">
<table id="tableId"/>
<over alias="maxValue">
<field id="date" function="MAX"/>
<partition-by field="name"/>
<order-by field="position" type="ASC"/>
</over>
</query>

Computed element

The computed element has the following attributes:

AtributoUsoTipoDescripciónValores
aliasObligatorioStringComputed field output name
formatObligatorioStringUsed to insert another field alias as variables. It has the same syntax as the javascript eval element(Ex. [code] - [description] will take the code field and concatenate it with the description field with a " - " string
evalOpcionalBooleanEvaluates computed format as expressionBy defaul is false
nullValueOpcionalStringSets a value to null values in computed fieldsEx: nullValue="ZERO" set "ZERO" to null values
transformOpcionalStringUsed to format the computed valueSee this for more info about transform attribute.
patternOpcionalStringUsed in a computed with numeric value, defines the pattern to format the numberSee this page for more info
translateOpcionalStringTranslates the output with an enumerated group identifierNote: If the field value is equal to an enumerated value, output the enumerated label
labelOpcionalStringTo use an international i18n label in the computedNote: You can use i18n files (locales)

Computed examples

<!-- Computed for add string "Prueba" to field Nam -->
<query id="QryEdiSug" cacheable="true">
<table id="AweThm"/>
<field id="IdeThm" alias="value" />
<field id="Nam" alias="name" />
<computed alias="label" format="Prueba - [name]"/>
<where>
<or>
<filter left-field="Nam" condition="like" right-variable="Nam" ignorecase="true"/>
</or>
</where>
<variable id="Nam" type="STRINGB" name="suggest" />
</query>
<!-- Using computed to get value of field "Value" as a label field -->
<query id="ProNamLst" service="ProFilLst" cacheable="true">
<field id="value" />
<computed format="[value]" alias="label" />
</query>

Compound element

The compound structure is the next one:

<compound alias="[Compound alias]">
<computed format="[Format]" alias="[Alias]"/>
<computed format="[Format]" alias="[Alias]"/>
...
</compound>

The compound element has the following attributes:

AtributoUsoTipoDescripciónValores
aliasObligatorioStringIs the compound identifierNote: The alias name must be unique in the query

Compound examples

Use compound element to get complex output structures.

<!-- This compound get label and icon from many computeds-->

<query id="DbsLst" cacheable="true">
<table id="AweDbs"/>
<field id="IdeDbs" alias="IdeDbs" />
<field id="Als" alias="Als" />
<field id="Des" alias="Des" />
<field id="Act" alias="Act" />
<field id="Act" alias="ActTxt" translate="Es1Es0"/>
<compound alias="ActIco">
<computed format="GENERAL_STATUS_FA_[Act]" alias="icon"/>
<computed format="[ActTxt]" alias="label"/>
</compound>
</query>

Usage of the icon compound:

  • The compound alias must match the icon field id in the grid.
  • The computed element with the alias icon contains the icon to be set the the icon field. In this case, there is an enum with the icons identifier.
  • The computed element with the alias label contains the string to be shown on mosue over.
<!-- This compound get label and value as [Nam] field from many computeds fields -->

<query id="ScrCnfLst" cacheable="true">
<table id="AweScrCnf" alias="scrCnf"/>
<field id="IdeAweScrCnf" table="scrCnf" alias="IdeAweScrCnf" />
<field id="IdeOpe" table="scrCnf" alias="IdeOpe" />
<field id="IdePro" table="scrCnf" alias="IdePro" />
<field id="Nam" table="scrCnf" alias="NamVal" />
<compound alias="Nam">
<computed format="[NamVal]" alias="value"/>
<computed format="[NamVal]" alias="label"/>
</compound>
<where>
<and>
<filter left-field="Act" left-table="scrCnf" condition="eq" right-variable="Act" optional="true"/>
</and>
</where>
<variable id="Act" type="INTEGER" name="CrtAct" />
</query>

Join element

The join structure is the next one:

<query id="query">
...
<join type="[Type]">
<table id="[Table id]" alias="[Table alias]"/>
<and>
<filter left-field="[Join field 1]" left-table="[Table join 1]" condition="eq"
right-field="[Join field 2]" right-table="[Table join 2]" ignorecase="[Ignorecase]" trim="[Trim]"/>
</and>
</join>
... (more joins)
...
</query>

The join element has the following attributes:

AtributoUsoTipoDescripciónValores
typeOpcionalStringSQL join typeThe possible values are: FULL, LEFT, INNER or RIGHT Note: Default value is INNER. To see more info about joins read this page

Union element

The union structure is the next one:

...
<query id="query">
<union type="[Type]" query="[Union subquery]"/>
... (more unions)
</query>
...

The union element has the following attributes:

AtributoUsoTipoDescripciónValores
queryObligatorioStringQuery id to combine the resultNote: The alias query must be exist
typeOpcionalStringCombines the result of two or more SELECT statementsUse ALL to allow duplicate values

Where element

The where element structure is the following one:

  <query id="WhereTest">
<table id="HISAweThm" />
<field id="hisact" alias="typ" />
<where>
<and>
<filter left-field="hisact" condition="eq" right-variable="top" />
<filter left-field="hisope" condition="ne" right-variable="ope" />
</and>
</where>
<variable id="top" type="INTEGER" value="1445" />
<variable id="ope" type="STRING" value="mgr" />
</query>

Having element

The having structure is the next one, is the same as where element:

  <query id="HavTst" public="true">
<table id="HISAweThm" />
<field id="hisact" alias="typ" />
<field id="sum(1)" alias="act"/>
<group-by field="HisAct"/>
<having>
<and>
<filter left-field="sum(1)" condition="gt" right-variable="top" />
</and>
</having>
<variable id="top" type="INTEGER" value="1445" />
</query>

Filter element

The filter structure is as follows:

<filter left-field="[Field 1]" left-table="[Field table 1]" left-variable="[Variable Id]" condition="[Condition]" type="[Type]"  
right-field="[Field 2]" right-table="[Field table 2]" right-variable="[Variable Id]" query="[Query Id]" ignorecase="[Ignorecase]" trim="[Trim]" optional="[Optional]"/>

NEW! Now you can define a left-operand and a right-operand children to define the filters. These elements must contain field, constant, operation, case or over elements:

<filter condition="[Condition]" ignorecase="[Ignorecase]" trim="[Trim]" optional="[Optional]">
<left-operand>
<field id="[field name]"/>
</left-operand>
<right-operand>
<constant value="[static value]" type="[value type]"/>
</right-operand>
</filter>

The filter element has the following attributes:

AtributoUsoTipoDescripciónValores
left-fieldOpcionalStringThe name of a column
left-tableOpcionalStringThe name of the table that field belongs to
left-variableOpcionalStringThe id of a variable
typeOpcionalStringThe type of values stored in columns being comparedNUMBER, DECIMAL_NUMBER, DATE, TIME, STRING (default)
conditionObligatorioStringThe condition of the comparisonSee comparison conditions
right-fieldOpcionalStringThe name of a column
right-tableOpcionalStringThe name of the table that right-field belongs to
right-variableOpcionalStringThe id of a variable
queryOpcionalStringThe id of a query to compare (right side)
ignorecaseOpcionalBooleanIf comparison should ignore casetrue, false (default)
trimOpcionalBooleanIf values should be trimmed before comparisontrue, false (default)
optionalOpcionalBooleanIf this filter is checking versus a variable and the variable value is null, then remove this filtertrue, false (default)

Comparison conditions

  • eq: Equals
  • ne: Not equals
  • ge: Greater or equal
  • le: Lower or equal
  • gt: Greater than
  • lt: Lower than
  • in: First operand is in a list defined by second operand (subquery or variable list)
  • not in: First operand is not in a list defined by second operand (subquery or variable list)
  • is null: First operand is null
  • is not null: First operand is not null
  • like: First operand contains some text of second operand
  • not like: First operand does not contain some text of second operand
  • exists: For queries only, subquery contains values
  • not exists: For queries only, subquery doesn't contain values

Group by element

The group by element has the following attributes:

AtributoUsoTipoDescripciónValores
fieldObligatorioStringField alias to group the results
tableOpcionalStringTable alias to group the results
functionOpcionalStringFunction to apply to the fieldThe possible values are defined in field functions

Also, you can use CASE operation inside group-by.

Order by element

The order by element has the following attributes:

AtributoUsoTipoDescripciónValores
fieldObligatorioStringField alias to order the results
tableOpcionalStringTable alias to order the result
functionOpcionalStringFunction to apply to the fieldThe possible values are defined in field functions
typeOpcionalStringOrder directionThe possible values are DESC or ASC. By default is ASC
nullsOpcionalStringWhether to sort the null fieldsThe possible values are FIRST or LAST. By default depends on database type

Totalize element

The totalize structure is the next one:

<query id="query">
...
<totalize function="[Function]" label="[Label]" field="[Field]" style="[Style]">
<totalize-by field="[Totalize field]"/>
... (more totalize by fields)
<totalize-field field="[Totalize field]"/>
... (more totalized fields)
</totalize>
... (more totalize)
...
</query>

The totalize element has the following attributes:

AtributoUsoTipoDescripciónValores
functionObligatorioStringFunction used to generate the totalThe possible values are AVG, CNT, MAX, MIN or SUM)
labelObligatorioStringLabel of the text to appear on totalizer-field in totalized rows
fieldObligatorioStringField where the totalizer label is going to be shown
styleObligatorioStringIs the css style to set in grid wigetThe possible values are TOTAL or SUBTOTAL

Totalize element has the following elements:

ElementoUsoVarias instanciasDescripción
totalize-fieldObligatorioSiHas field attribute to set the field alias to apply the totalization
totalize-byObligatorioSiHas field attribute to set the field alias to group in the totalization
Totalize examples
<!-- Test matrix with totalizer -->
<query id="QrySitModDbsOrdTot" cacheable="true">
<table query="QrySitModDbsOrd" alias="TotLst"/>
<field id="IdeSitModDbs" table="TotLst" alias="IdeSitModDbs"/>
<field id="IdeSit" table="TotLst" alias="IdeSit"/>
<field id="NamSit" table="TotLst" alias="NamSit"/>
<field id="IdeMod" table="TotLst" alias="IdeMod"/>
<field id="NamMod" table="TotLst" alias="NamMod"/>
<field id="IdeDbs" table="TotLst" alias="IdeDbs"/>
<field id="Als" table="TotLst" alias="Als"/>
<field id="Ord" table="TotLst" alias="Ord" transform="NUMBER"/>
<totalize function="SUM" label="Subtotal" field="NamMod" style="SUBTOTAL">
<totalize-field field="Ord"/>
<totalize-by field="IdeMod"/>
</totalize>
<totalize function="SUM" label="Total" field="Als" style="TOTAL">
<totalize-field field="Ord"/>
</totalize>
</query>

Variable element

The variable element has the following attributes:

AtributoUsoTipoDescripciónValores
idObligatorioStringIdentifier of variableNote: The id must be unique
typeObligatorioStringVariable typeThe possible values are available here
nameOpcionalStringVariable name. It's the name of the component we are interacting with in the screenNote: In some cases it might be useful to know the name of the criterion we are interacting with. If we define the variable as name="component", it will send the id of the criterion
valueOpcionalStringTo define a static value of the variable
sessionOpcionalStringVariable is set by a session value
propertyOpcionalStringVariable is set by a property value
optionalOpcionalBooleanFlag to indicate if variable is optional. If the criterion configured in the variable could exist or not. If it is optional and does not exist, the query won't be executed. If it is not optional and the variable does not exist an error will be shown.Note: It's not recommended to configure suggest type criteria as optional, because it might lead to misbehaviour

Variable types

These are the possible variable types:

  • STRINGL: a string with % at left side (for LIKE operator)
  • STRINGR: a string with % at right side (for LIKE operator)
  • STRINGB: a string with % at both sides (for LIKE operator)
  • STRINGN: a string that has NULL when it's empty
  • STRING: a string that has "" when it's empty
  • STRING_HASH: applies Sha 256 function to string variable
  • STRING_ENCRYPT: applies encryptRipEmd160 function to string variable
  • INTEGER: integer number
  • FLOAT: float number (32 bits)
  • DOUBLE: double number (64 bits)
  • DATE: web date (dd/MM/aaaa)
  • TIME: web time (HH:mm:ss)
  • TIMESTAMP: web timestamp (dd/MM/aaaa HH:mm:ss)
  • SYSTEM_DATE: Server Date (stored as date) (dd/MM/aaaa)
  • SYSTEM_TIME: Server time (Stored as string) (HH:mm:ss)
  • SYSTEM_TIMESTAMP: Server Date (stored as timestamp with milliseconds) (dd/MM/aaaa HH:mm:ss.SSS)
  • NULL: To pass a null value
  • OBJECT: To define a variable as a java object
  • CLOB: To define a variable as a big text file
  • LIST_TO_STRING: Retrieve a list of values and manage them as a comma separated values in a string
  • STRING_TO_LIST: Retrieve comma separated string and transforms it into a list of values

Enumerated query

An enumerated query is a call to an enumerated group in the Enumerated.xml file.

It will not receive any input variables, and will return a list with two fields: value and label.

You can check out enumerate xml structure in this page

Xml enumerated structure

<!-- Example enumerated query -->
<query id="[Query id]" enumerated="[Id enumerated]" cacheable="[cacheable]">
<field id="[Field label]"/>
<field id="[Field value]"/>
</query>

Note: All enumerated are defined in the Enumerated.xml file at global folder. View project structure for more info.

Ejemplos de enumerados

<!-- Enumerated YES:1|NO:0 -->
<query id="Es1Es0" enumerated="Es1Es0">
<field id="value"/>
<field id="label"/>
</query>
<!-- Enumerated YES:Y|NO:N -->
<query id="EsyEnn" enumerated="EsyEnn">
<field id="value"/>
<field id="label"/>
</query>

Service query

Special type of queries used to call services, either if they are java or web services. A service query is composed by input variables and output fields.

You can view services xml structure in this page

Note: All services are defined in the Services.xml file at global folder. View project structure for more info.

Xml service structure

<query id="[Query ID]" service="[Service ID]" public="[Public]">
<field id="[Field Id 1]"/>
...
<field id="[Field Id n]"/>
<computed alias="[Alias]" format="[Format]"/>
...
<computed alias="[Alias]" format="[Format]"/>
<variable id="[Variable ID1]" type="[Variable type]" name="[Variable name 1]" />
...
<variable id="[Variable IDn]" type="[Variable type]" name="[Variable name N]" />
<order-by field="[Order field]" table="[Order table]" type="[Order type]" nulls="[Nulls first or last]"/>
</query>

IMPORTANT: The order of the fields in query.xml must be the same as defined in the service in Services.xml file

Service examples

  • Example of java service:

Code of query xml

<!-- Encrypt text (Service encryptText) -->
<query id="GetEncTxt" service="SerEncTxt" cacheable="true">
<field id="value" />
<field id="label" />
<variable id="text" type="STRING" name="CrtTxt"/>
<variable id="phraseKey" type="STRING" name="CrtPhr"/>
</query>

Code of service xml

<service id="SerEncTxt">
<java classname="com.almis.awe.core.services.controller.AccessController" method="encryptText">
<service-parameter type="STRING" name="text" />
<service-parameter type="STRING" name="phraseKey" />
</java>
</service>
  • Example of web service:

Code of query xml

<query id="BoCptMomLiqTyp" service="FmbBoCptMomLiqTyp">
<field id="value"/>
<field id="orp_des"/>
<field id="lab"/>
<field id="orp_ext"/>
<computed alias="label" format="[orp_des] - [lab] - [orp_ext]"/>
<variable id="FldIde" type="STRING" value="LiqTyp" optional="false"/>
<variable id="LiqTyp" type="STRING" name="LiqTyp" optional="false"/>
</query>

Code of service xml

<service id="FmbBoCptMomLiqTyp">
<web name="FmbBoCptMomLiqTyp" type="DATA">
<service-parameter type="STRING" name="FldIde" list="false"/>
<service-parameter type="STRING" name="LiqTyp" list="false"/>
</web>
</service>

Queue query

Special type of queries used to communicate with message queues. A queue query is composed by input variables and output fields.

You can view queues xml structure in this page

Note: All queues are defined in the Queues.xml file at global folder. View project structure for more info.

Xml queue structure

<query id="[Query ID]" queue="[Queue ID]" public="[Public]">
<!-- Output parameters -->
<field id="[Field Id 1]"/>
...
<field id="[Field Id n]"/>
<computed alias="[Alias]" format="[Format]"/>
...
<computed alias="[Alias]" format="[Format]"/>
<!-- Input parameters -->
<variable id="[Variable ID1]" type="[Variable type]" name="[Variable name 1]" />
...
<variable id="[Variable IDn]" type="[Variable type]" name="[Variable name N]" />
</query>

Queue examples

  • Example of queue query:

Code of query xml

<!-- Queues: Fill a criterion with a wrapper values -->
<query id="TstSynQueWrpTxt" queue="SynQueWrpTxt">
<!-- Input parameters -->
<variable id="CrtVen" type="INTEGER" value="4"/>
<variable id="CrtPue" type="INTEGER" value="2"/>
<!-- Output parameters -->
<field id="OutFld1" alias="value" />
<field id="OutFld2" alias="label" />
</query>

Code of queues xml

<!-- Queue retreive sync test with wrappers -->
<queue id="SynQueWrpTxt">
<request-message destination="AweReq" type="TEXT" selector="wrapper">
<message-wrapper type="XML" classname="com.almis.awe.core.wrappers.test.Casa"/>
</request-message>
<response-message destination="AweRes" type="TEXT">
<message-wrapper type="XML" classname="com.almis.awe.core.wrappers.test.Casa"/>
</response-message>
</queue>