BillingPlatform Query Language

The BP query language offers a powerful tool for retrieving data over both the SOAP and REST APIs as well as the Web tool-kit. All standard and custom objects and their relationships are available in BP SQL.

The BP Query language is structured much like other SQL languages with a number of exceptions:

  1. Explicit Table or "Entity" joins are not supported
  2. Relationships are referenced via the structured hierarchies constructed in the BP data model
  3. Only one Entity can be referenced in the "From" clause
  4. Use of the asterisk is not supported (ex: "select *")
  5. Query results are influenced by the Sharing Group configuration of the API user performing the query. If the API user is not supposed to access certain records based on the Sharing Group filter for its profile, the records will not be returned in the query response.

The following is a simple example selecting data from the Invoice Entity:

Select GrandTotalAmount
      ,PaymentAmount
      ,PaymentStatus
      ,ClosedDate
From Invoice

The Structure of the Syntax is as follows:

SELECT fieldList [,subquery][,...]
FROM entity
[WHERE conditionExpression 
[AND conditionExpression]
[OR conditionExpression]
[ROWNUM CONDITION]]
[GROUP BY {fieldGroupByList}
            [HAVING havingConditionExpression] ]
[ORDER BY fieldOrderByList {ASC|DESC}]

Syntax Item

Description

fieldList

General list of fields from the base Entity or Parent Entities. Can also include aggregate functions such as SUM, COUNT, AVG, etc..

  • Select Name, AccountType From Account
  • Select AccountObj.Name, City, State, Zip From BillingProfile
  • Select Sum(GrandTotalAmount) total from Invoice

The fields are not case sensitive so Name is the same as NAME and name. Field aliases can also be used without having to specify the 'AS' keyword for aggregated fields.

subQuery

Used to include records from a child relationship. Also referred to as an "inline view" in other languages:

Select GrandTotalAmount
	, (Select Cost, TaxCost from Activity.InvoiceObj)
From Invoice

Subqueries can be further filtered using WHERE as needed:

Select GrandTotalAmount
	, (Select Cost, TaxCost from Activity.InvoiceObj WHERE Cost > 0.00)
From Invoice

entity

The focus or root of the query. Only one Entity can be specified in the "Where" clause and is not case sensitive.

conditionExpression 

Conditions on what will be filtered in the SQL statement. Sub-queries can be used here such as:

select Id, Name
from PACKAGE 
where Id IN (select PackageProductIdObj.PackageIdObj.Id 
	from CONTRACT_RATE)
and rownum < 5

fieldGroupByList

List of fields to Group by when using Aggregate functions such as SUM and COUNT in a query. 

Select SUM(GrandTotalAmount) total
	, Status
From Invoice
Group By Status

havingConditionExpression

Used to further filter a Grouped Result 

Select SUM(GrandTotalAmount) total
	, Status
From Invoice
Group By Status
Having sum(GrandTotalAmount) > 1000

fieldOrderByList

The list fields to order the results by Ascending or descending. 

Select GrandTotalAmount
	, PaymentAmount
	, PaymentStatus
	, ClosedDate
From Invoice
Order By GrandTotalAmount Desc
	, ClosedDate Asc

 Relationships

Relationships are defined in BillingPlatform using either the Lookup field or the Embedded List control. Relationships are referenced using the "Obj" syntax:

[Parent Lookup Field]Obj

Reference a parent object’s field as follows:

[Parent Lookup Field]Obj.[Parent Field]

For Example, a lookup on the Invoice Object to the BillingProfile Entity called "BillingProfile" will be called "BillingProfileObj" and if you were to reference the parent BillingProfile’s "Address1" field it would look like this:

SELECT BillingProfileObj.Address1 FROM Invoice

Multi-level parents (e.g. Invoice > Billing Profile > Account) can be accessed by using the same reference model continuously. For example:

SELECT BillingProfileObj.AccountObj.Name from INVOICE

Child relationships are accessed using Sub-Queries. The select and where clause of the sub-query is identical to the standard query language. The from clause uses the following construct:

[Child Entity].[Parent Lookup Field]Obj

For example, the relationship between the Invoice Entity and the BillingProfile Entity, using the BillingProfile as the driver can be referenced in a sub-query as follows:

SELECT Id, AccountObj.Name
	, (SELECT GrandTotalAmount
	FROM Invoice.BillingProfileObj)
