Agregar columnas en sentencias SELECT SQL

Artículo original: Adding Columns in SQL SELECT statements
http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx
Autor: Andy Kramek
Traducido por: Ana María Bisbé York 

---

Un requerimiento habitual cuando trabajamos con datos, independientemente de su origen, es poder agregar "al vuelo" columnas adicionales al resultado. Esto es muy sencillo si sólo se necesita una columna en blanco, basta con definirla directamente en la consulta utilizando la función SPACE(), de esta forma:

SELECT SPACE(30) AS newcol FROM nametable

(Observe que la palabra "AS" en realidad no es requerida por la sintaxis de VFP (o SQL Server); pero algunos de los dialectos lo requieren, y en cualquier caso, pienso que mejora la lectura de la consulta.) Ahora, vamos a suponer que tenemos algunos datos en una tabla como esta:

cfirst	clast	iintcol	nnum1	nnum2
Andy	Kramek	0	123.45	3456.78
Vladimir	Andropovitch	3	1111.65	654.32

Si necesitamos concatenar las columnas del nombre para crear un "nombre completo", entonces, podemos hacer algo como esto:

SELECT (ALLTRIM( cfirst ) + " " + ALLTRIM( clast )) AS fullname FROM sample

Aunque, en la práctica esto no es tan intuitivo como se puede ver a simple vista. La cuestión aquí es que cuando creamos una "columna computada" (que es lo que yo hago aquí) VFP crea la definición para esa columna basado en la longitud de las columnas existentes en la concatenación. Entonces, si ambos campos "cfirst" y "clast" se definen como c(20), el resultado se define con un campo que tenga c(41). En otras palabras, los 20 caracteres para el primer campo, uno para el espacio y 20 caracteres para cada campo.

Esto está bien, aunque puede ser una pérdida de espacio; pero no provocará pérdida de datos y si deseamos en realidad acortarlos, podemos simplemente utilizar la función PADR() para forzar el ancho de un valor específico:

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

Sin embargo, si además estamos convirtiendo tipos de datos (de números a caracteres, por ejemplo), entonces tendremos un problema potencia debido a que en este caso VFP no conoce de qué largo pudieran ser los datos. Todo lo que puede hacer es basarse en la definición del tamaño del primer elemento encontrado. Entonces, la consulta siguiente:

SELECT TRANSFORM( nnum1 ) AS cvalue FROM sample

Devolverá un conjunto resultante con la columna "cvalue" definida como c(6) - en otras palabras, el número de caracteres en el primer valor de la tabla. Esto, por supuesto, significa que el segundo valor se trunca debido a que en realidad contiene siete caracteres. Entonces, ahora es muy importante asegurarse de que hemos especificado un formato para el campo que sea tan largo como para manipular cualquier posible valor y nosotros podemos utilizar la función PADR() para controlar el formato una vez que hayamos transformado el dato: como esto:

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

Pero, la introducción de la función CAST() en VFP 9.0 brinda una alternativa, debido a que nos permite decir directamente a VFP cómo deseamos obtener la salida de los resultados. Entonces, en VFP 9.0 podemos escribir la primera consulta de esta forma:

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

y la segunda:

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

Todo esto está muy bien cuando estamos trabajando con columnas existentes; pero qué ocurre si necesitamos crear una columna nueva con un tipo de dato específico? Bueno, es bastante fácil crear columnas de caracteres, empleando la función SPACE() (o incluso PADL()) para crear el ancho requerido:

SELECT *, SPACE(30) AS newstring FROM sample

De forma similar, si necesita una columna para datos tipo moneda, las debería definir como "$U" y un dato nuevo utilizando una cadena fecha vacía "{}", una nueva columna decimal utilizando una cadena de ceros, una columna lógica utilizando .F., etc.

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

Teniendo esto, podría estar tentado a pensar, que crear una columna para datos enteros sería tan sencillo como:

SELECT 0 AS newint FROM sample

Después de todo, al crear una columna para enteros en una tabla, se inicializa con "0", entonces, parece razonable decirle a VFP que al crear una columna para valor "0" obtendremos un entero. Desafortunadamente no es el caso! Lo que en realidad ocurre es que VFP crea una columna numérica con ancho = 1 y decimales = 0. El resultado es que solamente puede guardar los valores de 0-9. ¡Y esto no es lo que queremos!

Entonces, cómo obtenemos un valor entero? Bueno, antes de VFP 9.0 existía un pequeño truco y dos vías posibles para hacerlo. La primera (y más sencilla) sería definir la columna con suficiente espacio para guardar el entero.

SELECT *, 0000000000 AS newint FROM sample

