Exporting Dataset Definitions to SuiteQL

You can export your saved dataset definitions to a SuiteQL TXT file. This allows you to understand how fields are joined in your dataset so that you can build your SuiteQL queries. SuiteQL queries are available through the SuiteScript Analytic API, SuiteAnalytics Connect, and SuiteTalk REST Web Services.

To export your dataset definition to a SuiteQL TXT file, open your dataset, click Export and choose the Export dataset to SuiteQL TXT option. For more information, see Defining a Dataset.

When you export your dataset definitions, you must consider the following:

• You can only export dataset definitions that have been saved. Unsaved datasets do not display the export option.

• Dataset exports may include internal records or fields that are not exposed to all channels. To review which records and fields are exposed, check them in the Records Catalog. For information about the Records Catalog, see Records Catalog Overview.

• Some dataset exports may not be complete and you may need to include criteria values manually. For example, in the following query you must replace "?" with the corresponding value:

                AND TransactionAccountingLine.posting = ? 
  
              

• All dataset exports include BUILTIN functions in SELECT clauses. BUILTIN functions are not supported so you must remove them. The following example shows how the BUILTIN function should be removed in this case.

  • Query example with the BUILTIN function:

                      BUILTIN_RESULT.TYPE_DATE("TRANSACTION".trandate) AS trandate /*{trandate#RAW}*/, 
    
                    

  • Query example without the BUILTIN function:

                      "TRANSACTION".trandate AS trandate, 
    
                    

  For more information about BUILTIN functions, see SuiteQL Supported Built-in Functions.

• When using SuiteAnalytics Connect, you cannot use right outer joins. For example, the following Oracle SQL is not valid in SuiteQL:

                select a1.id from account a1, account a2 where a1.id (+) = a2.id 
  
              

  For information about syntax requirements for SuiteAnalytics Connect, see SuiteQL Performance and Best Practices for Connect.

General Notices