Consulta con GROUP BY sobre DBF me da error

[Victor Casajuana Mas]

Hola.

Estoy atascado con una consulta muy simple:

oC := win_oleCreateObject( "ADODB.Connection" )

   oC:Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source="' + cPath + '";Extended Properties=dBASE IV;User ID=Admin;Password=;')

   If !(oC:State > 0)

      ?'error'

      Return ( Nil )

   Endif

   oRs := win_oleCreateObject( "ADODB.Recordset" )

   TEXT INTO cQuery

      SELECT CODIGO, NOMBRE, COD_PRO

      FROM ARTICULO

      GROUP BY COD_PRO

   ENDTEXT

   oRs:Open( cQuery, oC )  

que me devuelve es:

Error description: (DOS Error -2147352567) WINOLE/1007  Ha intentado ejecutar una consulta que no incluye la expresión especificada 'CODIGO' como parte de una función de agregado. (0x80040E21): Microsoft JET Database Engine
   Args:
     [   1] = C         SELECT CODIGO, NOMBRE, COD_PRO
      FROM ARTICULO
      GROUP BY COD_PRO

     [   2] = O   WIN_OLEAUTO

Como veis la consulta es muy básica, con la misma tabla pero en MySql me funciona correctamente.

Alguna idea?

Gracias!

[David Field]

Hola,

