Archivo de la Categoría de ‘Access’

Archivo de la Categoría 'Access'

MySQL no admite TRANSFORM PIVOT (pero se pueden obtener resultados parecidos).

Publicado

por

el 27 Noviembre 2008

. 9 Comentarios

Estos últimas días he estado mirando un tema que me preocupaba porque iba a necesitarlo en la aplicación que ando desarrollando… y tras no haber encontrado nada, finalmente me he tenido que poner a fondo con ello hasta sacarlo de una u otra manera. El tema consiste en hacer una consulta de referencias cruzadas en MySQL y, como digo, tras bastante investigar he descubierto que en realidad MySQL no admite esta clase de consultas. Para explicar mejor lo que pretendía hacer voy a basarme en un ejemplo consistente en la siguiente tabla sfc_salefrcast:

+---------+---------+------------+---------+
| sfc_cus | sfc_mat | sfc_mth    | sfc_qty |
+---------+---------+------------+---------+
| 12345   |       1 | 2008-11-01 |    1200 |
| 12345   |       3 | 2008-11-01 |    1500 |
| 54321   |       2 | 2008-11-01 |    2500 |
| 54321   |       3 | 2008-12-01 |    3500 |
| 54321   |       3 | 2009-03-01 |    4500 |
| 54321   |       3 | 2009-07-01 |    4500 |
| 99999   |       4 | 2009-02-01 |    2000 |
| 99999   |       4 | 2009-04-01 |    4000 |
| 99999   |       4 | 2009-06-01 |    6000 |
+---------+---------+------------+---------+

Es una tabla de previsiones de ventas en donde sfc_cus es el código del cliente, sfc_mat el código del material, sfc_mth el mes representado por su primer día y sfc_qty la cantidad que prevemos que vamos a venderle de dicho material a dicho cliente dicho mes (y dichosos nosotros si acertamos).

Bien, pues a partir de esta tabla pretendía conseguir una tabla (o vista… un conjunto de registros, vamos) en la que en cada registro se me mostrara una combinación de cliente y material, y a partir de aquí una columna para cada mes con la cantidad correspondiente. Algo del estilo de lo siguiente…

+---------+---------+------+------+------+------+------+------+
| sfc_cus | sfc_mat | M0   | M1   | M2   | M3   | M4   | M5   |
+---------+---------+------+------+------+------+------+------+
| 12345   |       1 | 1200 |    0 |    0 |    0 |    0 |    0 |
| 12345   |       3 | 1500 |    0 |    0 |    0 |    0 |    0 |
| 54321   |       2 | 2500 |    0 |    0 |    0 |    0 |    0 |
| 54321   |       3 |    0 | 3500 |    0 |    0 | 4500 |    0 |
| 99999   |       4 |    0 |    0 |    0 | 2000 |    0 | 4000 |
+---------+---------+------+------+------+------+------+------+

…en donde M0 es el mes actual (2008-11-01), M1 es el mes próximo, M2 el siguiente, etc.

Pues bien, si estuviéramos trabajando en Microsoft Access podríamos hacer algo simple como…

TRANSFORM SUM(sfc_qty)
    SELECT sfc_cus, sfc_mat
    FROM sfc_salefrcast
    GROUP BY sfc_cus, sfc_mat
PIVOT sfc_mth;

…y obtendríamos algo muy parecido al resultado deseado. Simplemente no nos mostraría las columnas donde no haya ningún valor que mostrar (como M2), pero sería un problema muy menor que solventaríamos sin dificultad. En Microsoft Excel también sería sencillísimo obtener esos resultados a través de una tabla dinámica.

No obstante, trabajo con MySQL. Y por desgracia MySQL no admite TRANSFORM-PIVOT. De hecho no he encontrado ninguna alternativa atractiva para resolver mi problema, de modo que he tenido que rascar un poquito y crear una a medida, que me ha quedado así:

