Servoy 7.x.x Documentation
Child pages
  • QBFunctions
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »


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.

Method Summary
QBFunction #abs(value)
Create abs(column) expression
QBFunction #bit_length(value)
Create bit_length(column) expression
QBFunction #cast(value, type)
Cast using type name.
QBFunction #ceil(arg)
Create ceil(column) expression
QBFunction #coalesce(args)
Create year(date) expression
QBFunction #concat(arg1, arg2)
Create concat(args, arg2) expression
QBFunction #day(arg)
Create day(date) expression
QBFunction #divide(arg1, arg2)
Create divide(args, arg2) expression
QBFunction #floor(arg)
Create floor(column) expression
QBFunction #hour(arg)
Create hour(date) expression
QBFunction #len(value)
Create length(column) expression
QBFunction #locate(string1, string2)
Create locate(str1, str2) expression
QBFunction #locate(string1, string2, start)
Create locate(str1, str2, start) expression
QBFunction #lower(value)
Create lower(column) expression
QBFunction #minus(arg1, arg2)
Create minus(args, arg2) expression
QBFunction #minute(arg)
Create minute(date) expression
QBFunction #mod(dividend, divisor)
Create mod(dividend, divisor) expression
QBFunction #month(arg)
Create month(date) expression
QBFunction #multiply(arg1, arg2)
Create multiply(args, arg2) expression
QBFunction #nullif(arg1)
Create nullif(arg1, arg2) expression
QBFunction #plus(arg1, arg2)
Create plus(args, arg2) expression
QBFunction #round(arg)
Create round(column) expression
QBFunction #second(arg)
Create second(date) expression
QBFunction #sqrt(value)
Create sqrt(column) expression
QBFunction #substring(arg, pos)
Create substring(column, pos) expression
QBFunction #substring(arg, pos, len)
Create substring(column, pos, len) expression
QBFunction #trim(value)
Create trim(column) expression
QBFunction #upper(value)
Create upper(column) expression
QBFunction #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
QBSelect
Sample
/** @type {QBSelect<db:/example_data/person>} */
	var query = databaseManager.createSelect('db:/example_data/person')
	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
/** @type {QBSelect<db:/example_data/order_details>} */
	var subquery = databaseManager.createSelect('db:/example_data/order_details')
	
 /** @type {QBSelect<db:/example_data/orders>} */
	var query = databaseManager.createSelect('db:/example_data/orders')
	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)

Method Details
abs

QBFunction abs (value)

Create abs(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynum.abs.eq(query.functions.abs(myval))) 
foundset.loadRecords(query);
bit_length

QBFunction bit_length (value)

Create bit_length(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.bit_length.eq(query.functions.bit_length('Sample'))) 
foundset.loadRecords(query);
cast

QBFunction cast (value, type)

Cast using type name.
Parameters
{Object} value – object to cast
{String} type – type see QUERY_COLUMN_TYPES
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
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

QBFunction ceil (arg)

Create ceil(column) expression
Parameters
{Object} arg – number object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.ceil.eq(query.functions.ceil(myvar)) 
foundset.loadRecords(query);
coalesce

QBFunction coalesce (args)

Create year(date) expression
Parameters
{Object...} args – arguments to coalesce
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mycol.coalesce('defval').eq(query.functions.coalesce(myvar, 'defval')) 
foundset.loadRecords(query);
concat

QBFunction concat (arg1, arg2)

Create concat(args, arg2) expression
Parameters
{Object} arg1
{Object} arg2
Returns
QBFunction
Sample
/** @type {QBSelect<db:/udm/contacts>} */
	var query = databaseManager.createSelect('db:/udm/contacts') //$NON-NLS-1$
	query.result.add(query.columns.name_first.concat(' ').concat(query.columns.name_last))
	var ds = databaseManager.getDataSetByQuery(query, -1)
day

QBFunction day (arg)

Create day(date) expression
Parameters
{Object} arg – date object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mydatecol.day.eq(query.functions.day(mydatevar)) 
foundset.loadRecords(query);
divide

QBFunction divide (arg1, arg2)

Create divide(args, arg2) expression
Parameters
{Object} arg1
{Object} arg2
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.divide(2).eq(query.functions.divide(myvar, 2)) 
foundset.loadRecords(query);
floor

QBFunction floor (arg)