FROM BillingProfile

In the above example, the Invoice Entity has a lookup filed referencing the BillingProfile Entity called "BillingProfile".

Alternatively, the [Child Entity].[Parent Lookup Field]Obj can be written as [Child Entity]__[ParentLookup Field]Obj, where the "." is replaced by double underscores "__". This will make the query consistent with the other object references within the app (New in release 7.0 and up) particularly when you are familiar with using the web toolkit of the platform.

Avoiding Sub-queries For Child Objects

Starting with release 7.0 of the platform, you can also directly access fields of child objects relative to the entity used on your FROM clause. This can be done by using the following notation: [Child Entity]__[Parent Lookup Field]Obj.[Child Field]. For example, to access the Id and GrandTotalAmount fields on the INVOICE when creating a query using BILLING_PROFILE on the FROM clause, you can use either of the following methods. Note that this can only be done if the double underscore notation is used on the query:

Sub-Query Child Field Access
SELECT Id, (
	SELECT Id, GrandTotalAmount
	FROM Invoice__BillingProfileIdObj
	)
FROM Billing_Profile
WHERE Id = 12345
SELECT Id, Invoice__BillingProfileIdObj.Id,
	Invoice__BillingProfileIdObj.GrandtotalAmount
FROM Billing_Profile
WHERE Id = 12345

The advantage of using Child Field Access is that you can apply aggregate functions when you cannot perform them using sub-queries.

You can also go down multiple levels in the data model. For example, an Invoice record is associated to a Billing Profile record, which in turn is mapped to an Account record. If we need to pull fields from the Invoice object while using the Account object as the source/anchor in the SQL c/o the FROM clause, the following can be done.

Sub-Query Child Field Access
SELECT id, name, (
	SELECT id, (
		SELECT id, grandtotalamount 
		FROM invoice.billingprofileidobj
		)
	FROM billing_profile.accountidobj
	)
FROM account where id = 34610
SELECT id, name, Billing_Profile__AccountIdObj.Id,
	Billing_Profile__AccountIdObj.Invoice__BillingProfileIdObj.Id,
	Billing_Profile__AccountIdObj.Invoice__BillingProfileIdObj.GrandTotalAmount
FROM account
WHERE id = 34610

Here are some examples of how queries with relationships would be executed using the SOAP API:

Sample SOAP Request showing two levels of relationships in a nested sub-query

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:stronglytyped.soap.brmsystems.com">
   <soapenv:Header>
      <urn:SessionHeader>
         <urn:SessionID>hmeMTcxKWavEAnvkpupOOszawLyePdyLbVhlQBsz</urn:SessionID>
      </urn:SessionHeader>
   </soapenv:Header>
   <soapenv:Body>
      <urn:Query>
         <urn:Sql>SELECT AccountObj.Name ← Parent Relationship
                       , AccountObj.Type
                       , AccountObj.Status
                       , Address1
                       , Address2
                       , City
                       , State
                       , Zip
                       ,(SELECT GrandTotalAmount ← Child Relationship/Sub-Query
                              , PaymentAmount
                              , PaymentStatus
                              , ClosedDate
                           FROM Invoice.BillingProfileObj)
                 ,(SELECT Amount  ← Nested Child Relationship/Sub-Query                                           
                       , PaymentDate
                       , PaymentType
                       , BillingProfileObj.AccountObj.Name
                    FROM Payment.BillingProfileObj)
                    FROM Billing_Profile ← Driver Entity
                   WHERE AccountObj.Name like 'Blue Moon%'← Where referencing parent field
         </urn:Sql>
      </urn:Query>
   </soapenv:Body>
</soapenv:Envelope>

Sample SOAP Response Showing Hierarchical Result Set with Nested Objects.

