SQL > What's missing in Local SQL

What we miss in the otherwise well working BDE Local SQL implementation

Though much can be done using BDE Local SQL, there still are some limitations hard to circumvent except via elaborate SQL tricks.

This leaves a wish list to consider choosing a database format with associated available query language.

SELECT wishes

•Allowing correlated sub-query SELECT statements as arguments for the SELECT clause

•Allowing sub-query SELECT statements as arguments for the FROM predicate - see Finding duplicates

•SELECT fieldslist INTO destinationtable FROM sourcetable
(actually not an ISO SQL standard but a very widely implemented SyBase extension)
or from standard SQL:
CREATE TABLE NewTable AS SELECT * FROM SourceTable;

•temporary in MEMORY tables

SQL script processing

•Paradox natively cannot execute standard semicolon separated SQL scripts (SQL batch commands).
However, this is mediated in PdxEditor by a parsing semicolon separated scripts into sequentially executed SQL statements, including mixed DDL and DML.

Conditions:

•CASE

Functions:

•NOW / CURRENT_TIMESTAMP

•DATE / CURRENT_DATE

•DAYOFYEAR(DateValue) - see also SQL Tricks / Day of Year, Week Number

•DAYOFWEEK(DateValue)

•WEEKNUMBER(DateValue)

•ROUND(float value)

•INTEGER_PART(float value)

•FRACTIONAL_PART(float value)

•MODULO(a,n)

•LENGTH(string)

•POSITION(substring, string) - or equivalent: INSTR (Oracle) or: CHARINDEX (MS SQL Server)

CREATE TABLE wishes

Field types:

•WIDECHAR / WIDESTRING

•WIDEMEMO

ALTER TABLE wishes

•ALTER TABLE TableName RENAME TO NewTableName

•ALTER TABLE TableName RENAME COLUMN FieldName TO FieldName

•ALTER TABLE TableName MODIFY (FieldName DataType)

Missing SQL commands to specify Field Validity checks:

•[DEFAULT(value)] specifying pre-filled default value

•[NULL] allowing NULLs (the default condition)

•Required fields specified as [NOT NULL]

•[MIN(value)]

•[MAX(value)]

Referential Integrity

•SQL specifying Referential Integrity.
Even though Paradox tables do support referential integrity (since Paradox table vers. 4), the BDE Local SQL strangely does not support defining this valuable feature, available only by other means through specified software, as e.g. the no more functional DBD7.

•Creating Paradox referential integrity is made available with PdxEditor 5.12.

And for the demanding

•cross tab or pivot table query