rbv_api.selectQuery()

Warning: Support for using this method with external objects (such as those mapped to external tables or through a HDP connection) is a beta feature. This method is supported in production systems, except for external objects.

Purpose

Runs an SQL SELECT query and returns results as a 2D-array. For Private Cloud customers, the maximum number of rows to return can be configured: see MaxReqsInQuery parameter in the description of the Shared Properties file.

The SELECT query used in this method consists of the following parts:

  • The SELECT statement expects columns or expressions to be selected (mandatory). Use the field integration names as SQL column names. You can use expressions such as COUNT(1). You cannot use * to retrieve all columns.
  • The FROM clause must consist of exactly one object name (mandatory).
  • The WHERE clause can include a valid SQL expression to narrow the selection (optional). Use field integration names as SQL column names.
  • The ORDER BY clause can include a valid SQL expression to order the selection (optional). Use field integration names as SQL column names.

You can use special tokens in your queries such as:

  • TODAY for current time
  • WEEK for 12PM of last Sunday
  • MONTH for 12PM of 1st day of current month
  • QUARTER for 12PM of 1st day of current quarter
  • YEAR for 12PM of 1st day of current year
  • CURR_USER for id of currently logged in user

Object and Field names are case-sensitive, while other components of the SQL query are not.

Use #code suffix to fetch integration codes for picklist fields rather than IDs. See Adding business logic for more information.

Syntax

rbv_api.selectQuery (query, maxRows, arg1, arg2…)

Parameters

query

SQL SELECT query. See Query API for examples and limitations.

maxRows

Maximum number of rows to retrieve (1-20,000 range)

args

Variable number of parameters passed to query (optional)

Return value

Query result in a 2-D array

Permissions required

View permission for the selected object type.

Example

The following example uses selectQuery() to obtain Line Item object records. The WHERE clause with the value R8011457=? retrieves only records related to the current order. The actual ID is passed as parameter, which is more efficient because it embeds the value directly into the query. The #code suffix for the column category fetches the integration name instead of the ID.

var arr = rbv_api.selectQuery( "select name, amount, price,
category#code from line_item where R8011457=?", 100, {!id});
var buff = "Name, Amount, Price<br>";
for (var i=0; i<arr.length; i++)
{ buff += arr[i][0]+", "+arr[i][1]+",
"+arr[i][2] +", "+arr[i][3]+"<br>";
}
return buff;

Query API LIMIT Clause

This API now automatically applies an implicit LIMIT clause to parsed SQL queries to improve performance. Manual LIMIT or OFFSET clauses in query inputs are no longer recommended and will be unsupported in future platform releases.

Post-Migration guidance

  • Remove any manual LIMIT clauses from queries used with these APIs.

  • After upgrading to Platform v6.11.7.1, review deprecated queries by navigating to Customer > View Logs > sql_deprecation.log. A System Administrator can access this log.