# Paging Through RETRIEVE Data Using ROWNUM

BillingPlatform can return very large datasets when performing **RETRIEVE** operations through the web service.
In some cases, the results need to be broken into smaller chunks so the client system can handle them.

The **ROWNUM** operator is used as part of the ANSI SQL query passed to the RETRIEVE function to achieve this.

## Procedure

### Standalone Operation

The `ROWNUM` operator can be used on its own without filters.
The range defined in the `BETWEEN` expression can be adjusted (e.g., `0 AND 20`, `21 AND 40`, etc.).

#### Sample SOAP Request


```xml
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:stronglytyped.soap.brmsystems.com"> 
    <soapenv:Header> 
        <urn:SessionHeader>
             <urn:SessionID>qiAJMdPbCHOQIKumDPgiaEYADNgRsEtMltmQEUmq</urn:SessionID> 
        </urn:SessionHeader>
     </soapenv:Header> 
    <soapenv:Body>
         <urn:retrieve>
             <urn:queryAnsiSql>ROWNUM BETWEEN 0 AND 20</urn:queryAnsiSql>
             <urn:brmObjectType>PRICING</urn:brmObjectType>
         </urn:retrieve> 
    </soapenv:Body>
</soapenv:Envelope>
```

#### Sample SOAP response


```xml
<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:retrieveResponse>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259268</urn:Id>
                <urn:Created>2014-10-29T20:00:06.000Z</urn:Created>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:PackageProductId>318</urn:PackageProductId>
                <urn:Rate>.1</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259269</urn:Id>
                <urn:Created>2014-10-29T20:00:06.000Z</urn:Created>
                <urn:CurrencyCode>CAN</urn:CurrencyCode>
                <urn:PackageProductId>318</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259270</urn:Id>
                <urn:Created>2014-10-29T20:00:06.000Z</urn:Created>
                <urn:CurrencyCode>EUR</urn:CurrencyCode>
                <urn:PackageProductId>318</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259271</urn:Id>
                <urn:Created>2014-10-29T20:00:06.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>318</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259272</urn:Id>
                <urn:Created>2014-10-29T20:11:47.000Z</urn:Created>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:PackageProductId>319</urn:PackageProductId>
                <urn:Rate>.9</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259273</urn:Id>
                <urn:Created>2014-10-29T20:11:47.000Z</urn:Created>
                <urn:CurrencyCode>CAN</urn:CurrencyCode>
                <urn:PackageProductId>319</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259274</urn:Id>
                <urn:Created>2014-10-29T20:11:47.000Z</urn:Created>
                <urn:CurrencyCode>EUR</urn:CurrencyCode>
                <urn:PackageProductId>319</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259275</urn:Id>
                <urn:Created>2014-10-29T20:11:47.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>319</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259308</urn:Id>
                <urn:Created>2014-10-30T16:43:54.000Z</urn:Created>
                <urn:ContractRateId>26</urn:ContractRateId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.02</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259308</urn:Id>
                <urn:Created>2014-10-30T16:43:54.000Z</urn:Created>
                <urn:ContractRateId>34</urn:ContractRateId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.02</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259308</urn:Id>
                <urn:Created>2014-10-30T16:43:54.000Z</urn:Created>
                <urn:ContractRateId>35</urn:ContractRateId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.02</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259529</urn:Id>
                <urn:Created>2014-11-01T02:57:24.000Z</urn:Created>
                <urn:ContractRateId>27</urn:ContractRateId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.25</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259529</urn:Id>
                <urn:Created>2014-11-01T02:57:24.000Z</urn:Created>
                <urn:ContractRateId>36</urn:ContractRateId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.25</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>261593</urn:Id>
                <urn:Created>2014-11-07T13:38:57.000Z</urn:Created>
                <urn:ContractRateId>28</urn:ContractRateId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>99</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>261704</urn:Id>
                <urn:Created>2014-11-07T21:52:08.000Z</urn:Created>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:PackageProductId>332</urn:PackageProductId>
                <urn:Rate>.5</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>261705</urn:Id>
                <urn:Created>2014-11-07T21:52:08.000Z</urn:Created>
                <urn:CurrencyCode>CAN</urn:CurrencyCode>
                <urn:PackageProductId>332</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>261706</urn:Id>
                <urn:Created>2014-11-07T21:52:08.000Z</urn:Created>
                <urn:CurrencyCode>EUR</urn:CurrencyCode>
                <urn:PackageProductId>332</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>261707</urn:Id>
                <urn:Created>2014-11-07T21:52:08.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>332</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>262151</urn:Id>
                <urn:Created>2014-11-10T18:58:59.000Z</urn:Created>
                <urn:AccountProductId>2909</urn:AccountProductId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.2</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>262268</urn:Id>
                <urn:Created>2014-11-10T19:02:55.000Z</urn:Created>
                <urn:AccountProductId>1155</urn:AccountProductId>
                <urn:CurrencyCode>USD</urn:CurrencyCode>
                <urn:Rate>.4</urn:Rate>
            </urn:result>
        </urn:retrieveResponse>
    </soapenv:Body>
</soapenv:Envelope>
```

