Group By Query

The $QueryGroup function can be used to create a value group based on the database query. There are two versions of the function. The first uses the default connection. The second allows the user to specify custom connection (predefined ODBC data source name). Both have the following parameters:

  1. Group No. Positive Integer.
  2. Query text. Additional quotation by '(' and ')' is required for the query that contains ','. The function (like $Pattern) call is acceptable as well.
  3. (optional) Query Mode, the default is 0.
    '1' means use data from result set sequentially i.e. the first row will be used after the last if necessary.
    '2' means rerun the query for each output row1. It is suitable for Oracle sequence requests, UID requests, etc.

The second version has three additional parameters:

  1. Predefined Data Source Name. String. Use ODBC Administrator* to prepare or manage it.
  2. (optional) Login. String, default is empty.
  3. (optional) Password. String, default is empty.

* you should use 32 or 64 bit version of the administrator depend on application version.

Note: function returns NULL value for an empty recordset


  1. $QueryGroup(1,(select AgentName,Code from Contracts)) - specifies query as data source and uses 'AgentName' column.
    $Group(1,2) - refers to the second column 'Code'.
  2. $QueryGroup(1,(select AgentName,Code from Contracts),0,MyDb,UserName,Password) - uses "MyDb" ODBC data source with specified user name and password.