SELECT Statements
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
SELECT Syntax
The following syntax diagram outlines the syntax supported by the NetSuite adapter:
SELECT {
[ TOP <numeric_literal> ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[
JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | != | < | > | >= | <= | LIKE | IN | NOT IN | AND | OR | IS NULL | IS NOT NULL } [ <expression> ]
} [ { AND | OR } ... ]
Examples
Return all columns:
SELECT * FROM Account
Rename a column:
SELECT "AcctName" AS MY_AcctName FROM Account
Search data:
SELECT * FROM Account WHERE acctName = 'Checking';
The NetSuite APIs support the following operators in the WHERE clause: =, !=, <, >, >=, <=, LIKE, IN, NOT IN, AND, OR, IS NULL, IS NOT NULL.
SELECT * FROM Account WHERE acctName = 'Checking';
Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM Account
Retrieve data from multiple tables.
SELECT n.Note AS MyNote, c.InternalId AS CustomerId FROM Customer, Note
See
JOIN Queries for details.
JOIN Queries
This section discusses some of the features and restrictions that are specific to how the adapter supports joins. If possible the NetSuite Adapter will attempt to perform joins directly in NetSuite. If NetSuite cannot handle the join, it will instead be performed client side when SupportEnhancedSQL is set to true.
The NetSuite supports join queries on tables that have a relationship defined in NetSuite. Only left outer joins are supported directly by NetSuite.
When you use the adapter to join tables with SQL, this is the same as creating a Saved Search in the NetSuite UI and then selecting the option to get back data from other related tables.
The following query retrieves all Notes associated with each Customer:
SELECT n.Note AS MyNote, c.InternalId AS CustomerId
FROM Customer c
LEFT OUTER JOIN Note n
ON n.Entity_InternalId = c.InternalId
Joins of more than two tables are supported. The following query retrieves Customer and BillingSchedule information about each SalesOrder:
SELECT Customer.AccountNumber AS CustomerAcctNumber, BillingSchedule.InitialAmount AS InitialAmount, SalesOrder.InternalId AS SalesOrderId
FROM SalesOrder
LEFT OUTER JOIN Customer ON SalesOrder.Entity_InternalId = Customer.InternalId
LEFT OUTER JOIN BillingSchedule ON SalesOrder.BillingSchedule_InternalId=BillingSchedule.InternalId
Due to limitations in the NetSuite API, joins are not supported for the following tables:
Child tables
Custom record tables
Tables listed in the "Simple Tables" section in
Tables