Using SuiteQL Queries in Skills

A user skill can include a saved SuiteQL query when you want Ask Oracle to retrieve specific NetSuite data in a consistent way. The query can read data but can't change records. It runs with the permissions of the person using the skill, so results depend on the records and fields that person can access.

To include a SuiteQL query, save the query as an SQL file in the skill's scripts folder. In the SKILL.md file, explain when Ask Oracle should use the query. Ask Oracle supplies the values defined for the query, but it doesn't change the saved query.

To include a SuiteQL query when creating a skill, upload a ZIP file on the Skills page. When creating a skill from Ask Oracle, you can provide a SKILL.md file but not an SQL file. To learn how to create a skill by uploading a ZIP file, see Creating a Skill from a Skill File or ZIP File.

Before you begin, verify that your role has the permissions needed to create a user skill. For more information, see Required Permissions. Don't include passwords, personal information, or one-time account values in the files you save with the skill.

To add a SuiteQL query to a new skill:

Create a SKILL.md file with the skill's name, description, and instructions. In the instructions, tell Ask Oracle when to use the query and give its path. For example:

                ---
name: Customer transaction count
description: Counts transactions for a customer.
---

When a user asks how many transactions a customer has, use the query in
scripts/customer-transaction-count.sql with the customer's internal ID.
Present the count in the answer.

Create a folder named scripts at the same level as the SKILL.md file. In that folder, create an SQL file containing a read-only query. Start the file with a metadata comment that describes the query and any values it needs. For example, save the following file as customer-transaction-count.sql:

                /* @suiteql-metadata
version: 1
description: Counts transactions linked to a customer.
inputs:
  - name: customerId
    type: NUMBER
    description: Internal ID of the customer to check.
*/
SELECT COUNT(*) AS transactionCount
FROM Transaction t
WHERE t.entity = :customerId

Put the SKILL.md file and the scripts folder together in a ZIP file. Upload the ZIP file from the Skills page, review the skill details, and save the skill.
Apply the saved skill in Ask Oracle and ask a question that uses the query. Test it with a customer internal ID that your role can access, then compare the result with the records in NetSuite.

SuiteQL Query File Requirements

Every SQL file used by a skill must begin with a @suiteql-metadata comment. Include version: 1 and a short description of what the query returns. If the query needs values, list each one under inputs. The input name must match the name after the colon in the query, such as customerId and :customerId in the preceding example.

For each input, include its name, type, and a description that explains what value to use. Choose one of these types:

NUMBER for a whole or decimal number, such as an internal ID or amount.
STRING for text or a date. If the input is a date, state its expected format in the description.
BOOLEAN for a true or false value.

List every value that the query expects. If the query needs no values, you can omit inputs. For queries that might return many rows, set a useful limit in the query, such as FETCH FIRST 100 ROWS ONLY. Ask Oracle may also limit large results.

If the query doesn't run, check that the SQL file is in the skill's scripts folder, the metadata comment is first, and each input matches its query placeholder. Also verify that the person using the skill has access to the data requested by the query.

To add a SuiteQL query to a new skill:

SuiteQL Query File Requirements

Related Topics