Supported SQL Syntax
The following provides an overview and examples of the different SQL syntax elements that are supported by DA SQL. The goal is to expand DA SQL, over time, to provide full SQL-921 compliance within SELECT statements.
Simple cases (Select from single table):
- SQL statements with and without table aliases like:
-- with alias
SELECT cust.Id, cust.Name, cust.Birthdate FROM dasql_Customers cust
-- with alias declared via AS
SELECT cust.Id, cust.Name, cust.Birthdate FROM dasql_Customers as cust
-- with and without alias (if alias is not specified, field name must be unique across tables)
SELECT cust.Id, Name, cust.Birthdate FROM dasql_Customers as cust
-- without alias
SELECT Id, Name, Birthdate FROM dasql_Customers
- SQL statements where all fields requested (using asterix)
SELECT * FROM dasql_Customers
- SQL statement to DA structures whose name differs from DB structures (using mapping)
SELECT CustomerID, CustomerName, CustomerBirthdate FROM dasql_Customers_MappingTest
- SQL statements on tables based on SQL and NonSQL statements
With WHERE clause
- SQL statements with
WHERE
statement (no splitting expected)
SELECT cust.Id, cust.Name, cust.Birthdate FROM dasql_Customers cust
WHERE --condition
Supported Operators:
-- comparisons, including =, < <=, >, >= and <>
SELECT * FROM customers AS c
WHERE c.id = 2
-- boolean logic, including AND, OR
SELECT * FROM customers AS c
WHERE (c.id = 2) or (c.id = 4) or (c.id = 3)
-- using "IN (list)"
SELECT * FROM customers AS c WHERE c.cid IN ('ALFKI', 'PARIS', 'FISSA', 'SPLIR')
-- using IS NULL and IS NOT NULL conditions
SELECT * FROM employees WHERE empaddress IS NOT NULL
-- using Unary conditions
SELECT * FROM employees WHERE NOT (Id = 5)
Joins (select from several tables)
- All kind of joins (Merged by
SortMergeJoin
only)
SELECT cust.Name, ord.Summ FROM dasql_Customers cust
INNER JOIN dasql_Orders ord on cust.Id = ord.CustID;
SELECT cust.Name, ord.Summ FROM dasql_Customers cust
LEFT OUTER JOIN dasql_Orders ord on cust.Id = ord.CustID;
SELECT cust.Name, ord.Summ FROM dasql_Customers cust
RIGHT OUTER JOIN dasql_Orders ord on cust.Id = ord.CustID;
- Inner join with condition inside a
WHERE
clause
SELECT cust.Name, ord.Summ FROM dasql_Customers cust, dasql_Orders ord
WHERE cust.Id = ord.CustID
- Join condition that based on fields with other than integer field type (
m.ID
andd.MasterID
are string fields)
SELECT m.Name, d.val FROM dasql_master1 m, dasql_details1 d
WHERE m.ID = d.MasterID AND val > 10;
- Joining more than 2 tables
SELECT c.cid, o.id, createdate, d.itemid, d.price, d.qty
FROM customers c
INNER JOIN orders o ON c.cid = o.customer
INNER JOIN [Order Details] d ON o.id = d.orderid
WHERE c.cid = 'ALFKI'
OR c.cid = 'PARIS'
OR c.cid = 'FISSA'
OR c.cid = 'SPLIR'
Ordering (Order By)
- Ordering of the result set via
ORDER BY
clause
SELECT * FROM customers ORDER BY CompanyName
SELECT * FROM customers ORDER BY CID DESC
SELECT * FROM customers ORDER BY ContactPerson ASC
SELECT customerid, firstname, lastname FROM customers ORDER BY 3 DESC
SELECT OrderID, ItemID, Price, Qty, Discount FROM [Order Details] ORDER BY OrderID, Price DESC
SELECT c.cid, o.id, o.createdate, freight
FROM Customers c
INNER JOIN orders o ON c.cid = o.customer
WHERE c.cid = 'ALFKI'
ORDER BY o.createdate, freight DESC
Using parameters
- Sql with parameters
SELECT c.cid, o.id, o.createdate, freight
FROM customers AS c
WHERE c.cid = :pCustomerID
ORDER BY o.createdate, freight DESC
Supported statements
TOP
SKIP
UNION
/UNION ALL
CASE WHEN {condition 1} THEN {result 1} WHEN {condition 2} THEN {result 2}... ELSE {result n} END
- Subqueries
Supported SQL functions
DA SQL supports a subset of functions defined in the SQL-92 standard, such as
-
DATEPART
Returns an integer representing the specified datepart of the provided date.
-
Syntax:
DATEPART(date, interval_type)
-
Example:
SELECT * FROM Orders o WHERE DatePart(o.OrderDate, year) >= 2018
-
Parameters:
- date - a valid date
- interval_type - sec, min, hour, day, week, month, year
-
Syntax:
-
LENGTH
Returns length of the provided string value.
-
Syntax:
LENGTH(value)
-
Example:
SELECT * FROM Table WHERE LENGTH(e.LastName) = 3
-
Parameters:
- value - string value or field name
-
Syntax:
-
LOWER
/UPPER
Returns a string converted to lowercase/uppercase accordingly.
-
Syntax:
LOWER(value) UPPER(value)
-
Example:
SELECT * FROM Table WHERE LOWER(e.LastName) = 'alex' SELECT * FROM Table WHERE UPPER(e.LastName) = 'ALEX'
-
Parameters:
- value - string value or field name
-
Syntax:
-
SUBSTRING
Returns a part of the provided string.
-
Syntax:
SUBSTRING(value FROM index FOR count)
-
Example:
SELECT * FROM Table WHERE SUBSTRING(e.LastName FROM 2 FOR 4) = 'ALEX'
-
Parameters:
- value - string value or field name
- index - position from where to start copying
- count - number of characters to copy
-
Syntax:
-
TRIM
Returns a string in which all leading and/or trailing whitespace characters are removed.
Following modifiers can be applied to the function call:
BOTH
/none - Remove both leading and trailing whitespace characters.LEADING
- Remove only leading whitespace characters.TRAILING
- Remove only trailing whitespace characters.
-
Syntax:
TRIM(value) TRIM(BOTH value) TRIM(LEADING value) TRIM(TRAILING value)
-
Example:
SELECT * FROM Table WHERE TRIM(BOTH e.LastName) = 'ALEX'
-
Parameters:
- value - string value or field name
-
LTRIM
Returns a string in which all leading whitespace characters are removed.
This is an alias for
TRIM(LEADING value)
-
Syntax:
LTRIM(value)
-
Example:
SELECT * FROM Table WHERE LTRIM(e.LastName) = 'ALEX'
-
Parameters:
- value - string value or field name
-
Syntax:
-
RTRIM
Returns a string in which all trailing whitespace characters are removed.
This is an alias for
TRIM(TRAILING value)
-
Syntax:
RTRIM(value)
-
Example:
SELECT * FROM Table WHERE RTRIM(e.LastName) = 'ALEX'
-
Parameters:
- value - string value or field name
-
Syntax:
Notes
-
This link is the Wikipedia page covering the standard. Visit The SQL-92 standard to see the standard. ↩