Mar 16, 2024 08:37 Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients SampleSupported 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.
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.
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
Object
value
;
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
Object
value
;
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.
Object
value
object to cast
String
type
type see QUERY_COLUMN_TYPES
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
Object
arg
number object
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
Array
args
arguments to coalesce
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
Object
arg1
;
Object
arg2
;
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)
String
name
custom function name
Array
args
function arguments
// 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
Object
arg
date object
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
Object
arg1
;
Object
arg2
;
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
Object
arg
number object
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
Object
arg
date object
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
Object
value
;
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
Object
string1
string to locate
Object
string2
string to search in
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
Object
string1
string to locate
Object
string2
string to search in
Number
start
start pos
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
Object
value
;
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
Object
arg1
;
Object
arg2
;
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
Object
arg
date object
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
Object
dividend
;
Object
divisor
;
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
Object
arg
date object
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
Object
arg1
;
Object
arg2
;
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
Object
arg1
;
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
Object
arg1
;
Object
arg2
;
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
Object
arg
number object
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
Object
arg
number object
Number
decimals
The number of decimal places to round number to, default 0
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
Object
arg
date object
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
Object
value
;
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
Object
arg
column name
Number
pos
position
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
Object
arg
column name
Number
pos
position
Number
len
length
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
Object
value
;
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)
String
leading_trailing_both
'leading', 'trailing' or 'both'
String
characters
characters to remove
String
fromKeyword
'from'
Object
value
value to trim
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
Object
value
;
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
Object
arg
date object
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.year.eq(query.functions.year(mydatevar))
foundset.loadRecords(query);