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 table alias
SELECT cust.Id, cust.Name, cust.Birthdate FROM Customers cust

SELECT cust.Id, cust.Name, cust.Birthdate FROM Customers as cust

-- With and without table alias (if alias is not specified, field name must be unique across tables)
SELECT cust.Id, Name, cust.Birthdate FROM Customers as cust

-- Without alias
SELECT Id, Name, Birthdate FROM Customers
  • SQL statements where all fields are requested (using asterix)
SELECT * FROM Customers
  • SQL statement to DA structures whose name differs from DB structures (using mapping)
SELECT CustomerID, CustomerName, CustomerBirthdate FROM Customers
  • SQL statements on tables based on SQL and NonSQL statements

With WHERE clause

  • SQL statements with WHERE clause
SELECT cust.Id, cust.Name, cust.Birthdate FROM Customers cust
WHERE Id = 10

Supported Operators:

  • Arithmetic Operators including +, - /, *, %

    SELECT * FROM raw_data AS d
WHERE d.value * d.value < 20

  • Comparisons operators including =, < <=, >, >=, <>

    SELECT * FROM customers AS c
WHERE c.id = 2

  • Boolean logic operators, including AND, OR, NOT

    SELECT * FROM customers AS c
WHERE (c.id = 2) OR (c.id = 4) OR (c.id = 3)

SELECT * FROM employees WHERE NOT (id = 5)

  • IN and NOT IN operators

    SELECT * FROM customers AS c
WHERE c.cid IN ('ALFKI', 'PARIS', 'FISSA', 'SPLIR')

SELECT * FROM customers AS c
WHERE c.cid NOT IN ('ALFKI', 'PARIS', 'FISSA', 'SPLIR')

  • LIKE and NOT LIKE operators

    SELECT * FROM customers AS c
WHERE c.cid LIKE '%AL%'

SELECT * FROM customers AS c
WHERE c.cid NOT LIKE '%AL%'

  • BETWEEN operator

    SELECT * FROM Customers AS c
WHERE c.ID BETWEEN 10 AND 20

  • IS NULL and IS NOT NULL operators

    SELECT * FROM employees
WHERE address IS NOT NULL

Joins (select from several tables)

  • All kind of joins (Merged by SortMergeJoin only)
SELECT cust.Name, ord.Summ FROM Customers cust
   INNER JOIN dasql_Orders ord on cust.Id = ord.CustID;

SELECT cust.Name, ord.Summ FROM Customers cust
   LEFT OUTER JOIN dasql_Orders ord on cust.Id = ord.CustID;

SELECT cust.Name, ord.Summ FROM 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 Customers cust, dasql_Orders ord
   WHERE cust.Id = ord.CustID
  • Join condition that based on fields with other than integer field type (m.ID and d.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
  • IIF({condition 1}, {result 1}, {result 2})
  • Subqueries

Supported SQL functions

DA SQL supports a subset of functions defined in the SQL-92 standard, such as

  • COALESCE / ISNULL

    Returns 1st argument if its value is not NLL. Otherwise returns 2nd argument.

    • Syntax:

      COALESCE(check_value, replacement_value)

ISNULL(check_value, replacement_value)

    • Example:

      SELECT * FROM Orders o WHERE COALESCE(o.OrderStatus, 1) <> 1

SELECT * FROM Orders o WHERE ISNULL(o.OrderStatus, 1) <> 1

    • Parameters:

      • check_value - expression to be checked for NULL
      • replacement_value - value to be returned if check_value is NULL

  • 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

  • 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

  • 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

  • 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

  • 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 (full):

      TRIM(BOTH character FROM value)

TRIM(LEADING character FROM value)

TRIM(TRAILING character FROM value)

      Note: Some database engines (f.e. MS SQL Server 2016 and below) do not allow to trim arbitrary characters. In that case the value of the character parameter will be ignored.

    • Syntax (short):

      TRIM(value)

TRIM(BOTH value)

TRIM(LEADING value)

TRIM(TRAILING value)

    • Example:

      SELECT * FROM Table WHERE TRIM(BOTH e.LastName) = 'ALEX'

SELECT * FROM Table WHERE TRIM(TRAILING ' ' FROM e.LastName) = 'ALEX'

    • Parameters:

      • character - (optional) character to trim. The default value is space character (char(32))
      • 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

  • 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

Notes

  1. This link is the Wikipedia page covering the standard. Visit The SQL-92 standard to see the standard. ↩