SELECT
    sfc_cus,
    sfc_mat,
    SUM(IF(MID(sfc_mth, 1, 7) = MID(NOW(), 1, 7), sfc_qty, 0)) AS M0,
    SUM(IF(MID(sfc_mth, 1, 7) =
           MID(DATE_ADD(NOW(),INTERVAL 1 MONTH), 1, 7),
           sfc_qty, 0))                                        AS M1,
    SUM(IF(MID(sfc_mth, 1, 7) =
           MID(DATE_ADD(NOW(),INTERVAL 2 MONTH), 1, 7),
           sfc_qty, 0))                                        AS M2,
    SUM(IF(MID(sfc_mth, 1, 7) =
           MID(DATE_ADD(NOW(),INTERVAL 3 MONTH), 1, 7),
           sfc_qty, 0))                                        AS M3,
    SUM(IF(MID(sfc_mth, 1, 7) =
           MID(DATE_ADD(NOW(),INTERVAL 4 MONTH), 1, 7),
           sfc_qty, 0))                                        AS M4,
    SUM(IF(MID(sfc_mth, 1, 7) =
           MID(DATE_ADD(NOW(),INTERVAL 5 MONTH), 1, 7),
           sfc_qty, 0))                                        AS M5
FROM
    sfc_salefrcast
GROUP BY
    sfc_cus, sfc_mat;

(Ver actualización 1.)

Al no utilizar directamente fechas sino estar jugando con NOW() y la función DATE_ADD, obtenemos siempre resultados para el mes corriente y los siguientes N meses, independientemente de cuando solicitemos esa información a la base de datos.

Ignoro si hay alguna manera más directa de hacerlo (y si la hay y la conoces, estaré encantadísimo de leerla), pero por lo pronto lo he solucionado así…

Actualización.

1. Siguiendo el consejo -uno de ellos- de Luís Medel en los comentarios, he cambiado la versión original que había publicado para dejar esta más simplificada al realizar en un solo paso el sumatorio y el filtrado. Además, Luís propone utilizar CASE WHEN THEN ELSE END en lugar de IF.

2. Pedro Cambra facilita en los comentarios un link a la página de Roland Bouman donde se expone un método para hacerlo de forma dinámica utilizando procedimientos almacenados.

3. Pedro Cambra comenta también que MySQL dispone de la función EXTRACT con el parámetro YEAR_MONTH que efectivamente se podría utilizar para mejorar el apartado del MID(fecha,1,7). Más información en el manual de referencia de MySQL.

Formato no disponible en Snapshot de Access.

Publicado

por

Albert Mata

el 9 Junio 2008

en Access, Batch y Herramientas

. 4 Comentarios

Hace un tiempo, cuando me dedicaba fundamentalmente a programar aplicaciones en Access, me encontré con un problema que irremediablemente todos mis clientes sufrían tarde o temprano. Es un problema que se refiere a las versiones XP y 2003 (ignoro si alguna más) y que ocurre cuando se quiere generar un informe en formato Snapshot. Este formato es muy útil para exportar informes en archivos .snp que se pueden visualizar desde prácticamente cualquier ordenador (y si no existe un visualizador gratuito de Microsoft).

Bien, el problema increíblemente reside en que al instalar Microsoft Access en español, la instalación crea una clave en el registro de Windows (regedit.exe) con la descripción en castellano:

snp,,1,Formato Snapshot (*.snp),0

Pero cuando el propio Access busca el valor para esa clave espera encontrarlo en inglés:

snp,,1,Snapshot Format (*.snp),0

Y al no encontrarlo muestra un mensaje diciendo que el formato en cuestión no está disponible. O sea, una chapuza monumental de los señores de Microsoft, sí.

Para solucionar esto simplemente hay que cambiar la entrada correspondiente en el registro de Windows para cambiar el primer valor por el segundo. Ojito, todos sabemos que puede ser crítico cambiar cosas en dicho registro, así que cada cual sabrá lo que hace (pero este cambio es bastante inofensivo, eso sí ). En cualquier caso la entrada a modificar es la siguiente para Access XP:

HKEY_LOCAL_MACHINE\_
SOFTWARE\Microsoft\Office\10.0\Access\Report Formats

Y esta otra para Access 2003:

HKEY_LOCAL_MACHINE\_
SOFTWARE\Microsoft\Office\11.0\Access\Report Formats

Y la entrada concreta a la que hay que cambiarle el valor es Snapshot Format.

A mis clientes solía enviarles un archivo .bat para facilitarles la modificación, que consistía simplemente en la instrucción:

REG ADD "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
Office\11.0\Access\Report Formats" /v "Snapshot Format" /d "snp,
,1,Snapshot Format(*.snp),0" /f