<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:urn="urn:stronglytyped.soap.brmsystems.com" >
    <soapenv:Body>
        <urn:QueryResponse
            xmlns:urn="urn:stronglytyped.soap.brmsystems.com"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <urn:Result xsi:type="urn:BILLING_PROFILE">
                <urn:Id>40973</urn:Id>
                <urn:Address1>RTP Address1</urn:Address1>
                <urn:Address2></urn:Address2>
                <urn:City>RTP City</urn:City>
                <urn:State>Colorado</urn:State>
                <urn:Zip>80111</urn:Zip>
                <urn:PaymentBillingProfileObj>
                    <urn:Id>449553</urn:Id>
                    <urn:Amount>0</urn:Amount>
                    <urn:PaymentDate>2015-05-15T09:33:06.241000-06:00</urn:PaymentDate>
                    <urn:PaymentType>CREDIT CARD</urn:PaymentType>
                    <urn:BillingProfileObj>
                        <urn:Id>40973</urn:Id>
                        <urn:AccountObj>
                            <urn:Id>10205</urn:Id>
                            <urn:Name>Blue Moon -- ATT</urn:Name>
                        </urn:AccountObj>
                    </urn:BillingProfileObj>
                </urn:PaymentBillingProfileObj>
                <urn:InvoiceBillingProfileObj>
                    <urn:Id>414106</urn:Id>
                    <urn:GrandTotalAmount>101</urn:GrandTotalAmount>
                    <urn:PaymentAmount>0</urn:PaymentAmount>
                    <urn:PaymentStatus>NOT PAID</urn:PaymentStatus>
                    <urn:ClosedDate></urn:ClosedDate>
                </urn:InvoiceBillingProfileObj>
                <urn:AccountObj>
                    <urn:Id>10205</urn:Id>
                    <urn:Name>Blue Moon -- ATT</urn:Name>
                    <urn:Type>Partner Retail</urn:Type>
                    <urn:Status>ACTIVE</urn:Status>
                </urn:AccountObj>
            </urn:Result>
        </urn:QueryResponse>
    </soapenv:Body>
</soapenv:Envelope>
 

Fields from Related Entities can be selected for up to 6 levels deep for parent Entity attributes and up to three levels deep for child, sub-queries. The example below demonstrates a multi-level, parent Query executed over the SOAP API:

SOAP Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:stronglytyped.soap.brmsystems.com">
   <soapenv:Header>
      <urn:SessionHeader>
         <urn:SessionID>hmeMTcxKWavEAnvkpupOOszawLyePdyLbVhlQBsz</urn:SessionID>
      </urn:SessionHeader>
   </soapenv:Header>
   <soapenv:Body>
      <urn:Query>
         <urn:Sql>SELECT InvoiceId
                       , Amount
                       , InvoiceObj.GrandTotalAmount ← one level up
                       , InvoiceObj.BillingProfileObj.City ← two levels up
                       , InvoiceObj.BillingProfileObj.AccountObj.Name ← three levels up
                       , InvoiceObj.BillingProfileObj.State
                       , InvoiceObj.BillingProfileObj.Zip
                       , PaymentItemObj.Amount
                    FROM PAYMENT_ALLOCATION
         </urn:Sql>
      </urn:Query>
   </soapenv:Body>
</soapenv:Envelope>
 

SOAP Response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:urn="urn:stronglytyped.soap.brmsystems.com">
   <soapenv:Body>
      <urn:QueryResponse>
         <urn:Result xsi:type="urn:PAYMENT_ALLOCATION">
            <urn:Id>448513</urn:Id>
            <urn:InvoiceId>414260</urn:InvoiceId>
            <urn:Amount>750</urn:Amount>
            <urn:InvoiceObj>
               <urn:Id>414260</urn:Id>
               <urn:GrandTotalAmount>1114</urn:GrandTotalAmount>
               <urn:BillingProfileObj>
                  <urn:Id>37730</urn:Id>
                  <urn:City>Fort Collins</urn:City>
                  <urn:State>CO</urn:State>
                  <urn:Zip>80567</urn:Zip>
                  <urn:AccountObj>
                     <urn:Id>13087</urn:Id>
                     <urn:Name>Running With Scissors</urn:Name>
                  </urn:AccountObj>
               </urn:BillingProfileObj>
            </urn:InvoiceObj>
            <urn:PaymentItemObj>
               <urn:Id>448512</urn:Id>
               <urn:Amount>750</urn:Amount>
            </urn:PaymentItemObj>
         </urn:Result>
         <urn:Result xsi:type="urn:PAYMENT_ALLOCATION">
            <urn:Id>448499</urn:Id>
            <urn:InvoiceId>448411</urn:InvoiceId>
            <urn:Amount>1000</urn:Amount>
            <urn:InvoiceObj>
               <urn:Id>448411</urn:Id>
               <urn:GrandTotalAmount>2363.87</urn:GrandTotalAmount>
               <urn:BillingProfileObj>
                  <urn:Id>55123</urn:Id>
                  <urn:City>Budapest</urn:City>
                  <urn:State/>
                  <urn:Zip>61685</urn:Zip>
                  <urn:AccountObj>
                     <urn:Id>26335</urn:Id>
                     <urn:Name>Accelsiors LTD</urn:Name>
                  </urn:AccountObj>
               </urn:BillingProfileObj>
            </urn:InvoiceObj>
            <urn:PaymentItemObj>
               <urn:Id>448498</urn:Id>
               <urn:Amount>1000</urn:Amount>
            </urn:PaymentItemObj>
         </urn:Result>
      </urn:QueryResponse>
   </soapenv:Body>
