Allowed query types

A native SQL query must follow the rules listed below when used in Alfabet configurations:

• Native SQL queries must be defined as ANSI-SQL-92 and no specific driver commands should be used.
• XML extensions provided by the database server are not supported.
• In general, only SQL queries with a SELECT statement are allowed in the context of Alfabet configurations. SQL statements that alter the data in the Alfabet database, for example DELETE , INSERT or UPDATE , are not allowed in the context of Alfabet configurations. Exceptions to this rule are explicitly stated in the documentation of the configuration.
• SQL queries must start with a SELECT or a WITH statement to be considered in Alfabet configurations. It is possible to add a comment as first line of a query. All lines that are empty or that are starting with /* or with - are ignored by the check for correct definition of the native SQL query.
• If you use WITH statements to built a temporary table within native SQL statements, you should make sure that the table name and all column names of the temporary table are written in lowercase letters only. Uppercase letters can lead to errors with some database server types or configurations.
• Only one SELECT statement can be defined in a native SQL query in the context of Alfabet configurations.
• Native SQL queries might not be parsed successfully if a HAVING statement is used. If the HAVING statement cannot be parsed correctly, it is recommended that you use sub-queries instead of a HAVING statement to restrict the results.

  The following query is not correctly parsed:

  • SELECT ou.REFSTR, ou.NAME, ou.STEREOTYPE,
  • (SELECT '(' + CAST(COUNT(*) AS VARCHAR(5)) + ')' FROM ICTOBJECT icto WHERE icto.OWNER = ou.REFSTR HAVING COUNT(*) > 0)
  • FROM ORGAUNIT ou
  • ORDER BY ou.NAME

  The following query is correctly parsed. It returns the same dataset without using the HAVING statement in the query:

  • SELECT ou.REFSTR, ou.NAME, ou.STEREOTYPE,
  • (SELECT '(' + CAST(value AS VARCHAR(5)) + ')'
  • FROM (SELECT COUNT(*) AS value FROM ICTOBJECT icto WHERE icto.OWNER = ou.REFSTR) x
  • WHERE x.value > 0)
  • FROM ORGAUNIT ou
  • ORDER BY ou.NAME
• FOR XML PATH cannot be used in SQL queries in the context of Alfabet configurations.
• To use the function ROW_NUMBER() in SQL queries defined in the context of Alfabet configurations you must use CAST to convert the values to the type INTEGER.

  For example instead of

  • ROW_NUMBER() OVER (ORDER BY value DESC)

  you have to use

  • CAST(ROW_NUMBER() OVER (ORDER BY value DESC) AS INTEGER)