MySql perdona muchas incongruencias cosa que MSSQL no (o para tu caso Microsoft aunque utilices DBF's).

Voy a pensar que tienes en tu tabla de artículo los campos CODIGO de producto, NOMBRE de producto y COD_PRO el código del proveedor, donde pudieras tener varios productos que le compras a un proveedor y quieres que te devuelva un solo registro por proveedor?

Si explicas lo que quieres obtener se te puede ayudar mejor.

Saludos,

David Field

[Victor Casajuana Mas]

Hola David.

Tienes razón, mi consulta ha sido algo escueta, pido disculpas...

Lo que necesito es; partiendo de una tabla facplin.dbf con los campos:

- CODIGO ( código del artículo )

- NOMBRE ( nombre del artículo )

- CODPRO ( código de proveedor del artículo )

- FECHA ( fecha de la compra )

- PRECIO ( precio de la compra )

Quiero hacer una consulta que me devuelva los últimos precios de compra de un artículo en concreto de cada proveedor al que se le ha comprado.

cualquier prueba que hago me funciona hasta que añado GROUP BY a la sentencia, entonces ya no me funciona.

la única forma que me ha funcionado el GROUP BY es añadiendo todos los campos:

SELECT ARTICULO,NOMBRE,CODPRO,FECHA,PRECIO
FROM facplin.dbf
WHERE ARTICULO = "1"
GROUP BY CODPRO,ARTICULO,NOMBRE,FECHA,PRECIO

pero no lo veo lógico, es una restricción de ADO?

Gracias de antemano por tu tiempo y atención.

Un saludo.

[David Field]

Hola Victor,

Lo que quieres saber es: 

Cuánto me costó el artículo la última vez que lo compré?

Por lo tanto:

SELECT ARTICULO,NOMBRE,CODPRO,MAX(FECHA),PRECIO

FROM facplin.dbf
WHERE ARTICULO = "1"

GROUP BY CODPRO,ARTICULO,NOMBRE,PRECIO

Utilizas la función MAX() para que te devuelva la de mayor fecha y en el GROUP BY no tienes que especificar ningún campo que tenga función asignada.

Ojo: al realizarlo como tu presentas, sin MAX(Fecha) y con todos los campos especificados en el GROUP BY no te garantiza que te devuelva el último precio de compra.

Probablemente te funcionó por que al azar decidió regresarte el último registro, pero pudiera no ser la última compra.

Esto no es asunto de ADO, es del DRIVER que usas con ADO, por eso mencioné anteriormente Microsoft.

Quizá no lo veas lógico pero piénsalo... si solo quisieras agruparlo por codpro entonces te debería regresar todos los ARTICULO, NOMBRE que es ilógico si solo deseas el MAX(Fecha), ...un solo registro, por lo tanto agrúpalos por codpro, articulo, nombre y precio (esto te daría todos los precios a los que compraste por que precio es la única diferencia) y de esos dame el de mayor fecha.

Es cosa de sintaxis, como dije, MySql es más flexible y Microsoft es muy cuadrado (aunque correcto)

Saludos,

David Field

P.D. Si estás utilizando ADO para tus sistemas con DBF's estás a una nada de convertirlo TODO a SQL y si ya estás usando MySQL te habrás dado cuenta de la enorme diferencia en velocidad y seguridad que provee.

[Victor Casajuana Mas]

Hola David.

Gracias por tu extensa respuesta.

Lo primero que he hecho es probar el tema de las diferencias entre MySql y SQL. En www.w3schools.com explican con ejemplos ambas, y en efecto, no funcionan igual:

En MySql esto funciona:

SELECT COUNT(CustomerID), Country, City
FROM Customers
GROUP BY Country;

En SQL No:

Error in SQL:
You tried to execute a query that does not include the specified expression 'City' as part of an aggregate function.

por lo que veo, en SQL hay que incluir todos los campos del SELECT en el GROUP BY, menos los que se aplican a funciones ( Count(), Max(), Min(), Sum(), Avg() )

Volviendo a la consulta que me pasaste:

SELECT ARTICULO,NOMBRE,CODPRO,MAX(FECHA),PRECIO
FROM facplin.dbf
WHERE ARTICULO = "1"
GROUP BY CODPRO,ARTICULO,NOMBRE,PRECIO

Me funciona bien, pero realmente necesito que me devuelva solo 1 línea con la última compra de cada proveedor, en este caso me devuelve todas las compras realizadas al proveedor, no me lo agrupa por proveedor. Creo que mi problema es que estoy muy acostumbrado a MySql, llevo varios años trabajando con MySql por el tema de webs y ahí si que me devuelve un registro por cada proveedor con la compra de la fecha más reciente.

partiendo de la misma información:

CREATE TABLE IF NOT EXISTS `facplin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ARTICULO` char(10) NOT NULL,
  `NOMBRE` char(10) NOT NULL,
  `CODPRO` int(10) NOT NULL DEFAULT 0,
  `FECHA` date NOT NULL,
  `PRECIO` float NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

INSERT INTO `facplin` (`id`, `ARTICULO`, `NOMBRE`, `CODPRO`, `FECHA`, `PRECIO`) VALUES
(1, '1', 'UNO', 1, '2024-02-20', 10),
(2, '1', 'UNO', 1, '2024-02-20', 11),
(3, '1', 'UNO', 2, '2024-02-20', 12),
(4, '1', 'UNO', 1, '2024-02-21', 13),
(5, '2', 'DOS', 1, '2024-02-22', 13),
(6, '2', 'DOS', 2, '2024-02-21', 13),
(7, '1', 'UNO', 1, '2024-02-22', 13),
(8, '2', 'DOS', 1, '2024-02-21', 13),
(9, '1', 'UNO', 2, '2024-02-22', 13);

necesito obtener la siguiente información:

ARTICULO,NOMBRE,CODPRO,FECHA,      PRECIO

1,       UNO,        1,2024-02-22, 10
1,       UNO,        2,2024-02-22, 12

Con la misma consulta en MySql la obtengo pero en SQL me aparecen más registros, no me los agrupa por el código de proveedor.

Supongo que es por mi poco conocimiento de SQL, iré haciendo averiguaciones y creo que es hora de una formación en SQL para poder avanzar.

P.D.: Actualmente trabajo mis DBF's sin ADO. No es viable cambiarlo todo a SQL ya que tengo mucho escrito y hay demasiada herencia tecnológica para un cambio de esta magnitud, pero me gustaría ir implementando ADO en todo lo nuevo que escribo y el código "legacy" que voy modificando.

Salud!

[Victor Casajuana Mas]

He estado revisando la información del GROUP BY en MSSQL y ya lo tengo claro, todo el problema lo tengo en la consulta, he de afinarla. Como comentas, MSSQL es más estricto y te obliga a hacer las cosas "bien"
Toca investigar un poco, cuando tenga la consulta correcta la pondré en el hilo por si puedo ayudar al que llegue hasta aquí.

Gracias!

[David Field]

Victor, 

Pudieras postear la consulta tal y como la tienes y los datos que te devuelve por favor?

Esto hará más fácil descifrar en donde está el problema.

Saludos,

David Field

[Victor Casajuana Mas]

Hola David.

Gracias por tu interés.

Teniendo en cuenta que la necesidad es saber a que último precio se ha comprado un artículo a cada proveedor y partiendo de una tabla con la información de las compras a los proveedores:

INSERT INTO `facplin` (`id`, `ARTICULO`, `NOMBRE`, `CODPRO`, `FECHA`, `PRECIO`) VALUES
(1, '1', 'UNO', 1, '2024-02-20', 10),
(2, '1', 'UNO', 1, '2024-02-20', 11),
(3, '1', 'UNO', 2, '2024-02-20', 12),
(4, '1', 'UNO', 1, '2024-02-21', 13),
(5, '2', 'DOS', 1, '2024-02-22', 13),
(6, '2', 'DOS', 2, '2024-02-21', 13),
(7, '1', 'UNO', 1, '2024-02-22', 13),
(8, '2', 'DOS', 1, '2024-02-21', 13),
(9, '1', 'UNO', 2, '2024-02-22', 13);

La consulta que tengo ahora es:

SELECT CODPRO,MAX(FECHA),PRECIO

FROM facplin.dbf
WHERE ARTICULO = "1"

GROUP BY CODPRO,PRECIO

pongo el campo de PRECIO en el select ya que necesito este valor, entonces lo tengo que poner también en el GROUP BY, esto hace que la consulta que me devuelva sea:

que en teoría es correcta, ya que a parte de la agrupación por proveedor, también me lo agrupa por precio y me devuelve una línea de cada precio dentro del mismo proveedor. 

El tema es que yo necesito que solo me devuelva la línea de la fecha más próxima y omita el resto.

El resultado correcto sería:

1  02/22/24  13

2  02/22/24  13

GRACIAS!

[Nippur Lagash]

Hola Victor, tal vez no aplique para dbf ya que lo probé en mysql sacando del group by el precio trae 1 solo registro por proveedor

Saludos,

Fer.MDQ

--
Has recibido este mensaje porque estás suscrito al grupo "ADO Harbour" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a ado-harbour...@googlegroups.com.
Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/ado-harbour/77c2d86f-da9d-4925-9720-845172360e94n%40googlegroups.com.

--

Nippur
Mar del Plata
Buenos Aires
Argentina

https://mozit.com.ar

[Victor Casajuana Mas]

Exacto Nippur, después de los consejos de David, busqué más información sobre MSSQL, en concreto sobre GROUP BY y el funcionamiento no es igual que en MySql, MSSQL es más estricto y lógico ( gracias David )

https://learn.microsoft.com/es-es/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16

[Nippur Lagash]

Hacía  el comentario, porque si en mysql pongo el mismo GROUP BY que vos, me trae más  registros, como que ignora el max(fecha).

El problema con MSSQL es que no respeta el Estándar ANSI SQL, el lenguaje que utiliza es T-SQL.

Saludos,

Fer.MDQ

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/ado-harbour/cc147abf-005c-42fa-bfdf-f75d3317aef9n%40googlegroups.com.

[Victor Casajuana Mas]

Yo no le veo tanto como un "problema", creo que es más lógico MSSQL, ya que MySql, al hacer la consulta agrupando solo por CODPRO, muestra el dato del PRECIO que se podría considerar "al azar", ya que muestra el primero que le parece ( supongo que siguiendo la lógica de ordenación correspondiente ), en cambio MSSQL detecta esta incongruencia y antes de mostrar un dato que es 100% correcto, te tira atrás la consulta. Entonces para mostrar el precio te obliga a meter el campo PRECIO en el GROUP BY, y como es lógico, después de la agrupación de CODPRO, lo agrupa por PRECIO y al haber varios precios diferentes en el mismo proveedor pues saca varias líneas. Estoy haciendo algunas pruebas para ver si encuentro una solución "fácil" que no pase por subconsultas par no penalizar el rendimiento

[David Field]

Hola Victor,

Si te fijas en la respuesta que te provee MySQL, te está regresando el primer registro encontrado, no necesariamente el último que por lógica y aunque tuviera la misma fecha debería ser la última compra.

Ahora, esto no es culpa de MySQL sino tal vez de diseño, esto se evitaría agregando a la fecha la hora... pero pues no la tiene así que nos toco a nosotros pensar o resolverle el problema a SQL por lo que sugiero agregues el ID como parte del query y de esta forma.

SELECT codpro, articulo, precio FROM facplin.dbf WHERE ID IN

(SELECT MAX(ID) FROM facplin.dbf

WHERE ARTICULO = "1" GROUP BY CODPRO)

Esto aún pudiera ser problemático si se captura una factura anterior de forma que quedara con un ID mayor que las otras asi que...

SELECT codpro, articulo, precio FROM facplin.dbf WHERE ID IN

   (SELECT MAX(ID) FROM 

      (SELECT id, MAX(fecha), codpro FROM facplin.dbf

       WHERE ARTICULO = "1" GROUP BY ID, CODPRO) a

   GROUP BY CODPRO)

ORDER BY CODPRO

Espero esto resuelva tu problema,

Saludos,

David Field

[David Field]

Mi última respuesta TAMBIEN está mal, si se agrega un registro al final con fecha anterior a la maxima, entonces regresa ese registo.

Aquí mi última solución:

SELECT codpro, fecha, articulo, precio FROM
(SELECT ROW_NUMBER() over (PARTITION BY codpro ORDER BY fecha DESC) id,
codpro, fecha, articulo, precio FROM facplin) a
WHERE id = 1

Esto en realidad no debe ser más lento ya que sucede en memoria.

Saludos,

David Field

[David Field]

Y aún para asegurarnos que nos devuelva el ULTIMO registro de la fecha

SELECT ROW, id, codpro, fecha, articulo, precio FROM
(SELECT ROW_NUMBER() over (PARTITION BY codpro ORDER BY fecha DESC, id DESC) ROW,
id, codpro, fecha, articulo, precio FROM facplin WHERE articulo = '1') a
WHERE ROW = 1

Aunque regresa lo mismo que el anterior, creo que agregando al orden el ID para que se organize por Fecha y ID de forma descendente asegura tener el último registro para la última fecha.

Agregué algunos campos para hacer más fácil el que se pudiera ver los registros seleccionados por el query.

Saludos,

David Field

[Victor Casajuana Mas]

Madre mía David, cuanta info!!!!

lo que me estoy dando cuenta es que si quiero hacer cosas avanzadas, los conocimientos que tengo de SQL basados en MySql no los puedo aplicar al SQL de ADO, por lo que antes de hacer un copypega de tu consulta ( que por cierto te lo agradezco mucho) voy a ver como aprendo las diferencias entre MySql y MSSql. Si quiero ir avanzando e implementando SQL en mi programa para trabajar con DBF hay que formarse.

Para no liarme, simplemente un par de consultas ( aunque creo que la primera ya sé la respuesta )

1) Con Harbour y utilizando ADO, hay alguna forma de poder trabajar con lasinstrucciones MySql?

2) Me recomiendas alguna formación de MSSql que no empiece desde cero?

Muchas gracias!!!!

[Nippur Lagash]

Hola Víctor, acá pongo algunos links:

SQL Ansi vs T-SQL

Escritura de consultas Transact-SQL avanzadas

Manual SQL Server –Transact SQL Básico /Avanzado

Espero que te sirvan de algo.

Saludos,

Fer.MDQ

--

Has recibido este mensaje porque estás suscrito al grupo "ADO Harbour" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a ado-harbour...@googlegroups.com.

Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/ado-harbour/9a261828-3b1f-4f2f-aeae-903aa47fe7c7n%40googlegroups.com.

[David Field]

Victor, 

No te asustes. 

Yo no sabia nada de SQL hasta que lo empecé a usar y son pocas las diferencias entre mysql y mssql. 

Buscando soluciones en Internet encuentras lo que necesites.

Y de ahí vas ampliando tus conocimientos. 

Saludos 

David Field