SQL is a common language used to retrieve, update and manage data in a variety of database systems, including all ODBC-compliant databases. We are only concerned with the retrieval aspect for importing data into STEM, and so the only SQL query or statement which should be required in STEM is the
SELECT
statement. It is beyond the scope of the STEM documentation to describe even this statement in full, but a simple example will illustrate the main principles.
Example SELECT statements
Suppose we have a database with a table called ‘MyTable’, which has columns called ‘Name’, ‘Field’, ‘Value’ and ‘Text’, something like this:
A query SELECT Name, Value FROM MyTable retrieves:
A query SELECT Value FROM MyTable WHERE Name='Switch' retrieves:
You can use ‘*’ to mean ‘all columns’; the query SELECT * FROM MyTable retrieves:
SQL queries from STEM
The form of an external reference in a formula in STEM is '<filename>'!<reference>, and if <filename> represents an ODBC database, then, in general, the <reference> part will be interpreted as an explicit SQL query. So, for example, you could create a formula for Resource Capital Cost which looked like:
'C:\DATA\COSTS.MDB'!"SELECT Value FROM MyTable WHERE Name='Switch' "
Note: The SQL query is enclosed in double quotes to prevent special characters being mis-interpreted as another part of the formula. Similarly, the name ‘Switch’ must be enclosed in single quotes within the query.
Some databases, such as Microsoft Access, allow you to embed named queries within the database itself. So if you were to create the query above within the database, and name it ‘MyQuery’, you could use a simpler formula in STEM:
'C:\DATA\COSTS.MDB'!MyQuery
In fact, STEM examines the <reference> part of an ODBC link, and if it doesn’t start with the word ‘SELECT’, STEM assumes it has been given the name of a database table or query and expands it to
SELECT * FROM <reference>
when it evaluates the formula.
Note: If the query name contained spaces, e.g., ‘My Query’, then it would have to be enclosed in double quotes, to prevent special characters being mis-interpreted as another part of the formula; and in fact Microsoft Access would further require the name to be enclosed in square brackets, so the formula would look like
'C:\DATA\COSTS.MDB'!"[My Query]". Since these semantics are not part of the ODBC standard, it is generally advisable to avoid spaces and punctuation in table or query names.