QBFunctions

Overview

The QBFunctions class provides a comprehensive set of SQL functions designed to enhance query building in QBSelect. It enables the creation of mathematical, string, and date-based expressions, along with conditional and custom logic.

This class allows users to perform operations like calculating absolute values, rounding, and extracting substrings. It also includes advanced capabilities such as casting data types, concatenating strings, trimming whitespace, and formatting dates. With access to aggregation methods and support for custom function definitions, the class is versatile in handling diverse SQL requirements.

For additional guidance on query construction and execution, refer to the QBSelect section of the documentation.

Properties Summarized

Type	Name	Summary
QBSelect	parent	Get query builder parent table clause, this may be a query or a join clause.
QBSelect	root	Get query builder parent.

Methods Summarized

Type	Name	Summary
QBColumn	abs(value)	Create abs(column) expression
QBColumn	bit_length(value)	Create bit_length(column) expression
QBColumn	cast(value, type)	Cast using type name.
QBColumn	ceil(arg)	Create ceil(column) expression
QBColumn	coalesce(args)
QBColumn	concat(arg1, arg2)	Create concat(args, arg2) expression
QBColumn	custom(name, args)
QBColumn	day(arg)	Create day(date) expression
QBColumn	divide(arg1, arg2)	Create divide(args, arg2) expression
QBColumn	floor(arg)	Create floor(column) expression
QBColumn	hour(arg)	Create hour(date) expression
QBColumn	len(value)	Create length(column) expression
QBColumn	locate(string1, string2)	Create locate(str1, str2) expression
QBColumn	locate(string1, string2, start)	Create locate(str1, str2, start) expression
QBColumn	lower(value)	Create lower(column) expression
QBColumn	minus(arg1, arg2)	Create minus(args, arg2) expression
QBColumn	minute(arg)	Create minute(date) expression
QBColumn	mod(dividend, divisor)	Create mod(dividend, divisor) expression
QBColumn	month(arg)	Create month(date) expression
QBColumn	multiply(arg1, arg2)	Create multiply(args, arg2) expression
QBColumn	nullif(arg1)	Create nullif(arg1, arg2) expression
QBColumn	plus(arg1, arg2)	Create plus(args, arg2) expression
QBColumn	round(arg)	Create round(column) expression
QBColumn	round(arg, decimals)	Create round(column) expression
QBColumn	second(arg)	Create second(date) expression
QBColumn	sqrt(value)	Create sqrt(column) expression
QBColumn	substring(arg, pos)	Create substring(column, pos) expression
QBColumn	substring(arg, pos, len)	Create substring(column, pos, len) expression
QBColumn	trim(value)	Create trim(column) expression
QBColumn	trim(leading_trailing_both, characters, fromKeyword, value)	Create trim([leading | trailing | both] [characters] from column)
QBColumn	upper(value)	Create upper(column) expression
QBColumn	year(arg)	Create year(date) expression

Properties Detailed

parent

Get query builder parent table clause, this may be a query or a join clause.

Type QBSelect

Sample

var query = datasources.db.example_data.person.createSelect();
	query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
	foundset.loadRecords(query)

root

Get query builder parent.

Type QBSelect

Sample

var subquery = datasources.db.example_data.order_details.createSelect();

	var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query
		.or
			.add(query.columns.order_id.not.isin([1, 2, 3]))

			.add(query.exists(
					subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
			))
		)

	foundset.loadRecords(query)

Methods Detailed

abs(value)

Create abs(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the absolute value of the input.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynum.abs.eq(query.functions.abs(myval)))
foundset.loadRecords(query);

bit_length(value)

Create bit_length(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the bit length of the value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.bit_length.eq(query.functions.bit_length('Sample')))
foundset.loadRecords(query);

cast(value, type)

Cast using type name.

Parameters

• Object value object to cast

• String type type see QUERY_COLUMN_TYPES

Returns: QBColumn A query builder function representing the value cast to the specified type.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.functions.cast("22",QUERY_COLUMN_TYPES.TYPE_INTEGER)).add(query.columns.amt_discount.cast(QUERY_COLUMN_TYPES.TYPE_STRING));
application.output(databaseManager.getDataSetByQuery(query,1).getAsHTML())

ceil(arg)

Create ceil(column) expression

Parameters

• Object arg number object

Returns: QBColumn A query builder function representing the smallest integer greater than or equal to the input.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.ceil.eq(query.functions.ceil(myvar))
foundset.loadRecords(query);

coalesce(args)

Parameters

• Array args arguments to coalesce

Returns: QBColumn A query builder function that returns the first non-null argument.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mycol.coalesce('defval').eq(query.functions.coalesce(myvar, 'defval'))
foundset.loadRecords(query);

concat(arg1, arg2)

Create concat(args, arg2) expression

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder function representing the concatenation of two arguments.

Sample

var query = datasources.db.udm.contacts.createSelect();
	query.result.add(query.columns.name_first.concat(' ').concat(query.columns.name_last))
	var ds = databaseManager.getDataSetByQuery(query, -1)

custom(name, args)

Parameters

• String name custom function name

• Array args function arguments

Returns: QBColumn A query builder function representing a custom function with the given name and arguments.

Sample

// select myadd(freight, 500) from orders
	var query = datasources.db.example_data.orders.createSelect();
	query.result.add(query.functions.custom('myadd', query.columns.freight, 500));
	var dataset = databaseManager.getDataSetByQuery(query, 100);

day(arg)

Create day(date) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder function representing the day component of a date/time value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.day.eq(query.functions.day(mydatevar))
foundset.loadRecords(query);

