SQL statement

SQL statements are at the center of a report definition. They control the retrieval of the desired data. This page offers a short overview over the composition of an SQL statement and what needs to be considered.

SELECT statement

The SELECT statement defines which data, meaning which database table columns, are retrieved from which database. The retrieved table columns will later be the columns of the report results. A statement that retrieves the ID and the product number from the database table “Article” could look like this:

SELECT art.id, art.productno FROM #SCHEMA#.Article art

The following needs to be considered regarding a SELECT statement:

  • The first value in the selection always has to be the product respectively article ID (“id” in the exemplary statement above). This is crucial for the linking of the report result to the corresponding products.
  • There must not be a semicolon at the end of a SQL statement.
  • To ensure that the database tables are accessed correctly, it is advisable to use the placeholder #SCHEMA# (e.g. #SCHEMA#.Article art) before each table listed in the SQL statement. When the report is carried out, the placeholder is replaced with a fixed value which is defined via a corresponding property.
  • The column names of the retrieved database table columns need to be separated by commas.

WHERE clause

Definition

The WHERE clause defines parameters (which means conditions) to narrow down the data that is retrieved via the query. A simple SQL statement with a WHERE clause that retrieves any product number that contains the numerical sequence “10013” from the database table “Article” could look like this:

SELECT art.id, art.productno FROM #SCHEMA#.Article art WHERE productno like ′10013%′

Parameters

When it comes to parameters, the following needs to be considered:

  • According to the JQL specification, parameters in the query must be specified with ?1, ?2, ?3 … ?N
  • Parameters for which allow for multiple selections must be processed in the query in an IN clause. The multiple selection is passed to the query as an ID list (Attribute, AttributeValue, PurchaseCategory).
  • Types are narrowed down via the type ID (e.g. “Type=52”).
  • Attribute values are narrowed down by the attribute (e.g. “AttributeValue=Color”)
  • Optional parameters are requested as a key-value pair in the form “<Parameterindex>#=#<Parametertyp>”. The parameter index corresponds to the index of the parameter within the statement. The parameter type corresponds to one of the data types possible for reports.
  • Default values for optional parameters can be set in the “System parameters” section (report.parameter).
  • Standard parameters for initial presetting of the report parameters are retrieved as a key-value pair in the form “<Parameterindex>#=#<Parametervalue>”. The parameter index corresponds to the index of the parameter within the statement. The parameter value corresponds to the desired default value for the first execution.
  • List parameters can be retrieved via a query that contains the addition “#LIST_PARAM#” (e.g. #LIST_PARAM#nar.node.id in (?1)#LIST_PARAM#)
  • The retrieval of list parameters is limited to lists with 1000 entries. Though, there can only be one query with the addition “#LIST_PARAM#” per SQL statement. In order to retrieve lists of more than 1000 entries, you need to use a query that combines the operator OR as well as IN clauses (e.g. nar.node.id IN (id1..id1000) OR nar.node.id IN (id1001..id2000) OR nar.node.id IN (id2001..id3000))