Create floor(column) expression
Parameters
{Object} arg – number object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.floor.eq(query.functions.floor(myvar)) 
foundset.loadRecords(query);
hour

QBFunction hour (arg)

Create hour(date) expression
Parameters
{Object} arg – date object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mydatecol.hour.eq(query.functions.hour(mydatevar)) 
foundset.loadRecords(query);
len

QBFunction len (value)

Create length(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.len.eq(query.functions.len('Sample'))) 
foundset.loadRecords(query);
locate

QBFunction locate (string1, string2)

Create locate(str1, str2) expression
Parameters
{Object} string1 – string to locate
{Object} string2 – string to search in
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.locate('amp').eq(query.functions.locate('Sample', 'amp'))) 
foundset.loadRecords(query);
locate

QBFunction 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
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.locate('amp', 1).eq(query.functions.locate('Sample', 'amp', 1))) 
foundset.loadRecords(query);
lower

QBFunction lower (value)

Create lower(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.lower.eq(query.functions.lower('Sample'))) 
foundset.loadRecords(query);
minus

QBFunction minus (arg1, arg2)

Create minus(args, arg2) expression
Parameters
{Object} arg1
{Object} arg2
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.minus(2).eq(query.functions.minus(myvar, 2)) 
foundset.loadRecords(query);
minute

QBFunction minute (arg)

Create minute(date) expression
Parameters
{Object} arg – date object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mydatecol.minute.eq(query.functions.minute(mydatevar)) 
foundset.loadRecords(query);
mod

QBFunction mod (dividend, divisor)

Create mod(dividend, divisor) expression
Parameters
{Object} dividend
{Object} divisor
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.mod(2).eq(query.functions.mod(myvar, 2)) 
foundset.loadRecords(query);
month

QBFunction month (arg)

Create month(date) expression
Parameters
{Object} arg – date object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mydatecol.month.eq(query.functions.month(mydatevar)) 
foundset.loadRecords(query);
multiply

QBFunction multiply (arg1, arg2)

Create multiply(args, arg2) expression
Parameters
{Object} arg1
{Object} arg2
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.multiply(2).eq(query.functions.multiply(myvar, 2)) 
foundset.loadRecords(query);
nullif

QBFunction nullif (arg1)

Create nullif(arg1, arg2) expression
Parameters
{Object} arg1
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.nullif('none').eq(query.functions.nullif('Sample', 'none'))) 
foundset.loadRecords(query);
plus

QBFunction plus (arg1, arg2)

Create plus(args, arg2) expression
Parameters
{Object} arg1
{Object} arg2
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.plus(2).eq(query.functions.plus(myvar, 2)) 
foundset.loadRecords(query);
round

QBFunction round (arg)

Create round(column) expression
Parameters
{Object} arg – number object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynumcol.round.eq(query.functions.round(myvar)) 
foundset.loadRecords(query);
second

QBFunction second (arg)

Create second(date) expression
Parameters
{Object} arg – date object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mydatecol.second.eq(query.functions.second(mydatevar)) 
foundset.loadRecords(query);
sqrt

QBFunction sqrt (value)

Create sqrt(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mynum.sqrt.eq(query.functions.sqrt(myval))) 
foundset.loadRecords(query);
substring

QBFunction substring (arg, pos)

Create substring(column, pos) expression
Parameters
{Object} arg – column name
{Number} pos – position
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.substring(3).eq(query.functions.substring('Sample', 3))) 
foundset.loadRecords(query);
substring

QBFunction substring (arg, pos, len)

Create substring(column, pos, len) expression
Parameters
{Object} arg – column name
{Number} pos – position
{Number} len – length
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.substring(3, 2).eq(query.functions.substring('Sample', 3, 2))) 
foundset.loadRecords(query);
trim

QBFunction trim (value)

Create trim(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.trim.eq(query.functions.trim('Sample'))) 
foundset.loadRecords(query);
upper

QBFunction upper (value)

Create upper(column) expression
Parameters
{Object} value
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('Sample'))) 
foundset.loadRecords(query);
year

QBFunction year (arg)

Create year(date) expression
Parameters
{Object} arg – date object
Returns
QBFunction
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.mydatecol.year.eq(query.functions.year(mydatevar)) 
foundset.loadRecords(query);
  • No labels