Todo junto en la misma línea y sin espacios en los saltos de línea, ¿ok? Ah, y en donde pone 11.0 cambiarlo por 10.0 cuando la versión de Access sea la XP.

Por último añadir que en mi caso el uso del formato Snapshot era como paso intermedio para la creación de archivos PDF directamente desde Access sin necesidad de tener instalada ninguna impresora PDF, utilizando para ello la magnífica herramienta de Lebans.

Actualización: Iván aporta amablemente en los comentarios de esta misma entrada que en su caso para que pasara a funcionarle tuvo que cambiar el valor en castellano por este otro:

Formats\snp,,1,Snapshot Format (*.snp),0

Es decir, añadiéndole el Formats\ delante. Así que si estáis intentando resolver este problema y con las indicaciones del post no se os resuelve, no dejéis de probar también esta alternativa.

Simulando DLookup fuera de Access (III).

Publicado

por

Albert Mata

el 30 Mayo 2008

en .NET, Access, Herramientas y MySQL

. 0 Comentarios

En los dos posts anteriores he mostrado maneras de simular un DLookup en una base de datos MySQL mediante stored procedures y stored functions (primera aproximación y versión mejorada). No obstante, ha quedado claro que hacerlo así aunque puede resultar útil tiene sus limitaciones. Es por ello que normalmente prefiero utilizar un DLookup llevado a cabo desde la parte de la aplicación. En mi caso esto equivale a realizarlo en VisualBasic.NET. Y sin más preámbulos veamos el código de la función:

'--------------------------------------------------------------------
' Author:      Albert Mata (www.albertmata.net)
' Date:        20080530
' Description: Function to simulate Microsoft Access DLookup.
'--------------------------------------------------------------------
Public Function DLookup(ByVal Field As String, _
                        ByVal Table As String, _
               Optional ByVal Condition As String = "TRUE") _
                        As Object
    Try
        'Creating SQL string.
        Dim SQL As String
        SQL = "SELECT " & Field _
            & " FROM " & Table _
            & " WHERE " & Condition
        'Filling dataset with desired value.
        Dim DS As New DataSet
        Dim DA As New MySqlDataAdapter(SQL, CONNECTION_STRING)
        DA.Fill(DS, "anyname")
        'Returning value or Null.
        If Not IsDBNull(DS.Tables("anyname").Rows(0).Item(0)) Then
            Return DS.Tables("anyname").Rows(0).Item(0)
        Else
            Dim X As Object = Convert.DBNull
            Return X
        End If
    Catch
        'If error happens, returning Null.
        Dim X As Object = Convert.DBNull
        Return X
    End Try
End Function

Cabe aclarar que CONNECTION_STRING será una constante definida en algún punto de la aplicación que recogerá la cadena de conexión para nuestra base de datos. En mi caso es algo como:

Database = nombre_bbdd; _
Data Source = localhost; _
User ID = root; _
Password = mi_password

Relacionado con cadenas de conexión, recomendado darse un paseo por ConnectionStrings.com.

Yo estoy trabajando con una base de datos MySQL, por eso el DataAdapter es en realidad un MySqlDataAdapter, pero el funcionamiento sería análogo con cualquier otra base de datos y utilizando un objeto DataAdapter convencional. Lo único es que si se trabaja con MySQL es necesario previamente haber agregado la referencia correspondiente al proyecto, y para mayor comodidad en temas de nomenclatura importar el espacio de nombres correspondiente en la parte superior de la clase:

Imports MySql.Data.MySqlClient

Respecto a la función, poco que explicar. Pasarle el campo y la tabla deseados y de manera opcional una condición en formato SQL sin el WHERE. Si no se desea condición se pueden informar sólo dos argumentos, en esta ocasión no es necesario enviar una cadena vacía ni nada por el estilo. Además, el valor que nos devuelve la función es de tipo Object, así que no tendremos ningún problema con el tipo de dato devuelto, nos admitirá cualquier tipo de campo que quiera que sea el campo en cuestión de la tabla en cuestión. En caso de producirse algún error (nombre de campo o tabla mal escritos, conexión a base de datos fallida…) o si no se halla ningún registro, la función nos devolverá un valor nulo, con lo cual desde código podremos recogerlo sin problemas y actuar en consecuencia.

