# NetSuiteSQL 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 SQL engine of the provider:
`SELECT` `{` `[ TOP` ` | DISTINCT` `]` `{` `*` `| {` ` [ [ AS` `] ]` `| { | } .*` `} [ , ... ]` `}` `[ INTO` `csv:// [ filename= ] [ ;delimiter=tab ] ]` `{` `FROM` ` [ [ AS` `] ]` `} [ , ... ]` `[ [` `INNER` `| { { LEFT` `| RIGHT` `| FULL` `} [ OUTER` `] }` `] JOIN` ` [ ON` ` ] [ [ AS` `] ]` `] [ ... ]` `[ WHERE` ` ]` `[ GROUP` `BY` ` [ , ... ]` `[ HAVING` ` ]` `[ UNION` `[ ALL` `] ]` `[` `ORDER` `BY` ` [ ASC` `| DESC` `] [ NULLS FIRST` `| NULLS LAST` `]` `]` `[` `LIMIT ` `[` `{ OFFSET | , }` `` `]` `]` `} | SCOPE_IDENTITY()` ` ::=` `| ` `| @ ` `| ?` `| COUNT( * | { [ DISTINCT` `] } )` `| { AVG` `| MAX` `| MIN` `| SUM` `| COUNT` `} ( )` `| NULLIF` `( , )` `| COALESCE` `( , ... )` `| CASE` `` `WHEN` `{ | } THEN` `{ | NULL` `} [ ... ]` `[ ELSE` `{ | NULL` `} ]` `END` `| ` `| ` ` ::=` `{` ` { = | > | < | >= | <= | <> | != | LIKE` `| NOT` `LIKE` `| IN` `| NOT` `IN` `| IS` `NULL` `| IS` `NOT` `NULL` `| AND` `| OR` `| CONTAINS` `| BETWEEN` `} [ ]` `} [ { AND` `| OR` `} ... ]` |

| | --------------- | #### Examples 1. Return all columns:
| `SELECT * FROM Account` | | ----------------------- | 2. Rename a column:
| `SELECT [AcctName] AS MY_AcctName FROM Account` | | ----------------------------------------------- | 3. Cast a column's data as a different data type:
| `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Account` | | ------------------------------------------------------------------------- | 4. Search data:
| `SELECT * FROM Account WHERE acctName = 'Checking'` | | --------------------------------------------------- | 5. Return the number of items matching the query criteria:
| `SELECT COUNT(*) AS MyCount FROM Account` | | ----------------------------------------- | 6. Return the number of unique items matching the query criteria:
| `SELECT COUNT(DISTINCT AcctName) FROM Account` | | ---------------------------------------------- | 7. Return the unique items matching the query criteria:
| `SELECT DISTINCT AcctName FROM Account` | | --------------------------------------- | 8. Summarize data:
| `SELECT AcctName, MAX(AnnualRevenue) FROM Account GROUP BY AcctName` | | -------------------------------------------------------------------- | See Aggregate Functions below for details. 9. Retrieve data from multiple tables.
| `SELECT n.Note, c.InternalId FROM Note n, Customer c WHERE n.Entity_InternalId = c.InternalId` | | ---------------------------------------------------------------------------------------------- | See JOIN Queries below for details. 10. Sort a result set in ascending order:
| `SELECT InternalId, AcctName FROM Account ORDER BY AcctName ASC` | | ----------------------------------------------------------------- | 11. Restrict a result set to the specified number of rows:
| `SELECT InternalId, AcctName FROM Account LIMIT 10` | | --------------------------------------------------- | 12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
| `SELECT * FROM Account WHERE acctName = @param` | | ----------------------------------------------- | ### Aggregate Functions #### COUNT Returns the number of rows matching the query criteria.
| `SELECT COUNT(*) FROM Account WHERE acctName = 'Checking'` | | ---------------------------------------------------------- | #### COUNT(DISTINCT) Returns the number of distinct, non-null field values matching the query criteria.
| `SELECT COUNT(DISTINCT InternalId) AS DistinctValues FROM Account WHERE acctName = 'Checking'` | | ---------------------------------------------------------------------------------------------- | #### AVG Returns the average of the column values.
| `SELECT AcctName, AVG(AnnualRevenue) FROM Account WHERE acctName = 'Checking'` `GROUP BY AcctName` | | --------------------------------------------------------------------------------------------------- | #### MIN Returns the minimum column value.
| `SELECT MIN(AnnualRevenue), AcctName FROM Account WHERE acctName = 'Checking'` `GROUP BY AcctName` | | -------------------------------------------------------------------------------------------------- | #### MAX Returns the maximum column value.
| `SELECT AcctName, MAX(AnnualRevenue) FROM Account WHERE acctName = 'Checking'` `GROUP BY AcctName` | | -------------------------------------------------------------------------------------------------- | #### SUM Returns the total sum of the column values.
| `SELECT SUM(AnnualRevenue) FROM Account WHERE acctName = 'Checking'` | | -------------------------------------------------------------------- | ### JOIN Queries The CData ADO.NET Provider for NetSuite supports standard SQL joins like the following examples. #### Inner Join An inner join selects only rows from both tables that match the join condition:
| `SELECT n.Note, c.InternalId FROM Note n, Customer c WHERE n.Entity_InternalId = c.InternalId` | | ---------------------------------------------------------------------------------------------- | #### Left Join A left join selects all rows in the FROM table and only matching rows in the JOIN table:
| `SELECT n.Note, c.InternalId FROM Customer c LEFT JOIN Note n ON n.Entity_InternalId = c.InternalId` | | ---------------------------------------------------------------------------------------------------- | ### Date Literal Functions The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not. #### L\_TODAY() The current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` | | ----------------------------------------------------- | #### L\_YESTERDAY() The previous day.
| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` | | --------------------------------------------------------- | #### L\_TOMORROW() The following day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` | | -------------------------------------------------------- | #### L\_LAST\_WEEK() Every day in the preceding week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` | | --------------------------------------------------------- | #### L\_THIS\_WEEK() Every day in the current week.
| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` | | --------------------------------------------------------- | #### L\_NEXT\_WEEK() Every day in the following week.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()` | | --------------------------------------------------------- | Also available: * L\_LAST/L\_THIS/L\_NEXT MONTH * L\_LAST/L\_THIS/L\_NEXT QUARTER * L\_LAST/L\_THIS/L\_NEXT YEAR #### L\_LAST\_N\_DAYS(n) The previous n days, excluding the current day.