Get query builder parent table clause, this may be a query or a join clause. |
|
Get query builder parent. |
|
|
Create abs(column) expression |
|
Create bit_length(column) expression |
|
Create ceil(column) expression |
|
Create year(date) 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 | trailing | both] [characters] from column) |
|
Create upper(column) expression |
|
Create year(date) expression |
|
|
Get query builder parent table clause, this may be a query or a join clause. |
|
|
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) |
|
|
|
|
Get query builder parent. |
|
|
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) |
|
|
|
|
|
Create abs(column) expression |
|
|
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); |
|
|
|
|
Create bit_length(column) expression |
|
|
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); |
|
|
|
|
Parameters type see QUERY_COLUMN_TYPES |
|
|
|
|
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()) |
|
|
|
|
Create ceil(column) expression |
|
|
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); |
|
|
|
|
Create year(date) expression |
|
|
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); |
|
|
|
|
Create concat(args, arg2) expression |
|
|
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) |
|
|
|
|
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); |
|
|
|
|
Create day(date) expression |
|
|
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); |
|
|
|
|
Create divide(args, arg2) expression |
|
|
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); |
|
|
|
|
Create floor(column) expression |
|
|
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); |
|
|
|
|
Create hour(date) expression |
|
|
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); |
|
|
|
|
Create length(column) expression |
|
|
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); |
|
|
|
|
Create locate(str1, str2) expression |
|
|
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 |
|
|
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); |
|
|
|
|
Create lower(column) expression |
|
|
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); |
|
|
|
|
Create minus(args, arg2) expression |
|
|
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); |
|
|
|
|
Create minute(date) expression |
|
|
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); |
|
|
|
|
Create mod(dividend, divisor) expression |
|
|
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); |
|
|
|
|
Create month(date) expression |
|
|
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); |
|
|
|
|
Create multiply(args, arg2) expression |
|
|
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); |
|
|
|
|
Create nullif(arg1, arg2) expression |
|
|
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); |
|
|
|
|
Create plus(args, arg2) expression |
|
|
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); |
|
|
|
|
Create round(column) expression |
|
|
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); |
|
|
|
|
Create round(column) expression |
|
|
Parameters The number of decimal places to round number to, default 0 |
|
|
|
|
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); |
|
|
|
|
Create second(date) expression |
|
|
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); |
|
|
|
|
Create sqrt(column) expression |
|
|
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); |
|
|
|
|
Create substring(column, pos) expression |
|
|
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); |
|
|
|
|
Create substring(column, pos, len) expression |
|
|
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); |
|
|
|
|
Create trim(column) expression |
|
|
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 'leading', 'trailing' or 'both' |
|
|
|
|
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); |
|
|
|
|
Create upper(column) expression |
|
|
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); |
|
|
|
|
Create year(date) expression |
|
|
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); |
|
|
|
|
|
|