</soapenv:Envelope>
 

REST Samples

REST Requests will add the URL-Encoded Query Statement to the "SQL" parameter like so:

https://sandbox.billingplatform.com/<yourOrg>/rest/2.0/query?sql=SELECT+Status%2C+Sum%28GrandTotalAmount%29+as+total++FROM+Invoice++WHERE+BillingProfileObj.AccountObj.Name+like+%27%25Blue%25%27+GROUP+BY+Status

Responses will be returned in JSON format.

Query Results

Query Results come in two basic types:

  1. Hierarchical List of Objects for standard queries
  2. List of Name-value pairs for Queries using aggregate functions

 Hierarchical Query results

 The Example output above shows the Results of a standard query with several object hierarchies. The relationships referenced in the Query are transformed in the results. Fields from parent relationships are contained within the Parent Object, named the same as the relationship. To review, the naming of Parent Relationships is as follows:

     [Parent Lookup Field]Obj

Reference a parent object’s field as follows:

       [Parent Lookup Field]Obj.[Parent Field]

For Example, in the following query we reference three parent object Relationships: InvoiceObj, PaymentItemObj and BillingProfileObj. Two Parent Object’s Parent relationships are also referenced.

SELECT InvoiceId
	, Amount
	, InvoiceObj.GrandTotalAmount
	, InvoiceObj.BillingProfileObj.AccountObj.Name
	, InvoiceObj.BillingProfileObj.City
	, InvoiceObj.BillingProfileObj.State
	, InvoiceObj.BillingProfileObj.Zip
	, PaymentItemObj.Amount
FROM PAYMENT_ALLOCATION

Notice that the results contain nested, Hierarchical relationships with the Relationship Names at the root:

<urn:QueryResponse>
         <urn:Result xsi:type="urn:PAYMENT_ALLOCATION">
            <urn:Id>448513</urn:Id>
            <urn:InvoiceId>414260</urn:InvoiceId>
            <urn:Amount>750</urn:Amount>
            <urn:InvoiceObj>
               <urn:Id>414260</urn:Id>
               <urn:GrandTotalAmount>1114</urn:GrandTotalAmount>
               <urn:BillingProfileObj>
                  <urn:Id>37730</urn:Id>
                  <urn:City>Fort Collins</urn:City>
                  <urn:State>CO</urn:State>
                  <urn:Zip>80567</urn:Zip>
                <urn:AccountObj>
                 <urn:Id>13087</urn:Id>
                 <urn:Name>Running With Scissors</urn:Name>
                 </urn:AccountObj>
              </urn:BillingProfileObj>
           </urn:InvoiceObj>
           <urn:PaymentItemObj>
              <urn:Id>448512</urn:Id>
              <urn:Amount>750</urn:Amount>
           </urn:PaymentItemObj>
        </urn:Result>
</urn:QueryResponse>

Child Relationships follow a slightly different pattern in terms of the relationship reference and the results returned. Take the following Query for example in which we reference two sub-queries to child Entities of the Parent, BillingProfile Entity, Invoice and Payment:

 

SELECT AccountObj.Name
	, AccountObj.Type
	, AccountObj.Status
	, Address1
	, Address2
	, City
	, State
	, Zip
	,(SELECT GrandTotalAmount
		, PaymentAmount
		, PaymentStatus
		, ClosedDate
	 FROM Invoice.BillingProfileObj)
     ,(SELECT Amount
	     , PaymentDate
		, PaymentType
		, BillingProfileObj.AccountObj.Name
	FROM Payment.BillingProfileObj)