El resultado en realidad no es una columna para datos enteros, muy por el contrario, es una columna numérica muy grande (N(10,0)) y siempre encuentro problemático estar contando tantos ceros. Para crear una verdadera columna para enteros en un conjunto resultante tenemos que emplear un truco que implica crear un producto cartesiano.


Un producto cartesiano ocurre cuado una consulta une dos tablas sin especificar una condición de validación. El resultado es que cada registro de la primera tabla se enlaza con cada registro de la segunda. El conjunto resultante contiene, por tanto, la cantidad de registros que se obtiene de la multiplicación de la cantidad de registros de la primera tabla por la cantidad de registros de la segunda tabla. Esto generará rápidamente un conjunto resultante MUY grande. Una consulta como esta:

SELECT * FROM table1, table2

Asumiendo que tenemos 100 registros en la primera tabla y 1000 registros en la segunda, podríamos generar un resultado que contenga 100,000 registros. Suena como un error, verdad? Entonces, ¿cómo puede esto ayudarnos a crear una columna para datos enteros?

Bueno, si creamos un cursor llamado "dummy" (ficticio) que tiene una única columna (definida como un entero). Entonces, añadimos un registro vacío al cursor e incluimos "dummy" en la lista FROM de la consulta sin especificar una condición de unión, forzamos que todas las columnas del cursor ficticio sean agregadas incondicionalmente al conjunto resultante con una columna extra, para valores enteros, que se llama con el nombre que le hayamos dado en el cursor ficticio. De esta forma:

CREATE CURSOR dummy ( newint I )
INSERT INTO dummy VALUES (0)
SELECT * FROM sample, dummy

El mismo truco puede ser utilizado para agregar al conjunto resultante, campos MEMO, GENERAL o incluso cualquier columna, para cualquier tipo de datos.

Sin embargo, utilizando VFP 9.0 no tendremos más necesidad de emplear este truco, gracias, una vez más a la función CAST(). Al utilizar esta función podemos simplemente decirle a VFP que cree el tipo de columna que necesitamos:

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

De hecho, podemos también utilizar CAST() para forzar que sea devuelto un tipo de datos determinado. Algo que siempre resultaba irritante para los usuarios era cuando nosotros, los desarrolladores, utilizamos campos tipo fechahora para guardar datos que son en realidad solo fecha (con frecuencia, no teníamos opción debido a que necesitábamos compatibilidad con SQL Server donde no podemos utilizar tipos fecha directamente porque no está soportado). Una vez más, tenemos forma de controlar esto en todas las versiones de VFP; pero la nueva función CAST() es la forma más limpia y sencilla de controlarlo:

SELECT CAST( datetime AS date ) AS sqldate FROM sample

El fichero de Ayuda de VFP 9.0 incluye una tabla que define las conversiones que se pueden hacer con CAST() y las que no. Por ejemplo, puede mostrar valores de fecha como caracteres, varchar, datetime o memo; pero no como datos numéricos.

Por supuesto, la razón habitual para agregar una nueva columna al conjunto resultante es que luego puede actualizarlo con algunos valores. Un requerimiento habitual es agregar una columna de valores lógicos al cursor que será utilizada como fuente para imprimir un informe. La idea es que al procesar cada registro, un indicador se actualiza y tenemos alguna idea de cuáles son los registros que en realidad deben enviarse a la impresora en caso de que algo vaya mal. El problema en este caso es, que el cursor creado por una instrucción select SQL se crea en VFP de tipo sólo lectura.

Antes de la versión 7.0, teníamos que emplear otro truco para crear una versión sólo lectura de un cursor VFP. Se basa en el hecho de que el cursor en realidad está implementado como una tabla temporal y si trata de utilizar en dos ocasiones un cursor existente, VFP está obligado a crear una segunda tabla para ello, y esa tabla se va a crear del tipo lectura-escritura. Entonces, el código siguiente va a crear un cursor de lectura-escritura en cualquier versión de VFP (¡¡ incluso en FoxPro 2.x!!)

SELECT * FROM sample INTO CURSOR temp
USE DBF( "temp" ) AGAIN IN 0 ALIAS cur_readwrite
SELECT cur_readwrite
USE IN temp

La versión 7.0 de VFP introdujo la claúsula READWRITE al lenguaje, la que dice a VFP que cree directamente un cursor tipo lectura-escritura:

SELECT * FROM sample INTO CURSOR temp READWRITE

Y ahora lo utilizo siempre que sea posible.

Sin embargo, como recordaba hace unos días, es importante conocer los viejos trucos ya que tenemos alguna aplicación funcionando en FoxPro 2.6 para un cliente y la vía más sencilla para controlar el problema era utilizar un cursor del tipo lectura-escritura, por supuesto, en FoxPro 2.6 no lo podía hacer ... excepto utilizando el truco mostrado antes.