Skip navigation links

Package com.veeva.vault.sdk.api.query

This package provides interfaces to execute queries and retrieve data from query responses.

See: Description

Package com.veeva.vault.sdk.api.query Description

This package provides interfaces to execute queries and retrieve data from query responses.

The following objects are available for query:

For help with VQL queries, refer to the VQL reference.

Record Query

The following example illustrates querying an object and updating a field for all records returned by the query.
     
     // Locate services
     RecordService recordService = ServiceLocator.locate(RecordService.class);
     QueryService queryService = ServiceLocator.locate(QueryService.class);

     // Query the Country object to retrieve all countries in a region
     String queryCountry = "select id, name__v from country__v where region__c=" + "'" + region + "'";
     QueryResponse queryResponse = queryService.query(queryCountry);

     List updateCountryRecords = VaultCollections.newList();
     // Set the Review Date to one month from now
     queryResponse.streamResults().forEach(queryResult -> {
          String countryId = queryResult.getValue("id", ValueType.STRING);
          Record record = recordService.newRecordWithId("country__c", countryId);
          record.setValue("review_date__c", LocalDate.now().plusMonths(1));
          updateCountryRecords.add(record);
     });
     // Batch save Country updates
     recordService.batchSaveRecords(updateCountryRecords).ignoreErrors().execute();
     
 

Document Query

The following example illustrates querying documents and using the results to set a field on a document.

When a document enters the Approved state, this lifecycle entry action queries for the document's parent. Once found, we query a second time to get the value of the parent document's Expiration Date field. We then set this field value on the Parent Expiration Date field on our original, newly approved document.

 
      // Get current doc version and initialize services
      DocumentVersion docVersion = documentActionContext.getDocumentVersions().get(0);
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      DocumentService documentService = ServiceLocator.locate(DocumentService.class);
      String docId = docVersion.getValue("id", ValueType.STRING);

      // Get parent doc id with query service
      String parentDocumentQuery = "SELECT target_doc_id__v FROM relationships WHERE source_doc_id__v = '"
              + docId+ "' AND relationship_type__v = 'basedon__v' ";
      QueryResponse parentDocument = queryService.query(parentDocumentQuery);

      Iterator<QueryResult> iterator = parentDocument.streamResults().iterator();
      if (iterator.hasNext()) {
          QueryResult idResults = iterator.next();
          String parentDocId = idResults.getValue("target_doc_id__v", ValueType.STRING);
          // Get expiration date field from parent doc with query service
          String expirationDateQuery = "SELECT LATESTVERSION expiration_date__c FROM documents WHERE id = '" + parentDocId + "'";
          QueryResponse expirationDate = queryService.query(expirationDateQuery);
          // Set parent expiration date field on current doc
          docVersion.setValue("parent_expiration_date__c",
                ((QueryResult) expirationDate.streamResults().iterator().next()).getValue("expiration_date__c", ValueType.DATE));

          // Update current doc with document service
          List<DocumentVersion> documentsToUpdate = VaultCollections.newList();
          documentsToUpdate.add(docVersion);
          documentService.saveDocumentVersions(documentsToUpdate);
      }
      else {
          throw new RollbackException("OPERATION_NOT_ALLOWED", "Cannot find parent for document: " + docId);
      }
 
 

Performance Consideration

Generally, it is more efficient to construct a query statement with a WHERE clause to retrieve all the records you want to work with rather than running queries in a loop. Reducing the number of queries improves performance and response time.
Skip navigation links

Copyright © Veeva Systems 2017–2019. All rights reserved.