FROM Billing_Profile
WHERE AccountObj.Name like 'Blue Moon%'
 

As discussed earlier, the pattern for referencing relationships in sub queries is the inverse of the parent reference.

[Child Entity].[Parent Lookup Field]Obj

OR

[Child Entity]__[Parent Lookup Field]Obj

Here we reference the Entity followed by a dot (or double underscores) and then the Parent Relationship:

Invoice.BillingProfileObj
Payment.BillingProfileObj

Here the Payment and invoice Entities have a parent, lookup relationship to the BillingProfile Entity called "BillingProfile". The relationship for both is called BillingProfileObj.

Since it is not good practice to include dots inside of Object names, the results for these types of queries name the sub-query objects with their Entity and Relationship names without the use of the dot operator:

InvoiceBillingProfileObj
PaymentBillingProfileObj

However, if the double underscore "__" notation is used, the results will be rendered with the underscores. For instance, if the above sub query references are rewritten as:

Invoice__BillingProfileObj
Payment__BillingProfileObj

At this point, responses will be formatted in the same manner such as:

Invoice__BillingProfileObj
Payment__BillingProfileObj

This makes using the double underscore notation more desirable for consistency.

Sample SOAP Response ("." notation)

<urn:QueryResponse
            xmlns:urn="urn:stronglytyped.soap.brmsystems.com"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <urn:Result xsi:type="urn:BILLING_PROFILE">
                <urn:Id>40973</urn:Id>
                <urn:Address1>RTP Address1</urn:Address1>
                <urn:Address2></urn:Address2>
                <urn:City>RTP City</urn:City>
                <urn:State>Colorado</urn:State>
                <urn:Zip>80111</urn:Zip>
                <urn:PaymentBillingProfileObj>
                    <urn:Id>449553</urn:Id>
                    <urn:Amount>0</urn:Amount>
                    <urn:PaymentDate>2015-05-15T09:33:06.241000-06:00</urn:PaymentDate>
                    <urn:PaymentType>CREDIT CARD</urn:PaymentType>
                    <urn:BillingProfileObj>
                        <urn:Id>40973</urn:Id>
                        <urn:AccountObj>
                            <urn:Id>10205</urn:Id>
                            <urn:Name>Blue Moon -- ATT</urn:Name>
                        </urn:AccountObj>
                    </urn:BillingProfileObj>
                </urn:PaymentBillingProfileObj>
                <urn:InvoiceBillingProfileObj>
                    <urn:Id>414106</urn:Id>
                    <urn:GrandTotalAmount>101</urn:GrandTotalAmount>
                    <urn:PaymentAmount>0</urn:PaymentAmount>
                    <urn:PaymentStatus>NOT PAID</urn:PaymentStatus>
                    <urn:ClosedDate></urn:ClosedDate>
                </urn:InvoiceBillingProfileObj>
                <urn:AccountObj>
                    <urn:Id>10205</urn:Id>
                    <urn:Name>Blue Moon -- ATT</urn:Name>
                    <urn:Type>Partner Retail</urn:Type>
                    <urn:Status>ACTIVE</urn:Status>
                </urn:AccountObj>
            </urn:Result>
        </urn:QueryResponse>

Sample SOAP Response ("__" notation)

<urn:QueryResponse
            xmlns:urn="urn:stronglytyped.soap.brmsystems.com"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <urn:Result xsi:type="urn:BILLING_PROFILE">
                <urn:Id>40973</urn:Id>
                <urn:Address1>RTP Address1</urn:Address1>
                <urn:Address2></urn:Address2>
                <urn:City>RTP City</urn:City>
                <urn:State>Colorado</urn:State>
                <urn:Zip>80111</urn:Zip>
                <urn:Payment__BillingProfileObj>
                    <urn:Id>449553</urn:Id>
                    <urn:Amount>0</urn:Amount>
                    <urn:PaymentDate>2015-05-15T09:33:06.241000-06:00</urn:PaymentDate>
                    <urn:PaymentType>CREDIT CARD</urn:PaymentType>
                    <urn:BillingProfileObj>
                        <urn:Id>40973</urn:Id>
                        <urn:AccountObj>
                            <urn:Id>10205</urn:Id>
                            <urn:Name>Blue Moon -- ATT</urn:Name>
                        </urn:AccountObj>
                    </urn:BillingProfileObj>
                </urn:PaymentBillingProfileObj>
                <urn:Invoice__BillingProfileObj>
                    <urn:Id>414106</urn:Id>
                    <urn:GrandTotalAmount>101</urn:GrandTotalAmount>
                    <urn:PaymentAmount>0</urn:PaymentAmount>
                    <urn:PaymentStatus>NOT PAID</urn:PaymentStatus>
                    <urn:ClosedDate></urn:ClosedDate>
                </urn:InvoiceBillingProfileObj>
                <urn:AccountObj>
                    <urn:Id>10205</urn:Id>
                    <urn:Name>Blue Moon -- ATT</urn:Name>
                    <urn:Type>Partner Retail</urn:Type>
                    <urn:Status>ACTIVE</urn:Status>
                </urn:AccountObj>
            </urn:Result>
        </urn:QueryResponse>
 