Simulando DLookup fuera de Access (II).

Publicado

por

Albert Mata

el 28 Mayo 2008

en Access, Herramientas y MySQL

. 0 Comentarios

En el anterior post expuse una manera de realizar un DLookup en MySQL mediante stored procedures y stored functions y comenté que para el siguiente post me reservaba un modo de hacer lo mismo desde .NET. No obstante me permito intercalar otra manera de realizar otra vez el DLookup desde MySQL algo más sencilla que la primera aunque para recuperar el valor necesitaremos igualmente una doble consulta. Veamos cómo queda el código en esta ocasión:

#--------------------------------------------------------------------
# Author:      Albert Mata (www.albertmata.net)
# Date:        20080527
# Description: MySQL procedure to simulate Microsoft Access
#              function DLookup.
#--------------------------------------------------------------------

#--------------------------------------------------------------------
# Returns DLookup value using fourth parameter.
#--------------------------------------------------------------------
DROP PROCEDURE IF EXISTS DLookup;
DELIMITER //
CREATE PROCEDURE DLookup(IN campo VARCHAR(7),
                         IN tabla VARCHAR(14),
                         IN condicion VARCHAR(250),
                         OUT valor VARCHAR(250))
BEGIN

DROP TABLE IF EXISTS tbl_dlookupaux;
CREATE TABLE tbl_dlookupaux (tbl_res VARCHAR(250));
SET @query = CONCAT('INSERT INTO tbl_dlookupaux SELECT ',
                    campo, ' FROM ', tabla, ' WHERE ',
                    IF(ISNULL(condicion),'TRUE',condicion),
                    ' LIMIT 1');

PREPARE running FROM @query;
EXECUTE running;
DEALLOCATE PREPARE running;
SELECT tbl_res INTO valor FROM tbl_dlookupaux;
DROP TABLE IF EXISTS tbl_dlookupaux;

END
//
DELIMITER ;

Esta vez utilizo únicamente una stored procedure, pero lo hago tomando parámetros tanto de entrada (campo, tabla y condicion) como de salida (valor). Con esto lo que hago es almacenar el resultado en el parámetro valor en lugar de almacenarlo provisionalmente en una tabla para luego recuperarlo de allí (bueno, internamente la stored procedure sí almacena en una tabla temporal, pero en tanto que la misma stored procedure la borra este proceso es transparente al usuario).

Recuerdo que partíamos de una tabla cty_nicecities con esta estructura:

La llamada debe ahora realizarse así:

CALL DLookup ('cty_nam', 'cty_nicecities', 'cty_id = 4', @my_city);
SELECT @my_city;

Almacenamos pues el valor en una variable (@my_city) y después lo rescatamos:

+----------+
| @my_city |
+----------+
| Dortmund |
+----------+

Tal vez este método es más limpio que el anterior. De todos modos para la próxima entrega, esta vez sí, postearé el DLookup mediante código en .NET, que será bastante más potente al incorporar gestión de errores y no tener limitaciones fruto de tener que especificar el tipo de datos concreto que esperamos obtener. No obstante un DLookup en MySQL se ejecutará íntegramente en el servidor, mientras que uno en .NET tendrá una parte ejecutándose en la máquina cliente, así que dependerá del gusto de cada cual escoger uno u otro. Yo me inclino por la versión en .NET…

Simulando DLookup fuera de Access (I).

Publicado

por

Albert Mata

el 27 Mayo 2008

en Access, Herramientas y MySQL

. 0 Comentarios

Para todos aquellos que empezamos en bases de datos con Access y que programamos aplicaciones con VBA, las funciones DCount, DMax y similares nos resultan de lo más útiles para obtener valores puntuales de una base de datos sin tener que recuperar explícitamente un recordset. La más útil de todas probablemente sea DLookup. La sintaxis resumida de DLookup es la siguiente (para una más detallada explicación se puede consultar la propia ayuda de Access):

DLookup (nombre_campo, nombre_tabla, condicion_opcional)

Y nos devuelve el valor del campo correspondiente de la tabla indicada, en el primer registro que cumpla la condición opcionalmente pasada como parámetro.

