Querying the Portfolios Region

The portfolios example continues, showing a sampling of specific queries. The query results for the data are listed in the table. For the first several, the coding examples are included as well to show how to execute the queries using the API.

Get distinct positions from portfolios with at least a $25.00 market value

This query assigns iterator variable names to the collections in the FROM clause. For example, the variable qryP is the iterator for the entry values in the portfolios region. This variable is used in the second part of the FROM clause to access the values of the positions map for each entry value.

Query string: 
SELECT DISTINCT posnVal
FROM /portfolios, positions.values posnVal TYPE Position
WHERE posnVal.mktValue >= 25.00

Results: 
Collection of Position instances with secId: xxx, xxy, bbb, bbc

Retrieve all active portfolios

In the following example, a query response timeout parameter of 10 seconds is specified for the execute method to allow sufficient time for the operation to succeed.

Query string: 
SELECT DISTINCT * FROM /portfolios WHERE status = ‘active’

Results: 
A collection of Portfolio objects for IDs 111, 222, and 333

Code: 
QueryServicePtr qrySvcPtr = cachePtr->getQueryService("examplePool");
QueryPtr qry = qrySvcPtr->newQuery(
               "SELECT DISTINCT * FROM /portfolios WHERE status = ‘active’");
SelectResultsPtr resultsPtr = qry->execute(10);
SelectResultsIterator iter = resultsPtr->getIterator();
while (iter.hasNext()) {
    PortfolioPtr portfolio = dynCast<PortfolioPtr >(iter.next());
}

Retrieve all active portfolios that have type xyz

The type attribute is passed to the query engine in double quotes to distinguish it from the query keyword of the same name. A query response timeout parameter of 10 seconds is specified for the execute method to allow sufficient time for the operation to succeed.

Query string: 
SELECT DISTINCT * FROM /portfolios
WHERE status = 'active' AND "type" = 'xyz'

Results: 
A collection of Portfolio objects for IDs 111 and 222

Code: 
QueryServicePtr qrySvcPtr = cachePtr->getQueryService("examplePool");
QueryPtr qry = qrySvcPtr->newQuery("SELECT DISTINCT * FROM
               /portfolios WHERE status = 'active' and \"type\"='xyz'");
SelectResultsPtr results = qry->execute(10);
SelectResultsIterator iter = results->getIterator();
while (iter.hasNext()) {
    PortfolioPtr portfolio = dynCast<PortfolioPtr >(iter.next());
}

Get the ID and status of all portfolios with positions in secId ‘yyy’

Query string: 
SELECT DISTINCT id, status FROM /portfolios
WHERE NOT (SELECT DISTINCT * FROM positions.values posnVal TYPE
Position WHERE posnVal.secId='yyy').isEmpty

Results: 
A collection of Struct instances, each containing an id field and a status field. 
For this data, the collection length is 1 and the Struct contains data
from the entry with id 222.


Code: 
QueryServicePtr qrySrvPtr = cachePtr->getQueryService("examplePool");
QueryPtr qry = qrySvcPtr->newQuery(
   "import javaobject.Position; SELECT DISTINCT ID, status FROM "
   "/portfolios WHERE NOT (SELECT DISTINCT * FROM positions.values"
   "posnVal TYPE Position WHERE posnVal.secId='DELL').isEmpty");
SelectResultsPtr results = qry->execute(10);
SelectResultsIterator iter = results->getIterator();
while (iter.hasNext()) {
    Struct * si = (Struct *) iter.next().ptr();
    SerializablePtr id = si->operator[]("ID");
    SerializablePtr status = si->operator[]("status");
    printf("\nID=%s, status=%s", id->toString().c_str(), status->toString().c_str());
}