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	cardinality(arg)	Create cardinality(column) expression
QBColumn	cast(value, type)	Create cast(column, type) expression
QBColumn	ceil(arg)	Create ceil(column) expression
QBColumn	coalesce(args)	Create coalesce(arg) expression
QBColumn	concat(arg1, arg2)	Concatenate with value
QBColumn	custom(name, args)	Call a custom defined function.
QBColumn	day(arg)	Extract day from date
QBColumn	divide(arg1, arg2)	Divide by value
QBColumn	floor(arg)	Create floor(column) expression
QBColumn	hour(arg)	Extract hour from date
QBColumn	len(value)	Create length(column) expression
QBColumn	locate(string1, string2)	Create locate(arg) expression
QBColumn	locate(string1, string2, start)	Create locate(arg, start) expression
QBColumn	lower(value)	Create lower(column) expression
QBColumn	minus(arg1, arg2)	Subtract value
QBColumn	minute(arg)	Extract minute from date
QBColumn	mod(dividend, divisor)	Create mod(arg) expression
QBColumn	month(arg)	Extract month from date
QBColumn	multiply(arg1, arg2)	Multiply with value
QBColumn	nullif(arg1, arg2)	Create nullif(arg) expression
QBColumn	plus(arg1, arg2)	Add up value
QBColumn	round(arg)	Create round(column) expression
QBColumn	round(arg, decimals)	Create round(column) expression
QBColumn	second(arg)	Extract second from date
QBColumn	sqrt(value)	Create sqrt(column) expression
QBColumn	substring(arg, pos)	Create substring(pos) expression
QBColumn	substring(arg, pos, len)	Create substring(pos, len) expression
QBColumn	trim(value)	Create trim(column) expression
QBColumn	trim(leading_trailing_both, characters, fromKeyword, value)	Create trim(column) expression
QBColumn	upper(value)	Create upper(column) expression
QBColumn	year(arg)	Extract year from date

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

cardinality(arg)

Create cardinality(column) expression

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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);

cast(value, type)

Create cast(column, type) expression

Parameters

• Object value object to cast

• String type type see QUERY_COLUMN_TYPES

Returns: QBColumn A query builder column 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 column 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)

Create coalesce(arg) expression

Parameters

• Array args arguments to coalesce

Returns: QBColumn A query builder column 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)

Concatenate with value

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder column 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)

Call a custom defined function.

Parameters

• String name custom function name

• Array args function arguments

Returns: QBColumn A query builder column 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)

Extract day from date

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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)

Divide by value

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder column 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 column 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)

Extract hour from date

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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 column 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(arg) expression

Parameters

• Object string1 string to locate

• Object string2 string to search in

Returns: QBColumn A query builder column 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(arg, start) expression

Parameters

• Object string1 string to locate

• Object string2 string to search in

• Number start start pos

Returns: QBColumn A query builder column 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 column 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)

Subtract value

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder column 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)

Extract minute from date

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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(arg) expression

Parameters

• Object dividend ;

• Object divisor ;

Returns: QBColumn A query builder column 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)

Extract month from date

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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)

Multiply with value

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder column 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, arg2)

Create nullif(arg) expression

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder column 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)

Add up value

Parameters

• Object arg1 ;

• Object arg2 ;

Returns: QBColumn A query builder column 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 column 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 column 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)

Extract second from date

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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 column 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(pos) expression

Parameters

• Object arg column name

• Number pos position

Returns: QBColumn A query builder column 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(pos, len) expression

Parameters

• Object arg column name

• Number pos position

• Number len length

Returns: QBColumn A query builder column 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 column 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(column) expression

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

Extract year from date

Parameters

• Object arg date object

Returns: QBColumn A query builder column 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);

---