Pues bien, como ya he comentado en posts anteriores, la base de datos con la que actualmente estoy trabajando es MySQL, y por desgracia esta función tan útil no existe de manera predeterminada en MySQL. No obstante, ello no quiere decir que no podamos utilizar algo similar, sí que podemos, aunque vamos a tener que programar un poco antes. He aquí el código:

#--------------------------------------------------------------------
# Author:      Albert Mata (www.albertmata.net)
# Date:        20080527
# Description: MySQL procedure and function to simulate Microsoft
#              Access function DLookup.
#--------------------------------------------------------------------

#--------------------------------------------------------------------
# Creates an auxiliar table to store the value.
#--------------------------------------------------------------------
DROP PROCEDURE IF EXISTS PreDLookupAux;
DELIMITER //
CREATE PROCEDURE PreDLookup(campo VARCHAR(7),
tabla VARCHAR(14),
condicion VARCHAR(250))
BEGIN

END
//
DELIMITER ;

#--------------------------------------------------------------------
# Gets the value from auxiliar table created before.
#--------------------------------------------------------------------
DROP FUNCTION IF EXISTS DLookup;
DELIMITER //
CREATE FUNCTION DLookup() RETURNS VARCHAR(250)
BEGIN

DECLARE aux VARCHAR(250);
SET aux := (SELECT tbl_res FROM tbl_dlookupaux);
RETURN aux;

END
//
DELIMITER ;

Hay unas cuantas cosas que explicar:

1) Hasta la versión 5.0.45 de MySQL (la última GA cuando escribo esto y por tanto la de uso recomendado) las stored functions no permiten hacer ciertas cosas que harían mucho más fácil crear el DLookup en un solo paso, por eso se tiene que crear en un doble paso.

2) En el primer paso creamos una tabla auxiliar con un único campo y le insertamos un único registro con el valor que sale de la combinación campo-tabla-condicion que le hemos pasado. En el segundo paso nos devuelve ese valor que hemos almacenado temporalmente en la tabla auxiliar.

3) He puesto una longitud de 7 caracteres para el nombre del campo y de 14 para el de la tabla porque son las longitudes que tienen siempre mis nombres de campos y tablas, no obstante se puede modificar al gusto.

4) En cambio en tipo de campo de salida he puesto VARCHAR(250) por intentar ser algo genérico. Con esta definición nos devolverá sin problemas fechas y números, pero hay que tener en cuenta esta definición por si se utiliza DLookup con tipos de datos que no tengan cabida en un genérico VARCHAR(250).

5) Si no se desea pasar ningúna condición se puede establecer a cadena vacía ('') o bien a NULL, pero una stored function de MySQL no permite poner argumentos como de introducción opcional.

Veámoslo en funcionamiento. Tengo una tabla cty_nicecities con esta estructura:

Y puedo utilizar mi DLookup particular de esta manera:

CALL PreDLookup ('cty_nam', 'cty_nicecities', 'cty_id = 4');
SELECT DLookUp();

Para obtener:

+-----------+
| DLookup() |
+-----------+
| Dortmund |
+-----------+

Y otro ejemplo, si pongo:

CALL PreDLookup ('cty_nam', 'cty_nicecities', 'cty_cou = \'Italy\'');
SELECT DLookUp();

Obtengo:

+-----------+
| DLookup() |
+-----------+
| Rome |
+-----------+

Funciona aceptablemente bien y nos puede resultar muy útil cuando queramos utilizar la función DLookup en MySQL, sin embargo no es una solución idonea. Quizá cuando MySQL elimine las restricciones comentadas anteriormente se pueda crear una mejor solución. Por lo pronto en el próximo post subiré una solución infinitamente más elegante y potente para hacer lo mismo sólo que en lugar de hacerlo desde el lado del servidor MySQL lo haremos desde el lado de una aplicación programada en .NET.

Actualización.

En el siguiente post hay una versión distinta más breve y elegante del DLookup en MySQL.

El Blog de Albert Mata

Archivo de la Categoría 'Access'

MySQL no admite TRANSFORM PIVOT (pero se pueden obtener resultados parecidos).

Formato no disponible en Snapshot de Access.

Simulando DLookup fuera de Access (III).

Simulando DLookup fuera de Access (II).

Simulando DLookup fuera de Access (I).

Search

Últimos apuntes

Categorías

Links

Blogroll

También estoy en...

Temas habituales

Últimos comentarios