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-92¹ 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 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
• 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

• 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:

    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

• 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