Tag: SQL

List of SQL Functions

NetSuite offers a comprehensive suite of SQL Functions, catering to various data manipulation and analysis needs. Here’s a list of some of the common functions used: Note: The links to the function explanations will be updated as new articles about SQL Functions are posted. Numeric Functions ABS ROUND FLOOR CEIL MOD REMAINDER MOD vs. REMAINDER Character… Continue reading List of SQL Functions

Joining Employees and Vendors in Purchase Order Using SuiteQL

When working with SuiteQL to analyze Purchase Orders, you may want to include additional information about related Entities (e.g., Vendors) and Employees (e.g., Sales Reps). However, not all Purchase Orders are associated with an Employee, so it’s important to structure your joins correctly. This guide demonstrates how to build a SuiteQL query that pulls Purchase Orders along with: The Entity (Vendor) information — always required. The Employee information — if available. INNER JOIN vs LEFT… Continue reading Joining Employees and Vendors in Purchase Order Using SuiteQL

Sample SQL QUERY to fetch Custom Price levels from a customer record

Use this query to fetch the custom price levels from a customer record: QUERY: SELECT   internalid,   customername,   customeremail,   itemID,   displayname,   currency,   unitprice, FROM (     SELECT       item.id AS internalid,       c.altname AS customername,       c.email AS customeremail,      … Continue reading Sample SQL QUERY to fetch Custom Price levels from a customer record

Display Values from the Transaction Record Using SuiteQL BUILTIN.DF

When working with the Transaction record in SuiteQL, some fields return internal values that are not easily understood at a glance. For example: The Status field may return a single-letter code. Fields like Entity, Employee, and LastModifiedBy return internal IDs. The Type field displays abbreviations like SalesOrd or PurchOrd. These internal values are meaningful to the system but not very useful to end users or admins reviewing query results. To… Continue reading Display Values from the Transaction Record Using SuiteQL BUILTIN.DF

SQL query example that pulls Revenue data and the necessary fields from a BigQuery table containing NetSuite data

The following is an SQL Query that pulls data needed to generate Revenue data. The data is fetched from a BigQuery table that is updated from NetSuite via the FiveTran Connector. SELECT     t.id AS transaction_id,     t.tranid AS transaction_number,     MAX(t.trandate) AS transaction_date,     MIN(c.id) AS customer_id,     MAX(c.externalid) AS customer_sfid,     MAX(c.entityid) AS customer_name,     MAX(s.id) AS subsidiary_id,     MAX(s.name) AS… Continue reading SQL query example that pulls Revenue data and the necessary fields from a BigQuery table containing NetSuite data

Sample query to fetch custom price levels in customer record

SELECT netprice, internalid, base_price     FROM (         SELECT             cip.price AS netprice,             cip.item AS internalid,             ip.price AS base_price,             ROW_NUMBER() OVER (PARTITION BY cip.item ORDER BY ip.priceLevel ASC)… Continue reading Sample query to fetch custom price levels in customer record

SuiteQL Access Issue for Custom Record – Troubleshooting and Solution

Issue Summary: A user is unable to access the custom record customrecord_jj_intercompany_invoice via SuiteQL API, despite having the necessary permissions granted in their role. The user can access the record through the NetSuite UI, but SuiteQL returns no results. This article explores the potential causes and the solution for this issue. Root Cause Analysis: Upon… Continue reading SuiteQL Access Issue for Custom Record – Troubleshooting and Solution

Retrieve Vendor Transactions with Billing Address Using SuiteQL

Efficient data retrieval and analysis are essential for managing financial transactions in any business. Accessing detailed transaction records, including vendor information and billing addresses, helps organizations streamline reporting, improve decision-making, and enhance financial tracking. By leveraging structured queries, users can extract relevant data, integrate it with external systems, and generate comprehensive reports tailored to business… Continue reading Retrieve Vendor Transactions with Billing Address Using SuiteQL

Sales Orders Age Calculation Using SuiteQL

When working with NetSuite, it’s essential to track the age of transactions, especially Sales Orders. Calculating the age, in days, of Sales Orders allows businesses to monitor how long these orders have been open and take appropriate actions if necessary. Using SuiteQL, you can efficiently retrieve this data with a simple query. Below is the SQL query you can use:… Continue reading Sales Orders Age Calculation Using SuiteQL