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).
| Expression | Description | Oracle | Sybase | SQL Server |
|---|---|---|---|---|
| ${sql.as} | If required, inserts the 'AS' keyword for assigning a table or field alias | AS | AS | |
| value1${sql.concat}value2 | Formats SQL expression that concatenates two values | || | || | + |
| ${sql.currentDate} | Formats current date | |||
| ${sql.currentTime} | Formats current time | current time in 'HH:mm:ss' format | current time in 'HH:mm:ss' format | current time in 'HH:mm:ss' format |
| ${sql.currentTimestamp} | Formats current timestamp | current timestamp as DATETIME('YYYY-MM-DD'||' '|| 'HH:mm:ss') | ||
| ${sql.date(value)} | Formats date literal | value | ||
| ${sql.time(value)} | Formats time literal | value | ||
| ${sql.isNull(value, replaceWith)} | Replaces NULL with the specified replacement value | NVL(value, replaceWith) | ISNULL((value, replaceWith) | ISNULL((value, replaceWith) |
| ${sql.timestamp(value)} | Formats timestamp literal value in 'date' 'time' format (e.g. '\'YYYY-MM-DD\' \'HH:mm\'') | DATETIME('YYYY-MM-DD'||' '|| 'HH:mm') | ||
| ${sql.timestamp(dateField, timeField)} | Formats timestamp SQL expression that concatenates two fields: date and time | DATETIME(dateField||' '||timeField) | ||
| ${sql.yearOf(column)} |
Formats SQL expression that returns the year of a value contained in specified column |
TO_CHAR(column, ''YYYY'') | TRIM(STR(DATEPART(year, (column))) | LTRIM(RTRIM(STR(DATEPART(year, (column)))) |
| ${sql.yearQuarterOf(column)} | Formats SQL expression that returns the year-quarter of a value contained in specified column | TO_CHAR((column, ''YYYY-Q'') | TRIM(STR(DATEPART(year, (column))) + ''-'' + TRIM(STR(DATEPART(quarter, (column))) | LTRIM(RTRIM(STR(DATEPART(year, (column)))) + ''-'' + LTRIM(RTRIM(STR(DATEPART(quarter, (column)))) |
| ${sql.yearWeekOf(column)} | Formats SQL expression that returns the year-week of a value contained in specified column | TO_CHAR((column, ''YYYY-IW'') | TRIM(STR(DATEPART(year, (column))) + ''-'' + (CASE WHEN DATEPART(week, (column)/10 < 1 THEN ''0'' ELSE '''' END) + TRIM(STR(DATEPART(week, (column))) | LTRIM(RTRIM(STR(DATEPART(year, (column)))) + ''-'' + (CASE WHEN DATEPART(week, (column)/10 < 1 THEN ''0'' ELSE '''' END) + LTRIM(RTRIM(STR(DATEPART(week, (column)))) |
| ${sql.yearMonthOf(column)} | Formats SQL expression that returns the year-month of a value contained in specified column | TO_CHAR((column, ''YYYY-MM'') | TRIM(STR(DATEPART(year, (column))) + ''-'' + (CASE WHEN DATEPART(month, (column)/10 < 1 THEN ''0'' ELSE '''' END) + TRIM(STR(DATEPART(month, (column))) | LTRIM(RTRIM(STR(DATEPART(year, (column)))) + ''-'' + (CASE WHEN DATEPART(month, (column)/10 < 1 THEN ''0'' ELSE '''' END) + LTRIM(RTRIM(STR(DATEPART(month, (column)))) |
| ${sql.yearMonthDayOf(column)} | Formats SQL expression that returns the year-month-day of a value contained in specified column | TO_CHAR((column, ''YYYY-MM-DD'') | TRIM(STR(DATEPART(year, (column))) + ''-'' + (CASE WHEN DATEPART(month, (column)/10 < 1 THEN ''0'' ELSE '''' END) + TRIM(STR(DATEPART(month, (column))) + ''-'' + (CASE WHEN DATEPART(day, (column)/10 < 1 THEN ''0'' ELSE '''' END) + TRIM(STR(DATEPART(day, (column))) | LTRIM(RTRIM(STR(DATEPART(year, (column)))) + ''-'' + (CASE WHEN DATEPART(month, (column)/10 < 1 THEN ''0'' ELSE '''' END) + LTRIM(RTRIM(STR(DATEPART(month, (column)))) + ''-'' + (CASE WHEN DATEPART(day, (column)/10 < 1 THEN ''0'' ELSE '''' END) + LTRIM(RTRIM(STR(DATEPART(day, (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 | 'YYY-MM-DD' - column | CAST('YYY-MM-DD' AS DATE) - column | datediff(DD, column, 'YYY-MM-DD') |
| ${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. | column2 - column1 | column2 - column1 | datediff(DD, column1, column2 |
|
${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. | (TO_DATE(TO_CHAR(toDate,'YYYY-MM-DD ') || TO_CHAR(toTime,'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(TO_CHAR(fromDate,'YYYY-MM-DD ') || TO_CHAR(fromTime,'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')) * 24 | HOURS(STRING(fromDate,' ',fromTime), STRING(toDate,' ',toTime)) |
DATEDIFF( HOUR, CONVERT( DATETIME, CONVERT(CHAR,fromDate,102) + ' ' + CONVERT(CHAR,fromTime,108), 120), CONVERT( DATETIME, CONVERT(CHAR,toDate,102) + ' ' + CONVERT(CHAR,toTime,108), 120)) |
| ${sql.replaceZero(fieldName)} |
Generates the CASE operator that:
Example: ${sql.replaceZero('bl.area_usable')} The parameter is fieldName or sql expression. |
CASE WHEN column=0 THEN defaultValue ELSE column END | CASE column WHEN 0 THEN defaultValue ELSE column END | CASE column WHEN 0 THEN defaultValue ELSE column END |
| ${sql.replaceZero(fieldName, defaultValue)} |
Generates the CASE operator that:
Example: ${sql.replaceZero('bl.area_usable', '1000000')} The parameter is fieldName or sql expression. |
CASE WHEN column=0 THEN 9999999999 ELSE column END | CASE column WHEN 0 THEN 9999999999 ELSE column END | CASE column WHEN 0 THEN 9999999999 ELSE column END |
| ${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). | generated VPA restriction SQL, or (1=1) | generated VPA restriction SQL, or (1=1) | generated VPA restriction SQL, or (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). | generated VPA restriction SQL, or (1=1) | generated VPA restriction SQL, or (1=1) | generated VPA restriction SQL, or (1=1) |
| Copyright © 1984-2014, ARCHIBUS, Inc. All rights reserved. |