Binding Sources: SQL (with database examples)

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
${sql.convertToInt(expression)}	Expression parameter is a field name or SQL expression.	column name: `TRUNC(ls.area_negotiated, 0)` literal: `TRUNC(TO_NUMBER('123345.45'), 0)`	column name: `CONVERT(INT, ls.area_negotiated)` literal: `CONVERT(INT, CONVERT(NUMERIC(38,10), '123345.45'))`	column name: `CONVERT(INT, ls.area_negotiated)` literal: `CONVERT(INT, CONVERT(NUMERIC(38,10), '123345.45'))`
${sql.convertToString(expression)}	expression parameter is a field name or SQL expression.	column name: `TO_CHAR(ls.area_negotiated)` literal: `TO_CHAR('123345.45')`	column name: `CONVERT(CHAR, ls.area_negotiated)` literal: `CONVERT(CHAR, '123345.45')`	column name: `CONVERT(CHAR, ls.area_negotiated)` literal: `CONVERT(CHAR, '123345.45')`
${sql.inClause('field_name',parameter')}	`parameter` is the name of a parameter. `field_name` is the name of a field available where it appears in the query and may include a table_name. This expression will be replaced by an expression to match any value in the parameter which may be an array or null or an array containing null.	`(field_name IS NULL OR field_name IN (?,?,?))` Details: `field_name IS NULL OR` is only present if there is a null value in the array. The list `(?,?,?)` will contain one entry for each unique value in the array except null, unless there are no such values in which case `OR field_name IN (?,?,?)` will not be included. If the parameter is not an array it will use `=` instead of `IN`, unless that one value is null in which case it will use `IS NULL`. If the parameter is an array with no values it’ll result in `1=1`. An empty string will be treated as null.
${sql.datetime(isoDateLiteral,isoTimeLiteral)}	Takes only literals as parameters. literal: `isoDateTimeLiteral = isoDateLiteral + " " + isoTimeLiteral` (concatenate in java)	`TO_DATE('2014-07-29 13:26:45', 'YYYY-MM-DD HH24:MI:SS')`	`CONVERT(DATETIME, '2014-07-29 13:26:45', 120)`	`CONVERT(DATETIME, '2014-07-29 13:26:45', 120)`
${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`.
	year: Add years	`(ls.date_start + INTERVAL '2' YEAR)`	`DATEADD(YY, 2, ls.date_start)`	`DATEADD(YYYY, 2, ls.date_start`)
	quarter: Add quarters	`ADD_MONTHS(ls.date_start, 6)`	`DATEADD(QQ, 2, ls.date_start)`	`DATEADD(Q, 2, ls.date_start)`
	month: Add months	`ADD_MONTHS(ls.date_start, 2)`	`DATEADD(MM, 2, ls.date_start)`	`DATEADD(MM, 2, ls.date_start)`
	week: Add weeks	`(ls.date_start + INTERVAL '14' DAY`	`DATEADD(WK, 2, ls.date_start)`	`DATEADD(WW, 2, ls.date_start)`
	day: Add days	`(ls.date_start + INTERVAL '2' DAY)`	`DATEADD(DD, 2, ls.date_start)`	`DATEADD(DD, 2, ls.date_start)`
	hour: Add hours	`(ls.date_start + INTERVAL '2' HOUR)`	`DATEADD(HH, 2, ls.date_start)`	`DATEADD(HH, 2, ls.date_start)`
	minute: Add minutes	`(ls.date_start + INTERVAL '2' MINUTE)`	`DATEADD(MI, 2, ls.date_start)`	`DATEADD(MI, 2, ls.date_start)`
	second: Add seconds	`(ls.date_start + INTERVAL '2' SECOND)`	`DATEADD(SS, 2, ls.date_start)`	`DATEADD(SS, 2, ls.date_start)`
${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`.
	year: Difference in years between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) / 365 , 0)`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 365 )`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 365 )`
	quarter: Difference in quarters between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) / 90 , 0)`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 90 )`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 90` )
	month: Difference in months between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) / 30 , 0)`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 30` )	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 30 )`
	week: Difference in weeks between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) / 7 , 0)`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 7 )`	`CONVERT(INT, DATEDIFF(day, ls.date_start, ls.date_end) / 7 )`
	day: Difference in days between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) / 1 , 0)`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 86400 )`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 86400 )`
	hour: Difference in hours between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) * 24 , 0)`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 3600 )`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 3600 )`
	minute: Difference in minutes between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) * 1440 , 0)`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 60 )`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 60 )`
	second: Difference in seconds between two dates	`TRUNC(TO_NUMBER(ls.date_end - ls.date_start) * 86400 , 0)`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 1 )`	`CONVERT(INT, DATEDIFF(second, ls.date_start, ls.date_end) / 1 )`

${sql.dateDiffCalendar(datepart, date1,date2)}	Equivalent to DATEDIFF function in MSSQL. Difference in calendar periods 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. Values for datepart:
	year: Difference in calendar years between two dates		`CONVERT(INT, DATEDIFF(YY, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(YYYY, ls.date_start, ls.date_end))`
	quarter: Difference in calendar quarters between two dates	`TRUNC(ROUND(MONTHS_BETWEEN(TRUNC(ls.date_end, 'YYYY'), TRUNC(ls.date_start, 'YYYY')) / 12 ), 0)`	`CONVERT(INT, DATEDIFF(QQ, ls.date_start, ls.date_end)`)	`CONVERT(INT, DATEDIFF(Q, ls.date_start, ls.date_end))`
	month: Difference in calendar months between two dates	`TRUNC(ROUND(MONTHS_BETWEEN(TRUNC(ls.date_end, 'MM'), TRUNC(ls.date_start, 'MM')) / 1 ), 0)`	`CONVERT(INT, DATEDIFF(MM, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(MM, ls.date_start, ls.date_end))`
	week: Difference in calendar weeks between two dates	`TRUNC(ROUND(TO_NUMBER(TRUNC(ls.date_end, 'IW') - TRUNC(ls.date_start, 'IW')) / 7 ), 0`)	`CONVERT(INT, DATEDIFF(WW, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(WW, ls.date_start, ls.date_end))`
	day: Difference in calendar days between two dates	`TRUNC(ROUND(TO_NUMBER(TRUNC(ls.date_end, 'DD') - TRUNC(ls.date_start, 'DD')) / 1 ), 0)`	`CONVERT(INT, DATEDIFF(DD, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(DD, ls.date_start, ls.date_end))`
	hour: Difference in calendar hours between two dates	`TRUNC(ROUND(TO_NUMBER(TRUNC(ls.date_end, 'HH24') - TRUNC(ls.date_start, 'HH24')) * 24 ), 0)`	`CONVERT(INT, DATEDIFF(HH, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(HH, ls.date_start, ls.date_end))`
	minute:Difference in calendar minutes between two dates	`TRUNC(ROUND(TO_NUMBER(TRUNC(ls.date_end, 'MI') - TRUNC(ls.date_start, 'MI')) * 1440 ), 0)`	`CONVERT(INT, DATEDIFF(MI, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(MI, ls.date_start, ls.date_end))`
	second: Difference in calendar minutes between two dates	`TRUNC(ROUND(TO_NUMBER(TRUNC(ls.date_end, 'SS') - TRUNC(ls.date_start, 'SS')) * 86400 ), 0)`	`CONVERT(INT, DATEDIFF(SS, ls.date_start, ls.date_end))`	`CONVERT(INT, DATEDIFF(SS, ls.date_start, ls.date_end))`
${sql.datePart(datepart, dateExpression)}	Equivalent to DATEPART functions in MSSQL and Oracle. Returns an integer. dateExpression is a field name or SQL expression that evaluates to a datetime result. Values for datepart:
	year: Returns the month of the year (1-12)	`TO_NUMBER(TO_CHAR(ls.date_start, 'YYYY'))`	`DATEPART(YYYY, ls.date_start)`	`DATEPART(YYYY, ls.date_start)`
	quarter: Returns the quarter of the year (1-4)	`TO_NUMBER(TO_CHAR(ls.date_start, 'Q'))`	`DATEPART(QQ, ls.date_start)`	`DATEPART(Q, ls.date_start)`
	month: Returns the month of the year (1-12)	`TO_NUMBER(TO_CHAR(ls.date_start, 'MM'))`	`DATEPART(MM, ls.date_start)`	`DATEPART(MM, ls.date_start)`
	week: Returns the week of the year (1-53)	`CEIL((7 + (TRUNC(ls.date_start,'D') - TRUNC(ls.date_start,'Y')))/7)`	`DATEPART(WK, ls.date_start)`	`DATEPART(WW, ls.date_start)`
	isoweek: Returns the ISO 8601 week of the year (1-53)	`TO_NUMBER(TO_CHAR(ls.date_start, 'IW'))`	`DATEPART(CWK, ls.date_start)`	`DATEPART(ISOWW, ls.date_start)`
	day: Returns the day of the month (1-31)	`TO_NUMBER(TO_CHAR(ls.date_start, 'DD'))`	`DATEPART(DD, ls.date_start)`	`DATEPART(DD, ls.date_start)`
	dayofyear: Returns the day of the year (1-366)	`TO_NUMBER(TO_CHAR(ls.date_start, 'DDD'))`	`DATEPART(DY, ls.date_start)`	`DATEPART(DY, ls.date_start)`
	weekday: Returns the day of the week (1-7)	`TO_NUMBER(TO_CHAR(ls.date_start, 'D'))`	`DATEPART(DW, ls.date_start)`	`DATEPART(DW, ls.date_start)`
	hour: Returns the hour (0-23) minute:	`TO_NUMBER(TO_CHAR(ls.date_start, 'HH24'))`	`DATEPART(HH, ls.date_start)`	`DATEPART(HH, ls.date_start)`
	minute: Returns the minute of the hour (0-59)	`TO_NUMBER(TO_CHAR(ls.date_start, 'MI'))`	`DATEPART(MI, ls.date_start)`	`DATEPART(MI, ls.date_start)`
	second: Returns the second of the minute (0-59)	`TO_NUMBER(TO_CHAR(ls.date_start, 'SS'))`	`DATEPART(SS, ls.date_start)`	`DATEPART(SS, ls.date_start)`
${sql.trim(expression)}	TRIM function for each database engine. expression parameter is a field name or SQL expression.	`RTRIM(LTRIM(ls.comments))`	`RTRIM(LTRIM(ls.comments))`	`RTRIM(LTRIM(ls.comments))`
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: 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')} 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: 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')} 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)