Sample REST Response ("." notation)

{
    "queryResponse": [
        {
            "Id": "40973",
            "Address1": "RTP Address1",
            "Address2": null,
            "City": "RTP City",
            "State": "Colorado",
            "Zip": "80111",
            "AccountObj": {
                "Id": "10205",
                "Name": "Blue Moon -- ATT",
                "Type": "Partner Retail",
                "Status": "ACTIVE"
            },
            "InvoiceBillingProfileObj": [
                {
                    "Id": "414106",
                    "GrandTotalAmount": "101",
                    "PaymentAmount": "0",
                    "PaymentStatus": "NOT PAID",
                    "ClosedDate": null
                },
                {
                    "Id": "414107",
                    "GrandTotalAmount": "25024",
                    "PaymentAmount": "0",
                    "PaymentStatus": "NOT PAID",
                    "ClosedDate": "2015-04-02T10:04:09.000000-06:00"
                }
            ],
            "PaymentBillingProfileObj": [
                {
                    "Id": "449461",
                    "Amount": "777",
                    "PaymentDate": "2015-05-07T10:38:35.863000-06:00",
                    "PaymentType": "CREDIT CARD",
                    "BillingProfileObj": {
                        "Id": "40973",
                        "AccountObj": {
                            "Id": "10205",
                            "Name": "Blue Moon -- ATT"
                        }
                    }
                },
                {
                    "Id": "449455",
                    "Amount": "123",
                    "PaymentDate": "2015-05-07T10:33:49.106000-06:00",
                    "PaymentType": "CREDIT CARD",
                    "BillingProfileObj": {
                        "Id": "40973",
                        "AccountObj": {
                            "Id": "10205",
                            "Name": "Blue Moon -- ATT"
                        }
                    }
                }
            ]
        }
    ]
}

Sample REST Response ("__" notation)

{
    "queryResponse": [
        {
            "Id": "40973",
            "Address1": "RTP Address1",
            "Address2": null,
            "City": "RTP City",
            "State": "Colorado",
            "Zip": "80111",
            "AccountObj": {
                "Id": "10205",
                "Name": "Blue Moon -- ATT",
                "Type": "Partner Retail",
                "Status": "ACTIVE"
            },
            "Invoice__BillingProfileObj": [
                {
                    "Id": "414106",
                    "GrandTotalAmount": "101",
                    "PaymentAmount": "0",
                    "PaymentStatus": "NOT PAID",
                    "ClosedDate": null
                },
                {
                    "Id": "414107",
                    "GrandTotalAmount": "25024",
                    "PaymentAmount": "0",
                    "PaymentStatus": "NOT PAID",
                    "ClosedDate": "2015-04-02T10:04:09.000000-06:00"
                }
            ],
            "Payment__BillingProfileObj": [
                {
                    "Id": "449461",
                    "Amount": "777",
                    "PaymentDate": "2015-05-07T10:38:35.863000-06:00",
                    "PaymentType": "CREDIT CARD",
                    "BillingProfileObj": {
                        "Id": "40973",
                        "AccountObj": {
                            "Id": "10205",
                            "Name": "Blue Moon -- ATT"
                        }
                    }
                },
                {
                    "Id": "449455",
                    "Amount": "123",
                    "PaymentDate": "2015-05-07T10:33:49.106000-06:00",
                    "PaymentType": "CREDIT CARD",
                    "BillingProfileObj": {
                        "Id": "40973",
                        "AccountObj": {
                            "Id": "10205",
                            "Name": "Blue Moon -- ATT"
                        }
                    }
                }
            ]
        }
    ]
}
 

