Skip to main content

Best-practice samples for common queries

Zuora

Best-practice samples for common queries

See the following samples of best practices for some common use cases.

  • Get a list of Subscriptions and the associated Accounts whose custom field geographicregion__c is West
    SELECT subscription.id, account.id
    FROM Subscription
    INNER JOIN account ON subscription.accountid = account.id
    WHERE account.geographicregion__c = ‘West’
    
  • Get the Products updated since January 1, 2019
    SELECT *
    FROM Product
    WHERE updateddate >= timestamp '2019-01-01 -7:00'
    
  • Get the Accounts with Usage for the Products with specified SKUs
    SELECT a.accountnumber
    FROM Usage u, Account a, Product p
    WHERE a.accountnumber = u.accountnumber
    AND p.sku IN
      (
          'SKU-200001',
          'SKU-200002',
          'SKU-200003',
          'SKU-200004'
      )
    GROUP BY a.accountnumber 
  • Get Attachments for the object by specifying the associated object ID and associated object type in WHERE clause
    SELECT filename,
           fileid,
           filecontenttype
    FROM   attachment
    WHERE  associatedobjecttype = 'account'
           AND associatedobjectid = 'ff808081298c6e5401298c76f28b006c'