UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ACE SQL Extensions    
ACE SQL Extensions

The keywords below are not part of SQL standard but are very useful in ACE. The SQL Standard allows extensions to be added to the implementation. The article also lists cases where ACE SQL does not follow the SQL Standard.


Contents

Extensions



PARAMETERS

Enumerates all parameters that a query may use. This is optional but recommended when parameters is needed:

CODE
PARAMETERS StartDate DATE, EndDate DATE;
SELECT ...
FROM ...
WHERE myDate BETWEEN [StartDate] AND [EndDate];

The parameters statement must be terminated by a semicolon. The parameters may be used in any kind of queries except for Passthrough Query.

TRANSFORM and PIVOT

A Crosstab Query uses TRANSFORM and PIVOT to describe how the rows should be transposed into columns and what should be displayed for the intersection.


Deviations



Column list allowed in DELETE query

The SQL Standard does not allow for defining columns in a DELETE query as such queries operate on rows as a whole. However, ACE allows this to support preview functionality when using query builder. So a DELETE query may look like this:

CODE
DELETE FirstName, LastName
FROM tblCustomers
WHERE ActivityDate > #1/1/2009#;

This does not delete only the FirstName and LastName, but in fact the entire rows where criteria is met. If the intention is to clear data for those columns only without deleting the rows, an UPDATE query should be used instead.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 3,388 times.  This page was last modified 01:31, 10 February 2012 by Jack Leach. Contributions by BananaRepublic  Disclaimers