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;