Exporting SuiteAnalytics Dataset as SQL

The data structure of related fields and records in NetSuite is not always intuitive and can be very difficult to access easily. NetSuite saved searches are a great way to identify field relationships and joins. Still, they have their limitations as joins are prevented past the second level, and field IDs and record types are not always representative of the true values at the database level.

Understanding NetSuite Data Structure and Field Relationships

Datasets exported as SQL queries help identify complex joins, record relationships, and can improve script performance dramatically. Utilizing the ‘Export as Script’ feature within a dataset produces a SQL query that can provide developers with the context they need for complex scripts and reduce load time for the records those scripts run on.

Tool for Exporting NetSuite Datasets as SQL Queries

Download the free Saved Search and Dataset Chrome Extension to allow exports from saved searches and datasets. This extension is required for exporting datasets as SQL.

How to Use the Export as Script Feature in NetSuite

Once a dataset is created and saved, it can be exported as a SQL query using the ‘Export as Script’ button in the top right-hand corner of the screen:

The pop-up window output will contain the query used to produce the dataset in both standard SQL format and SuiteQL format for use in scripting. The N/query module can be used within SuiteScript to load querying functionality, and the SuiteQL dataset export can be inserted in scripts for easy data access:

Benefits of SQL Over Saved Searches in NetSuite

The export is helpful for identifying joins and record types and can be a much easier and more robust way to communicate with developers when writing script requirements. There are several benefits to using SQL over saved searches or other querying methods:

1. SQL grants access to the entire NetSuite data structure and exposes relationships and joins that saved searches do not.
2. SQL allows multi-layered joins in a single query, where the number of joins limits the number of saved searches.
3. The syntax for the SQL query module in JavaScript is often simpler than that of the search creation module.
4. SQL operates much quicker both within client-side and user event scripts due to the direct access to NetSuite’s database.

For example, the export is especially useful for identifying subrecord IDs and the joins used to access their fields. See the query below, which can be utilized to join the transaction record and the shipping address subrecord:

SELECT
 BUILTIN_RESULT.TYPE_DATE(TRANSACTION.trandate) AS trandate,
 BUILTIN_RESULT.TYPE_STRING(TRANSACTION.tranid) AS tranid,
 BUILTIN_RESULT.TYPE_STRING(TRANSACTION.trandisplayname) AS trandisplayname,
 BUILTIN_RESULT.TYPE_DATE(transactionShippingAddress.custrecord_rup_license_expir) AS custrecord_rup_license_expir
FROM
 TRANSACTION,
 transactionShippingAddress
WHERE
 TRANSACTION.shippingaddress = transactionShippingAddress.nkey(+)
  AND TRANSACTION.TYPE IN ('SalesOrd')

Related Articles: 

• How to Set Up the SuiteQL Query Tool | NetSuite
• Service Pro by MSI Data

Got stuck on a step in this article?

We like to update our blogs and articles to make sure they help resolve any troubleshooting difficulties you are having. Sometimes, there is a related feature to enable or a field to fill out that we miss during the instructions. If this article didn't resolve the issue, please use the chat and let us know so that we can update this article!

Oracle NetSuite Alliance Partner & Commerce Partner

If you have general questions about SuiteCommerce or more specific questions about how our team can support your business as you implement NetSuite or SuiteCommerce, feel free to contact us anytime. Anchor Group is a certified Oracle NetSuite Alliance Partner and Commerce Partner equipped to handle all kinds of NetSuite and SuiteCommerce projects, large or small!