If required, inserts the 'AS' keyword for assigning a table or field alias.

AS

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')) 

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') 

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)

${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(YYYY, 2, ls.date_start )

quarter: Add quarters  

 ADD_MONTHS(ls.date_start, 6)  

DATEADD(Q, 2, ls.date_start)

month: Add months    

 ADD_MONTHS(ls.date_start, 2) 

DATEADD(MM, 2, ls.date_start)

week: Add weeks    

(ls.date_start + INTERVAL '14' DAY)  

DATEADD(WW, 2, ls.date_start)

day: Add days    

(ls.date_start + INTERVAL '2' DAY)  

DATEADD(DD, 2, ls.date_start)

hour: Add hours  

(ls.date_start + INTERVAL '2' HOUR)  

 DATEADD(HH, 2, ls.date_start)

minute: Add minutes  

 (ls.date_start + INTERVAL '2' MINUTE)  

 DATEADD(MI, 2, ls.date_start)

second: Add seconds    

(ls.date_start + INTERVAL '2' SECOND)  

 DATEADD(SS, 2, ls.date_start)

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 )

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  )

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 )

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 )

${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(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(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))

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))

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))

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))

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))

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))

${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:

${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)) 

${sql.yearOf(column)}

Formats SQL expression that returns the year of a value contained in specified column

TO_CHAR(column, ''YYYY'')  

 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'')    

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'')  

 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  

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'')    

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    

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  

 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    

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

${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

${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)

${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)