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 creating a related record 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, LONGTEXT(review_info__c) AS info FROM country__v WHERE region__c=" + "'" + region + "'";
     QueryResponse queryResponse = queryService.query(queryCountry);

     List countryReviewRecords = VaultCollections.newList();
     // Create the related Country Review records
     queryResponse.streamResults().forEach(queryResult -> {
          String countryId = queryResult.getValue("id", ValueType.STRING);
          // When a function alias is used, retrieve the value using the alias
          String reviewInfo = queryResult.getValue("info", ValueType.STRING);
          Record record = recordService.newRecord("country_review__c");
          record.setValue("country__c", countryId);
          record.setValue("info__c", reviewInfo);
          countryReviewRecords.add(record);
     });
     // Batch save new Country Review records
     recordService.batchSaveRecords(countryReviewRecords).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–2021. All rights reserved.