divide(arg1, arg2)

Create divide(args, arg2) expression

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder function representing the division of two arguments.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.divide(2).eq(query.functions.divide(myvar, 2))
foundset.loadRecords(query);

floor(arg)

Create floor(column) expression

Parameters

• Object arg number object

Returns: QBColumn A query builder function representing the largest integer less than or equal to the input.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.floor.eq(query.functions.floor(myvar))
foundset.loadRecords(query);

hour(arg)

Create hour(date) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder function representing the hour component of a date/time value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.hour.eq(query.functions.hour(mydatevar))
foundset.loadRecords(query);

len(value)

Create length(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the length of the value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.len.eq(query.functions.len('Sample')))
foundset.loadRecords(query);

locate(string1, string2)

Create locate(str1, str2) expression

Parameters

• Object string1 string to locate

• Object string2 string to search in

Returns: QBColumn A query builder function representing the position of the first occurrence of one string in another.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.locate('amp').eq(query.functions.locate('amp', 'Sample')))
foundset.loadRecords(query);

locate(string1, string2, start)

Create locate(str1, str2, start) expression

Parameters

• Object string1 string to locate

• Object string2 string to search in

• Number start start pos

Returns: QBColumn A query builder function representing the position of the first occurrence of one string in another, starting from a given position.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.locate('amp', 1).eq(query.functions.locate('amp', 'Sample', 1)))
foundset.loadRecords(query);

lower(value)

Create lower(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the value converted to lowercase.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.lower.eq(query.functions.lower('Sample')))
foundset.loadRecords(query);

minus(arg1, arg2)

Create minus(args, arg2) expression

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder function representing the difference between two arguments.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.minus(2).eq(query.functions.minus(myvar, 2))
foundset.loadRecords(query);

minute(arg)

Create minute(date) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder function representing the minute component of a date/time value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.minute.eq(query.functions.minute(mydatevar))
foundset.loadRecords(query);

mod(dividend, divisor)

Create mod(dividend, divisor) expression

Parameters

• Object dividend ;

• Object divisor ;

Returns: QBColumn A query builder function representing the remainder of the division of two numbers.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.mod(2).eq(query.functions.mod(myvar, 2))
foundset.loadRecords(query);

month(arg)

Create month(date) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder function representing the month component of a date/time value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.month.eq(query.functions.month(mydatevar))
foundset.loadRecords(query);

multiply(arg1, arg2)

Create multiply(args, arg2) expression

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder function representing the product of two arguments.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.multiply(2).eq(query.functions.multiply(myvar, 2))
foundset.loadRecords(query);

nullif(arg1)

Create nullif(arg1, arg2) expression

Parameters

• Object arg1 ;

Returns: QBColumn A query builder function that returns null if the two arguments are equal.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.nullif('none').eq(query.functions.nullif('Sample', 'none')))
foundset.loadRecords(query);

plus(arg1, arg2)

Create plus(args, arg2) expression

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder function representing the sum of two arguments.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.plus(2).eq(query.functions.plus(myvar, 2))
foundset.loadRecords(query);

round(arg)

Create round(column) expression

Parameters

• Object arg number object

Returns: QBColumn A query builder function representing the input rounded to the nearest integer.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.round.eq(query.functions.round(myvar))
foundset.loadRecords(query);

round(arg, decimals)

Create round(column) expression

Parameters

• Object arg number object

• Number decimals The number of decimal places to round number to, default 0

Returns: QBColumn A query builder function representing the input rounded to the specified number of decimal places.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.round.eq(query.functions.round(myvar, 1))
foundset.loadRecords(query);

second(arg)

Create second(date) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder function representing the second component of a date/time value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.second.eq(query.functions.second(mydatevar))
foundset.loadRecords(query);

sqrt(value)

Create sqrt(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the square root of the input.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynum.sqrt.eq(query.functions.sqrt(myval)))
foundset.loadRecords(query);

substring(arg, pos)

Create substring(column, pos) expression

Parameters

• Object arg column name

• Number pos position

Returns: QBColumn A query builder function representing a substring starting at the specified position.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.substring(3).eq(query.functions.substring('Sample', 3)))
foundset.loadRecords(query);

substring(arg, pos, len)

Create substring(column, pos, len) expression

Parameters

• Object arg column name

• Number pos position

• Number len length

Returns: QBColumn A query builder function representing a substring of specified length starting at the specified position.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.substring(3, 2).eq(query.functions.substring('Sample', 3, 2)))
foundset.loadRecords(query);

trim(value)

Create trim(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the value with leading and trailing spaces removed.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.trim.eq(query.functions.trim('Sample')))
foundset.loadRecords(query);

trim(leading_trailing_both, characters, fromKeyword, value)

Create trim([leading | trailing | both] [characters] from column)

Parameters

• String leading_trailing_both 'leading', 'trailing' or 'both'

• String characters characters to remove

• String fromKeyword 'from'

• Object value value to trim

Returns: QBColumn A query builder function representing the value with specified characters trimmed from a specified position.

Sample

var query = datasources.db.example_data.orders.createSelect();
// show shipname but remove trailing space
query.result.add(query.functions.trim('trailing', ' ', 'from', query.columns.shipname));
foundset.loadRecords(query);

upper(value)

Create upper(column) expression

Parameters

• Object value ;

Returns: QBColumn A query builder function representing the value converted to uppercase.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('Sample')))
foundset.loadRecords(query);

year(arg)

Create year(date) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder function representing the year component of a date/time value.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.year.eq(query.functions.year(mydatevar))
foundset.loadRecords(query);

---