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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sample

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

---