Supported Portable SQL Syntax

IN THIS PAGE

Notes on Syntax
Select_Statement
Delete Syntax
Update Syntax
Insert Syntax

Description

Overview of supported SQL syntax in portable SQL queries.

Discussion

Although there are standards for SQL, each database vendor introduces slight variations to the language that make it challenging to write a single statement that executes on an arbitrary database. Alpha Anywhere Alpha DAO makes it possible to write SQL statements that are portable across many databases. The Portable SQL parser creates an intermediate data structure that Alpha DAO syntax handlers use to generate native syntax for any database that Alpha Anywhere has a handler. In cases where your database vendor selection does not determine the syntax (such as ODBC or ADO.NET drivers), you specify the target syntax you want to generate in connection string dialogs or a manually constructed connection string. In many cases, you can select the ODBC or Generic (based on ANSI SQL-92) syntax handler if a separate handler doesn't exist for your database.

The following is a simplified SQL grammar of the SQL dialect supported by the Alpha Anywhere Portable SQL parser.

Notes on Syntax

Words that are italicized are rules.
Words in all capital letters are literals or keywords and appear as typed. The actual SELECT statement is case insensitive.
The vertical bar ( | ) indicates that any of the alternatives are valid. Commas are part of the input stream.
Items in square brackets [] are optional.
With some exceptions, parentheses shown are a required part of the input stream

Select_Statement

Select_Statement Components and Syntax

SELECT [ DISTINCT | ALL [ROWS] | FIRST integer ] Column_Item [, Column_Item ... ]
FROM Select_Table_Reference [, Select_Table_Reference ... ]
[ WHERE Logical_Expression ]
[ GROUP BY Value_Expr [, Value_Expr ... ] [ HAVING Logical_Expression ] ]
[ UNION [ALL] Select_Statement [ UNION [ALL] Select_Statement ... ] ]
[ ORDER BY Column_Item [ ASC | DESC ] [, Column_Item [ ASC | DESC ]  ... ]]

Argument

Syntax

Column_Item

Value_Expr | Identifer [ AS Identifier ]
Identifier = Value_Expr

Column_Reference

[Identifier.]Identifier
[Identifier.]*

The table name is optional when specifying column names in a SELECT statement.

Select_Table_Reference

[Identifier.]Identifier
[[[LEFT|FULL|RIGHT [OUTER]] | INNER] JOIN Select_Table_Reference [ ON Logical_Expression ]] ... ]
[[AS] Identifier ]

JOIN is optional.
JOIN type (LEFT, FULL, RIGHT, INNER) is optional. If no type is specified, an INNER JOIN is performed.
OUTER can only be used with LEFT, FULL, or RIGHT.
Specify AS Identifier to define a table alias. E.g. tableb AS b

Defining a table alias is optional.

Logical_Expression

Value_Expr [ AND|OR Value_Expr ... ]

Value_Expr

[NOT] Logical_Expression [ AND|OR Logical_Expression ... ] |
[EXISTS]  Select_Statement |
Value_Expr Comparison_Operator [ ALL | SOME | ANY ] ( Select_Statement ) |
Value_Expr Comparison_Operator Value_Expr |
Value_Expr IS [NOT] NULL |
Value_Expr CONTAINS ( Column_Reference, String ) |
Value_Expr [NOT] LIKE Value_Expr |
Value_Expr [NOT] BETWEEN Value_Expr AND Value_Expr |
Value_Expr [NOT] IN (Select_Statement | Value_Expr_List) |
Constant [ Operator Value_Expr ] |
Value_Expr [ Operator Value_Expr ] |
[Unary_Operator]Sub_Expression

For Example:

ShipDate IS NULL
OrderDate IS NOT NULL
StartDate BETWEEN {1995-01-01} AND {2005-12-31}
Address LIKE '123 Main%'
Customer LIKE '%Co%'
FirstName IN ('Jane','Steve','Fred')
FullTextColumnContains(LastName,'Marie')

Sub_Expression

Function | Constant | NULL | Column_Reference | (  Select_Statement ) | CASE | CAST

Function

Identifier ( [ ALL | DISTINCT ] Value_Expr | * ] ) |
Identifier ( Value_Expr [, Value_Expr ... ] ) |
"current_timestamp" | "current_user" | "session_user" | "system_user" | "user"

CASE

CASE Value_Expr
    WHEN Value_Expr THEN Value_Expr
    [...]
    [ WHEN Value_Expr THEN Value_Expr ]
    [...]
    [ ELSE Value_Expr ]
END

CASE must have at least one WHEN clause. If testing for multiple values, multiple WHEN clauses can be used. ELSE is optional and can be specified without a Value_Expr to handle all cases that don't match a WHEN clause.

CAST

CAST (Value_Expr AS Type)

Type is the data type to cast the value, e.g. INTEGER or DATE. The type is database dependent. Consult your database documentation for a list of supported data types.

If you use a type name that is not supported on the target SQL database, CAST will fail.

Identifier

Non_Quoted_Identifier | Quoted_Identifier

Constant

Number | Logical | String | Currency | Date_Time

Constant

Example

Number

0.0
0
-0
0x0
0.0e0

Logical

.t.
.f.
true
false

String

"Spain"

Currency

$12.50

Date_Time

Date and Date Time values specified using the following format:

{yyyy-mm-dd [HH:MM:SS [.ffffff] [am|pm]]}
{yyyy-mm-dd}
{m[m]/d[d]/yy[yy]}
{m[m]/d[d]/yy[yy] [HH:MM:SS[.ffffff] [am|pm]]}

Where:

yyyy or yy is the year.
mm or m is the month.
dd or d is the day.
HH is the hour.
MM is the minutes.
SS is the seconds.
.ffffff is fractions of a second -- you can specify up to six digits.
am|pm is the "am" or "pm" for 12 hour time format.

For example:

{2019-12-31 14:47:00}
{2019-01-01}
{12/31/2019}
{1/1/2019 01:01:01.000000}
{11:59:00 pm}

Mathematical_Operator

+ | - | &
* | / | MOD

Comparison_Operator

= | != | <> | <= | !> | < | >= | !< | >

Unary_Operator

+ | - | ~

Unary_Operator: Description
+: Positive. E.g., +12
-: Negative. E.g., -3
~: One's complement where supported. Check your database documentation to see if this operator is available.

Delete Syntax

The Portable SQL DELETE statement deletes one or more records from the specified table.

DELETE FROM Table_Name
WHERE Column_Name = Value_Expression

Use SQL::Arguments in WHERE clauses to eliminate common SQL vulnerabilities (such as SQL injection attacks).

Update Syntax

The Portable SQL UPDATE statement modifies one or more records in the specified table.

UPDATE Table_Name
SET Column_Name = Value_Expression_1
 WHERE Column_Name = Value_Expression_2

Use SQL::Arguments in SET and WHERE clauses to eliminate common SQL vulnerabilities (such as SQL injection attacks).

Insert Syntax

The Portable SQL INSERT statement adds a record to the specified table.

INSERT INTO Table_Name ( Column_Name_1, Column_Name_2, ... , Column_Name_N )
VALUES ( Value_Expression_1, Value_Expression_2, ... Value_Expression_N )

Use SQL::Arguments in VALUES to eliminate common SQL vulnerabilities (such as SQL injection attacks).