Binding Sources: SQL

The sql object can be used to format field value literals according to the current database format. In the context of an AXVW, it can be used in the AXVW's data source (SQL query or restriction).

For examples with Oracle, Sybase, and SQL Server, see Binding Sources: SQL (with database examples)

Expression Description
${sql.as} If required, inserts the 'AS' keyword for assigning a table or field alias
value1${sql.concat}value2 Formats SQL expression that concatenates two values
${sql.currentDate} Formats current date
${sql.currentTime} Formats current time
${sql.currentTimestamp} Formats current timestamp
${sql.date(value)} Formats date literal
${sql.time(value)} Formats time literal
${sql.isNull(value, replaceWith)} Replaces NULL with the specified replacement value
${sql.timestamp(value)} Formats timestamp literal value in 'date' 'time' format (e.g. '\'YYYY-MM-DD\' \'HH:mm\'')
${sql.timestamp(dateField, timeField)} Formats timestamp SQL expression that concatenates two fields: date and time
${sql.yearOf(column)}

Formats SQL expression that returns the year of a value contained in specified column
${sql.yearQuarterOf(column)} Formats SQL expression that returns the year-quarter of a value contained in specified column
${sql.yearWeekOf(column)} Formats SQL expression that returns the year-week of a value contained in specified column
${sql.yearMonthOf(column)} Formats SQL expression that returns the year-month of a value contained in specified column
${sql.yearMonthDayOf(column)} Formats SQL expression that returns the year-month-day of a value contained in specified column
${sql.daysBeforeCurrentDate(field)} Returns the number of days between today’s date and specified date field value. The number is positive if the field value is before today’s date and negative if it is after today’s date
${sql.daysBetween(fromField, toField)} Returns the number of days between two date fields. The number is positive if the “from” field value is before the “to” field value, and negative if it is after the “to” field value.
${replaceZero(fieldName)}

Generates the CASE operator that:

  • Returns specified field value if it is not equal 0.
  • Returns a very large value (9999999999) if the field value is equal to 0.

Example: ${sql.replaceZero('bl.area_usable')}

${replaceZero(fieldName, defaultValue)}

Generates the CASE operator that:

  • Returns specified field value if it is not equal 0.
  • Returns specified default value if the field value is equal to 0.

Example: ${sql.replaceZero('bl.area_usable', '1000000')}

${sql.vpaRestriction} Returns the VPA (Virtual Private ARCHIBUS) SQL restriction attached to the current user session. If the current user/role has no VPA, the expression will be resolved as (1=1).
${sql.getVpaRestrictionForTable(tableName)}

Returns the VPA (Virtual Private ARCHIBUS) SQL restriction attached to the current user session, for specified table. If the current user/role has no VPA for that table, the expression will be resolved as (1=1).

Example: ${sql.getVpaRestrictionForTable('property')}
${sql.literal(textValue)}

Literalizes a text value so that it can be used in an SQL query even when the text contains single quotes. The literalized value contains two single quotes instead of every single quote in the original text.

Example: ${sql.literal(user.employee.id)}