selectNumber()

Purpose

Runs an SQL SELECT query on the server and returns a single decimal value as the result. This is a simplified version of selectQuery() and is suitable for calculations such as finding a sum of values.

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

selectNumber(string sessionId, string query);

Parameters

sessionId

A string containing the session ID obtained at log in.

query

A SQL SELECT query. See the examples described for selectQuery().

Output

Single result of the SELECT query as a decimal number

Permissions Required

View permission for the requested object type.

Example

Double total = binding.selectNumber(sessionId, 
    "SELECT SUM(amount) FROM customer WHERE name LIKE 'M%'");