Child pages
  • QBFunctions
Skip to end of metadata
Go to start of metadata

Refresh page Mar 16, 2024 08:37

Supported Clients
SmartClient WebClient NGClient

Property 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 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) Create year(date) expression
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

Property Details

parent

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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 year(date) expression

Parameters

Array args arguments to coalesce

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

  • No labels