### Combined With Other Expressions

If the query needs to be filtered but still returns a large chunk of data, the ROWNUM operator can be combined with other ANSI SQL expressions such as the example below:

#### Sample SOAP Request


```xml
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:stronglytyped.soap.brmsystems.com">
    <soapenv:Header>
        <urn:SessionHeader>
            <urn:SessionID>qiAJMdPbCHOQIKumDPgiaEYADNgRsEtMltmQEUmq</urn:SessionID>
        </urn:SessionHeader>
    </soapenv:Header>
    <soapenv:Body>
        <urn:retrieve>
            <urn:queryAnsiSql>CurrencyCode = 'GBP' AND ROWNUM BETWEEN 0 AND 20</urn:queryAnsiSql>
            <urn:brmObjectType>PRICING</urn:brmObjectType>
        </urn:retrieve>
    </soapenv:Body>
</soapenv:Envelope>
```

#### Sample SOAP Response


```xml
<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:retrieveResponse>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259271</urn:Id>
                <urn:Created>2014-10-29T20:00:06.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>318</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>259275</urn:Id>
                <urn:Created>2014-10-29T20:11:47.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>319</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>261707</urn:Id>
                <urn:Created>2014-11-07T21:52:08.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>332</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>262777</urn:Id>
                <urn:Created>2014-11-12T12:58:25.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>352</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>268242</urn:Id>
                <urn:Created>2014-11-19T15:28:39.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:Rate>5</urn:Rate>
                <urn:RateClassProductId>98380000000000000000000000002518</urn:RateClassProductId>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>268243</urn:Id>
                <urn:Created>2014-11-19T15:28:39.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:Rate>5</urn:Rate>
                <urn:RateClassProductId>98380000000000000000000000002557</urn:RateClassProductId>
                <urn:UpperBand>2</urn:UpperBand>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>268244</urn:Id>
                <urn:Created>2014-11-19T15:28:39.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:Rate>3</urn:Rate>
                <urn:RateClassProductId>98380000000000000000000000002557</urn:RateClassProductId>
                <urn:UpperBand>-1</urn:UpperBand>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>274986</urn:Id>
                <urn:Created>2014-11-20T20:11:06.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>371</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>274998</urn:Id>
                <urn:Created>2014-11-20T21:30:34.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>372</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>276361</urn:Id>
                <urn:Created>2014-11-21T10:44:12.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>375</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
                <urn:UpperBand>1</urn:UpperBand>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>276364</urn:Id>
                <urn:Created>2014-11-21T10:44:12.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>375</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
                <urn:UpperBand>-1</urn:UpperBand>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>280749</urn:Id>
                <urn:Created>2014-11-24T18:08:33.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>358</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>285421</urn:Id>
                <urn:Created>2014-11-26T19:21:37.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>390</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>291991</urn:Id>
                <urn:Created>2014-11-27T13:37:05.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>394</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>291997</urn:Id>
                <urn:Created>2014-11-27T14:36:28.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>395</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>292389</urn:Id>
                <urn:Created>2014-11-27T17:22:46.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>397</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>296393</urn:Id>
                <urn:Created>2014-11-28T06:23:32.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>398</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>301085</urn:Id>
                <urn:Created>2014-11-28T21:17:08.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>399</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>302429</urn:Id>
                <urn:Created>2014-12-01T04:33:36.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>401</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
            </urn:result>
            <urn:result xsi:type="urn:PRICING">
                <urn:Id>302434</urn:Id>
                <urn:Created>2014-12-01T08:43:25.000Z</urn:Created>
                <urn:CurrencyCode>GBP</urn:CurrencyCode>
                <urn:PackageProductId>404</urn:PackageProductId>
                <urn:Rate>0</urn:Rate>
                <urn:UpperBand>1</urn:UpperBand>
            </urn:result>
        </urn:retrieveResponse>
    </soapenv:Body>
</soapenv:Envelope>
```