QBFunctions

Property Summary

TypeNameSummary

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

Get query builder parent..

Methods Summary

TypeNameSummary

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

Create upper(column) expression.

Create year(date) expression.

Properties Details

parent

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

Returns 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.

Returns 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 Details

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