Published on May 21, 2020.

One of the features of NetSuite's SuiteTalk REST Web Services that I've found to be particularly interesting is its support for executing SuiteQL queries. SuiteQL is NetSuite's implementation of the SQL-92 standard, and it makes it possible to query against an instance's backend Oracle database.

I had experimented with the SuiteQL functionality during the SuiteTalk REST beta period. However, it wasn't until this week that I had a practical need to use it. A client of mine needed a way to provide purchasing information to an external system. Specifically, they needed to be able to provide a Web API that the external system could use to pull a list of items that should be considered for purchasing. That list would include items whose on-hand quantity was at or below its reorder point.

I thought I'd share a little about how I fulfilled this request.

The SQL Query

Let's start with the SQL query itself. Here's the query, formatted for readability.

SELECT 
	Item.id,
	Item.itemid,
	Item.purchasedescription,
	Item.quantityonhand,
	Item.reorderpoint,
	( Item.reorderpoint - Item.quantityonhand ) AS quantitytoorder,
	Item.cost,
	Item.leadtime,
	ItemVendor.vendor,
	Vendor.companyName AS VendorName 
FROM 
	Item 
	INNER JOIN ItemVendor ON 
		( 
			( ItemVendor.item = Item.id ) 
			AND ( ItemVendor.preferredvendor = 'T' ) 
		)
	INNER JOIN Vendor ON 
		( Vendor.id = ItemVendor.vendor ) 
WHERE 
	( reorderpoint > 0 ) 
	AND ( quantityonhand <= reorderpoint ) 
ORDER BY 
	Vendor.companyName ASC,
	Item.itemid ASC

The list of columns being requested is straightforward. Notice that the "quantitytoorder" value is a simple mathematical calculation, and that I've aliased the vendor's company name to make it less ambiguous in the response.

Things get a little interesting in the FROM clause. I'm joining an item to it's preferred vendor, and doing so via the ItemVendor join table. From there, I'm joining to the actual Vendor table so that I can provide the vendor's name.

The filters specified in the WHERE clause are also straightforward. I'm specifying that I only want items that have a reorder point, and where the quantity on hand is at or below that point.

And finally, I'm using the ORDER BY clause to specify the order that I want the items to be returned in. I'm specifying that I want the values to be sorted in ascending order. In most of the systems that I've worked with, the sort direction is implied. However, I've found that with SuiteQL, you nearly always need to specify the direction.

The SuiteTalk REST API Call

Now let's take a look at an API call that actually uses the query. This is an example call made with cURL.

curl -X "POST" "https://***YOUR-NS-ACCOUNT-ID***.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql/?limit=3&offset=0" \
     -H 'Authorization: OAuth oauth_consumer_key="***YOUR-CONSUMER-KEY***", oauth_nonce="***YOUR-NONCE***", oauth_signature="1bb31e23-4e97-40ba-8ef7-1ca20101fdfb", oauth_signature_method="HMAC-SHA1", oauth_timestamp="***YOUR-TIMESTAMP***", oauth_token="***YOUR-TOKEN***", oauth_version="1.0", realm=***YOUR-NS-ACCOUNT-ID***' \
     -H 'Prefer: transient' \
     -H 'Content-Type: text/plain; charset=utf-8' \
     -H 'Cookie: NS_ROUTING_VERSION=LAGGING' \
     -d $'{
"q": "SELECT Item.id, Item.itemid, Item.purchasedescription, Item.quantityonhand, Item.reorderpoint, ( Item.reorderpoint - Item.quantityonhand ) AS quantitytoorder, Item.cost, Item.leadtime, ItemVendor.vendor, Vendor.companyName AS VendorName FROM Item INNER JOIN ItemVendor ON ( ( ItemVendor.item = Item.id ) AND ( ItemVendor.preferredvendor = '"'"'T'"'"' ) ) INNER JOIN Vendor ON ( Vendor.id = ItemVendor.vendor ) WHERE ( reorderpoint > 0 ) AND ( quantityonhand <= reorderpoint ) ORDER BY Vendor.companyName ASC, Item.itemid ASC"
}'

Note that in the example above, I've replaced the account number, tokens, nonce, signature, etc with placeholder values. Be sure to replace those with your own values.

Also note that I've removed all extraneous whitespace from the query. SuiteQL will reject queries that contain tabs, returns, and so on.

And finally, note that for testing purposes I'm limiting the response to 3 results (by specifying a "limit" in the URL). I'm also specifying an offset of zero. You can adjust those values to implement pagination.

The API Response

Here's a sample response.

{
  "links": [
    {
      "rel": "next",
      "href": "https://9999999.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=3&offset=3"
    },
    {
      "rel": "last",
      "href": "https://9999999.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=3&offset=285"
    },
    {
      "rel": "self",
      "href": "https://9999999.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=3&offset=0"
    }
  ],
  "count": 3,
  "hasMore": true,
  "items": [
    {
      "links": [],
      "cost": "11.38",
      "id": "27146",
      "itemid": "B024LB510",
      "leadtime": "1",
      "purchasedescription": "Bay View Giant Zippered Boat Tote - Black",
      "quantityonhand": "-19",
      "quantitytoorder": "40",
      "reorderpoint": "21",
      "vendor": "1888",
      "vendorname": "ALPHA"
    },
    {
      "links": [],
      "cost": "7.78",
      "id": "27150",
      "itemid": "WBA-JG15BK",
      "leadtime": "3",
      "purchasedescription": "Jumbo Globetrotter Drawstring Bag - Black",
      "quantityonhand": "-10",
      "quantitytoorder": "60",
      "reorderpoint": "50",
      "vendor": "2883",
      "vendorname": "ARIEL"
    },
    {
      "links": [],
      "cost": "27.49",
      "id": "40693",
      "itemid": "EL139-99-00",
      "leadtime": "3",
      "purchasedescription": "UL Certified Sprint Digital Power Bank - 4000 mAh - Graphite",
      "quantityonhand": "0",
      "quantitytoorder": "120",
      "reorderpoint": "120",
      "vendor": "3027",
      "vendorname": "STARLINE"
    }
  ],
  "offset": 0,
  "totalResults": 275
}

Notice that the response includes hasMore, totalResults, and links values. These are convenient in cases where you want to paginate the results.

Also notice that the values of the individual records (referred to as "items" in the response) aren't in the order that was specified in the query. Instead, they're returned in alphabetical order. That shouldn't matter too much, but it is worth noting, especially if you are expecting the values to be returned in a specific order for some reason.

Wrapping Up

The query and API call that I shared above are just a few of the components that make up the full solution that I developed for my client. In front of those components is a PHP-based middleware layer, which handles authentication from the external system, and essentially serves to shield the NetSuite instance from the external system. And whenever possible, I run all of my PHP-based solutions behind nginx, so that I can implement load balancing, rate limiting, and so on.

At this point in my career, I've probably written more using SQL than I have using English. So for me, the ability to run queries directly against NetSuite's Oracle backend is exciting. I've found that this new functionality is easy to use, fast, and reliable. I'm looking forward to using it more in the future.

If you have any questions about this post, or about SuiteTalk REST Web Services / SuiteQL in general, please feel free to contact me.