Andy Kramek

Adding Columns in SQL SELECT statements

A common requirement when working with data, irrespective of its source is to be able to add additional columns to the result set ‘on the fly’. This is pretty straightforward if you simply want a new, blank column, just define it directly in the query using the SPACE() function like this:

SELECT SPACE(30) AS newcol FROM nametable

(Note: The word “AS” is not actually required in VFP (or in SQL Server) syntax, but some SQL dialects do require it, and, in any case, I think it improves the readability of the query). Now. let’s suppose we have some data in a table like this:

If we needed to concatenate the name columns to create a ‘full name’, then we can do so like this:

SELECT (ALLTRIM( cfirst ) + “ ” + ALLTRIM( clast )) AS fullname FROM sample

Although, in practice even this is not quite as straightforward as it may seem at first glance. The issue here is that when creating a ‘computed column’ (which is what we are doing here) VFP creates the definition for that column based on the length of the existing columns in concatenation. So if the “cfirst” and “clast” fields are each defined as C(20), the result set is defined with a field that is C(41). In other words, the 20 characters for the first field, one for the space and 20 characters for the last field.

This is fine, although it may be a little wasteful of space, but it won’t cause us to lose any data and if we really wanted to trim it down we could simply use the PADR() function to force the width to a specific value:

SELECT PADR( ALLTRIM( cfirst ) + “ ” + ALLTRIM( clast ), 30) AS fullname FROM sample

However, if we are also converting between data types (numeric to character for example) then we run into a potential problem because in this case VFP doesn’t necessarily know how long each value might be. All it can do is to base the definition on the length of the first values it finds. So a query like this:

SELECT TRANSFORM( nnum1 ) AS cvalue FROM sample

would return a result set with the ‘cvalue’ column defined as C(6) – in other words, the number of characters in the first value in the table. This, of course, means that the second value gets truncated because it actually contains seven characters. So now it becomes really important to ensure that we specify the format of the field to be large enough to handle any possible value and we can use the PADR() function to handle the formatting once we have transformed the data, like this:

SELECT PADR( TRANSFORM( nnum1 ), 10) AS cvalue FROM sample

But the introduction of the CAST() function in VFP 9.0 provides an alternative because it allows us to tell VFP directly how we want to output the result. So, in VFP 9.0 we can write the first query like this:

SELECT CAST( ALLTRIM( cFirst ) + “ ” + ALLTRIM( cLast ), AS CHAR(30)) AS fullName FROM sample

and the second like this:

SELECT CAST( nnum1 AS CHAR(10)) AS cvalue FROM sample

This is all fine when we are dealing with existing columns, but what happens if we need to create a new column with a specific data type? Well it’s easy enough to create character columns, just use the SPACE() function (or even PADL()) to create the required width:

SELECT *, SPACE(30) AS newstring FROM sample

Similarly if you needed a new currency column, you would define it as “$0” and a new date by using an empty date string “{}”, a new decimal column by using a string of zeroes, logical fields using .F. and so on:

SELECT $0 AS yamount, {} AS dpaid, 00000.00 AS newbal, .F. AS lCleared FROM sample

Given this, you might be tempted to think that to create an integer column you can simply use the following:

SELECT 0 AS newint FROM sample

after all, when you create an integer column in a table it is initialized to “0”, so it seems reasonable that telling VFP to create a column for the value “0” would result in an integer. Unfortunately that is not the case! What actually happens is that VFP creates a Numeric column with width = 1 and decimals = 0. The result is that you can only store the values 0 – 9 in the result. Not quite what we wanted!

So how do we get an integer? Well before VFP 9.0 this was a little tricky and there were two possible ways of doing it.

First (and simplest) you could define the computed column with sufficient width to hold an integer.

SELECT *, 0000000000 AS newint FROM sample

The result is not really an integer column however, merely a larger numeric column (N(10,0)) and I always found counting those zeroes problematic anyway. To create a true integer column in a result set we have to resort to a little trick that involves creating a Cartesian product.

A Cartesian product occurs when a query joins two tables without specifying a valid condition. The result is that every record in the first table is joined with every record in the second. The result set contains, therefore the number of records that is obtained by multiplying the record count of the first table by the record count of the second. This can rapidly generate VERY large result sets. A query like this

SELECT * FROM table1, table2

Assuming 100 records in the first table and 1000 records in the second, would generate a result containing 100,000 records. Sounds like an error eh? So how can this help us to create a true integer column?

Well, if we create a cursor named “dummy” which has one column (defined as an integer). Then add one (empty) record to the cursor and include “dummy” in the FROM list of the query without specifying a join condition we force every column from the dummy cursor to be added unconditionally to every record in the result set. The result is that every record in the result set ends up with an extra, integer, column named whatever we called it in the dummy cursor. Like this:

CREATE CURSOR dummy ( newint I )

INSERT INTO dummy VALUES (0)

SELECT * FROM sample, dummy

The same trick can be used to add MEMO, GENERAL or indeed any columns, of any data type, to result sets.

However, using VFP 9.0 we no longer need to resort to this trick thanks, once again, to the CAST() function. By using this function we can simply tell VFP to create whatever type of column we need:

SELECT *, CAST( 0 AS INT) AS newint FROM sample

In fact we can also use CAST() to force specific data types to be returned. One the perpetual irritants for users is when we developers use datetime fields to hold data that is really only a date (often, we have no choice because if we are seeking compatibility with SQL Server does we cannot use DATE data types directly because it does not support them). Once again we have ways of handling this in all versions of VFP but the new CAST() function is the cleanest and simplest to handle it:

SELECT CAST( datetime AS date ) AS sqldate FROM sample

The VFP 9.0 help file includes a table which defines which conversions can be handled by CAST() and which cannot. For example you can cast date values as a character, varchar, datetime or memo, but not as a numeric data type.

Of course, the usual reason for adding a new column to a result set is so that you can update it with some value later. One common requirement is to add a logical column to a cursor that will be used as the source for printing a report. The idea is that as each record is processed, the flag gets updated and so we have some idea which records have actually been sent to the printer in case something goes wrong. The problem with this scenario is that cursors created by SQL Select statements are created as read-only by VFP.

Prior to Version 7.0 we had to use another trick to create a read-only version of a VFP cursor. This relies on the fact that a cursor is actually implemented as a temporary table and that if you try to use an existing cursor twice, VFP is forced to create a second table for it, and that table will be created read-write. So the following code will create a read-write cursor in any version of VFP (and even in FoxPro 2.x!!!)

SELECT * FROM sample INTO CURSOR temp

USE DBF( “temp” ) AGAIN IN 0 ALIAS cur_readwrite

SELECT cur_readwrite

USE IN temp

VFP Version 7.0 introduced the READWRITE clause to the language which tells VFP to create a read-write cursor directly:

SELECT * FROM sample INTO CURSOR temp READWRITE

And whenever possible I now use this.

However, as I was reminded the other day, the old tricks are useful to know because I had to do some work on a FoxPro 2.6 application for a client and the simplest way to handle the problem was to use a read-write cursor which, of course, in Fox 2.6 you couldn’t do…except that by using the above trick I was able to do it.