Query Results with Aggregation Functions

Queries that leverage aggregation functions such as SUM and MAX do not support sub-queries and must always contain an appropriate "Group By" clause. The following is a sample Query using Aggregation functions executed over the SOAP API.

Sample SOAP Request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:stronglytyped.soap.brmsystems.com">
   <soapenv:Header>
      <urn:SessionHeader>
         <urn:SessionID>LhzOGvPQemPFhUpyxljAcPgKaoQlTKANoNZkwHJr</urn:SessionID>
      </urn:SessionHeader>
   </soapenv:Header>
   <soapenv:Body>
      < urn:Query >
         <urn:Sql>SELECT Status
                       , '$' || Sum(GrandTotalAmount) as total
                    FROM Invoice
                   WHERE BillingProfileObj.AccountObj.Name like '%Blue%'
                   GROUP BY Status
   </urn:Sql>
      </urn:Query>
   </soapenv:Body>
</soapenv:Envelope>
 

Sample SOAP Response (ID is always null in the responses that use aggregation)

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:urn="urn:stronglytyped.soap.brmsystems.com">
   <soapenv:Body>
      <urn:QueryResponse>
         <urn:Result xsi:type="urn:BrmAggregate">
            <urn:Id xsi:nil="true"/>
            <urn:Status>CLOSED</urn:Status>
            <urn:total>$25024</urn:total>
         </urn:Result>
         <urn:Result xsi:type="urn:BrmAggregate">
            <urn:Id xsi:nil="true"/>
            <urn:Status>CURRENT</urn:Status>
            <urn:total>$101</urn:total>
         </urn:Result>
      </urn:QueryResponse>
   </soapenv:Body>
</soapenv:Envelope>

Sample REST Request

REST Requests will add the URL-Encoded Query Statement to the "sql" parameter like so:

https://sandbox.billingplatform.com/<yourOrg>/rest/2.0/query?sql=SELECT+Status%2C+%27%24%27+%7C%7C+Sum%28GrandTotalAmount%29+as+total+%0D%0AFROM+Invoice%0D%0AWHERE+BillingProfileObj.AccountObj.Name+like+%27%25Blue%25%27%0D%0AGROUP+BY+Status

Sample REST Response

 {
    "queryResponse": [
        {
            "Id": null,
            "Status": "CLOSED",
            "total": "$25024"
        },
        {
            "Id": null,
            "Status": "CURRENT",
            "total": "$101"
        }
    ]
}

 

Object Reference Flexibility (Starting with 7.0)

Reference and parent fields are typically accessed using either the ID or reference. For example, the Activity is a child object to Invoice. The invoice Id value then can be referenced directly via the InvoiceId field, or by specifically accessing the Id field from within the parent object such as InvoiceIdObj.Id. This means that the data returned in the responses will be the same, but slightly formatted differently:

select InvoiceId, ProductIdObj.Name
from ACTIVITY
where InvoiceIdObj.GrandTotalAmount > 1000 AND rownum <= 5

VS

select InvoiceIdObj.Id, ProductIdObj.Name
from ACTIVITY
where InvoiceIdObj.GrandTotalAmount > 1000 AND rownum <= 5

Sample response of the first query (JSON):

{"queryResponse": [
      {
      "Id": "12902541",
      "InvoiceId": "617226",
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902547",
      "InvoiceId": "617226",
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902562",
      "InvoiceId": "617226",
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902582",
      "InvoiceId": "617226",
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902583",
      "InvoiceId": "617226",
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   }
]}

Sample resonse of the second query (JSON):

{"queryResponse": [
      {
      "Id": "12902541",
      "InvoiceIdObj": {"Id": "617226"},
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902547",
      "InvoiceIdObj": {"Id": "617226"},
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902562",
      "InvoiceIdObj": {"Id": "617226"},
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902582",
      "InvoiceIdObj": {"Id": "617226"},
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   },
      {
      "Id": "12902583",
      "InvoiceIdObj": {"Id": "617226"},
      "ProductIdObj":       {
         "Id": "12971",
         "Name": "CL Test Sub 4920 NoProrate"
      }
   }
]}

This will allow for more flexibility on the field reference notations to be used.

Have more questions? Submit a request

Comments

Powered by Zendesk