by Ross Mack - GUI Computing

The Surgeon General warns that excessive use of SQL may harm others.

Sometimes it's important to select data from tables that is not strictly there. For example you may have a library of functions that will use data derived from database queries for some sort of operation. A common example of this might be a library that populates combo boxes based on SQL statements. This is great for doing look-ups of reference data on edit screens. You might use a query like "SELECT State from tblStates" to populate a combo with VIC, NSW, QLD, and so on. But if you didn't decide to store those states in a table to start with how do you get them because of an SQL statement? On the other hand, maybe you want to add an item to the list like, '(All States)' and would like to be able to include that in your SQL statement and not hard code it.

Well, you can. All you do is have your SQL return literals instead of or as well as queried data. To implement the above examples try executing the following SQL (the table, Table1 is irrelevant, use any table name you want, so long as it exists in your database).

        SELECT 'Vic' AS State FROM Table1
        UNION SELECT 'NSW' AS State FROM Table1
        UNION SELECT 'Qld' AS State FROM Table1
        UNION SELECT 'ACT' AS State FROM Table1
        UNION SELECT 'NT' AS State FROM Table1
        UNION SELECT 'Tas' AS State FROM Table1
        UNION SELECT 'SA' AS State FROM Table1
        UNION SELECT 'WA' AS State FROM Table1  

Or

        SELECT '< All States >' AS State FROM Table1
        UNION SELECT 'Vic' AS State FROM Table1
        UNION SELECT 'NSW' AS State FROM Table1
        UNION SELECT 'Qld' AS State FROM Table1
        UNION SELECT 'ACT' AS State FROM Table1
        UNION SELECT 'NT' AS State FROM Table1
        UNION SELECT 'Tas' AS State FROM Table1
        UNION SELECT 'SA' AS State FROM Table1
        UNION SELECT 'WA' AS State FROM Table1

Or

        SELECT '< All States >' AS State FROM tblStates
        UNION SELECT State FROM tblStates

This works quite well, but there are a couple of things to be aware of because of the way JET interprets these queries. Let's look at how this is done:

SELECT 'Fred' AS Name FROM MyTable UNION SELECT 'Barney' AS Name FROM MyTable;

Unfortunately, there are two circumstances where this does not work so well. The first case is where there is no data in the table already. In the above example, if there is no data in the table called MyTable then no data would be returned. This is because Access tries to optimise the query by checking if there is a rowcount of 0 on the table and if that is the case just returns 0 records from any query that queries that table. The easiest work-around for this is to reference a table that will always have records. Because you are hard coding the data returned and the column names it doesn't matter what table is actually referenced.

The second problem arises when there are multiple records in the table and you are attempting to return only one row of literal data. Let's say that MyTable has four rows containing 'Fred', 'Barney', 'Wilma', and 'Betty' (Dino is in a different table). You then execute the following query (remember that because you are specifying what to return literally it need not relate to the actual contents of the table):

SELECT 'Vic' AS State FROM MyTable

This would return a RecordSet containing four rows all containing 'Vic'. In other words, one row will be returned for each record in the table. There are two ways around this, the first is to query more than one record:

SELECT 'Vic' AS State FROM MyTable UNION SELECT 'NSW' AS State FROM MyTable;

The second is to use the DISTINCT keyword to only return non-duplicate rows:

SELECT DISTINCT 'Vic' AS State FROM MyTable

So, there's how to fake data when it has to be provided in the form of a SELECT statement, or how to add one or two extra rows of special data that don't exist in your tables. Remember, use this carefully and it will assist you, use it recklessly and it will eventually lead a rebellion and bring about your downfall. Or something like that.

---

[HOME] [TABLE OF CONTENTS] [SEARCH]