QBFunctions

Properties Summarized

Type

Name

Summary

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

Get query builder parent.

Methods Summarized

Type

Name

Summary

Create abs(column) expression

Create bit_length(column) expression

Cast using type name.

Create ceil(column) expression

Create concat(args, arg2) expression

Create day(date) expression

Create divide(args, arg2) expression

Create floor(column) expression

Create hour(date) expression

Create length(column) expression

Create locate(str1, str2) expression

Create locate(str1, str2, start) expression

Create lower(column) expression

Create minus(args, arg2) expression

Create minute(date) expression

Create mod(dividend, divisor) expression

Create month(date) expression

Create multiply(args, arg2) expression

Create nullif(arg1, arg2) expression

Create plus(args, arg2) expression

Create round(column) expression

Create second(date) expression

Create sqrt(column) expression

Create substring(column, pos) expression

Create substring(column, pos, len) expression

Create trim(column) expression

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

Create upper(column) expression

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

PreviousQBFunction NextQBGroupBy

Last updated 1 month ago