|
Contents
- Index
SQL - Quirks and known issues
Field names must not contain spaces
Though field names may contain numbers, they must begin with an alpha character
Raw data must not contain ; (semi colon, because it is the field delimiter) or CRLF (carriage return line feed, because it is the record delimiter)
Regardless of your International settings the . (period) must always be used for the decimal point in the raw data and all SQL syntax
If your International settings uses a , (comma) as the decimal separator, always use the asnumber() function when referencing table columns that contain numeric (non integer) data.
WHERE clause - if using negative number literals, then reference them using the AsNumber() function, for example AsNumber('-452.6'). Numbers will be treated as absolutes if you just use -452.6
Using * (As in "Select * from table") to list all fields is only valid for a single table. When joining tables you must qualify each field you want to return.
To sort a column in strictly numerical sequence (remember, the SQL engine stores all data as strings), precede the column name with a #. For example "Select * from products order by #qty"
The IN operator (on the WHERE clause) is only supported for SELECT (does not work with UPDATE or DELETE)
The AVG function can only be used once per Select statment (Multiple AVG on the same select will yield incorrect results)
A table remains open[1] in memory until you issue a RELEASE TABLE. The table will remain open across macro sessions for the life of your current GSAK session (until you exit GSAK). If you want to make sure your macro always uses a fresh copy of the table on start up, always make RELEASE TABLE your first command before using any others.
The engine does single table queries blazingly fast. However, joins can be abysmally slow so I would avoid them unless the number of records in each table are quite small.
You cannot combine AND and OR booleans in the same SQL WHERE statement.
As the SQL engine uses single quotes to denote string literals, you can not have a single quote in the name of your path (for CONNECT TO) or sql table name.
This works:
SELECT d FROM data WHERE a = 'this' OR b = 'that' OR c = 'the other'
So does this:
SELECT d FROM data WHERE a = 'this' AND b = 'that' AND c = 'the other'
But this doesn't
SELECT d FROM data WHERE a = 'this' AND (b = 'that' OR c = 'the other')
You have to code the final example like this, splitting it into two queries using a temp table to create the AND:
ASSIGN TO temp SELECT * FROM data WHERE a = 'this'
SELECT d FROM temp WHERE b = 'that' OR c = 'the other'
Also note that the WHERE clause for DELETE requires that each expression is enclosed in parenthasis. For example:
DELETE FROM caches WHERE (difficulty='1.5') and (terrain='3.0')
[1] The main quirk of the SQL engine is that it is memory based. The term "open" here really just means when you reference a table for the first time. You could reference a table using any of the SQL commands (SELECT is the most common). When you reference a table in any way (open it) for the first time, a copy of the table data is stored in memory. All further actions on that table are done from this memory table until you issue a RELEASE command. If you want to make sure any changes to the "memory" table are saved you must use COMMIT before you RELEASE the table
|