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/Comments |
|---|---|
| ${sql.as} | If required, inserts the 'AS' keyword for assigning a table or field alias |
| ${sql.convertToInt(expression)} | Expression parameter is a field name or SQL expression. |
| ${sql.convertToString(expression)} | expression parameter is a field name or SQL expression. |
| ${sql.datetime(isoDateLiteral,isoTimeLiteral)} | Takes only literals as parameters. |
| ${sql.dateAdd(datepart, number, date)} |
Equivalent to DATEADD function in MSSQL. Values for datepart:
date parameter is an expression, field name, or literal value. Literal values must be preceded with # symbol, '#2014-04-08'. |
| ${sql.dateDiffInterval(datepart, date1, date2)} |
Difference in periods between two dates. Values for datepart:
date1 and date2 parameters are expressions, field names, and/or literal values. Literal values must be preceded with # symbol, '#2014-04-08'. |
| ${sql.dateDiffCalendar(datepart, date1,date2)} |
Equivalent to DATEDIFF function in MSSQL. Difference in calendar periods between two dates. Values for datepart: year: Difference in calendar years between two dates
date1 and date2 parameters are expressions, field names, and/or literal values. Literal values must be preceded with # symbol, '#2014-04-08'. The return value is positive if the “date1” field value is before the “date2” field value and negative if it is after the “date2” field value. |
| ${sql.datePart(datepart, dateExpression)} |
Equivalent to DATEPART functions in MSSQL and Oracle. Returns an integer. Values for datepart:
dateExpression is a field name or SQL expression that evaluates to a datetime result. |
| ${sql.trim(expression)} | TRIM function for each database engine. expression parameter is a field name or SQL expression. |
| 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)} |
The ${sql.yearWeekOf} expression uses default calculation for the first week of the year on all databases. All Archibus applications use this calculation by default. Example: ${sql.yearWeekOf('wr.date_requested')} When desired, add-in managers can specify the ISO 8601 calculation using the optional weekType parameter:
Solution template view: schema/ab-products/solutions/programming/binding-expr/ab-ex-binding-expr-year-week-of.axvw |
| ${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. |
|
${sql.hoursBetween(fromDateColumn, fromTimeColumn, toDateColumn, toTimeColumn)} |
Returns the number of hours between two date-time fields. The number is positive if the “from” field date-time value is before the “to” field date-time value, and negative if it is after the “to” field date-time value. |
| ${sql.replaceZero(fieldName)} |
Generates the CASE operator that:
Example: ${sql.replaceZero('bl.area_usable')} The parameter is fieldName or sql expression. |
| ${sql.replaceZero(fieldName, defaultValue)} |
Generates the CASE operator that:
Example: ${sql.replaceZero('bl.area_usable', '1000000')} The parameter is fieldName or sql expression. |
| ${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)} |
|
${sql.vpaRestriction} |
Generates SQL WHERE clause from VPAs for current user/role applicable to the main table in current DataSource. |
|
${sql.getVpaRestrictionForTable('table')} |
Generates SQL WHERE clause from VPAs for current user/role applicable to the specified table in current DataSource. table - name of main or standard table in the current DataSource for which to generate SQL. |
|
${sql.getVpaRestrictionForTableAndRole('table','role')} |
Generates SQL WHERE clause from VPAs applicable to the specified table in current DataSource. The role will be used by "forVpaGroups" VPAs (if current user/role has such restrictions) to generate "EXISTS (SELECT ...)" to restrict table using mapping tables; otherwise it will be ignored. This expression is useful for system WFRs, who must suspend the user's VPA (e.g., a room reservation query that can see all conference rooms in the campus, even if the user does not have VPA access to all rooms). Example: This expression enables you to add roles and VPAs that you can manage centrally, but that application code can use to get access to specific groups of records as part of specific features (e.g., Room reservations). The Admin assigns any needed VPA Group permissions to the role; this expression then creates an SQL statement that references those VPA groups.
|
|
${sql.getVpaGroupsRestrictionForBridgeTable(table, bridgeTable)} |
Generates "EXISTS (SELECT ...)" to restrict table using mapping tables vpa_groupstoroles, vpa_groupstousers, vpa_[bridgeTable] for the current user and role.
|
|
${sql.getVpaGroupsRestrictionForMappingTable(table, vpaMappingTable, fieldName)} |
Generates "EXISTS (SELECT ...)" to restrict table using mapping tables vpa_groupstoroles, vpa_groupstousers, vpaMappingTable for current user and role.
|