subquery support?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

subquery support?

Post by dooh » Fri 06 Jul 2007 21:17

I get error with this query:

SELECT entradas.matricula, entradas.modelo, mov_venta.cliente, mov_venta.f_reserva, mov_venta.f_confirmacion, mov_taller.f_prevision,
mov_taller.f_reparado, talleres.taller, status_peritaje.importe,
(SELECT COUNT(repair_incidencias.incidencia_id) AS counter
FROM repair_incidencias
WHERE repair_incidencias.matricula = mov_venta.matricula AND repair_incidencias.completado = 'False') AS desperfectos
FROM entradas RIGHT OUTER JOIN
mov_taller INNER JOIN
talleres ON mov_taller.taller_id = talleres.taller_id RIGHT OUTER JOIN
mov_salidas RIGHT OUTER JOIN
mov_venta ON mov_salidas.matricula = mov_venta.matricula LEFT OUTER JOIN
status_peritaje ON mov_venta.matricula = status_peritaje.matricula ON mov_taller.matricula = mov_venta.matricula ON
entradas.matricula = mov_venta.matricula
WHERE (mov_salidas.matricula IS NULL) AND (mov_venta.f_cancelado IS NULL) AND (mov_venta.profesional = 'True') AND (entradas.campa_id = 1)
GROUP BY mov_venta.venta_id
Last edited by dooh on Tue 10 Jul 2007 15:01, edited 2 times in total.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 09 Jul 2007 07:57

Curiously enough, me too.
Would you like to provide me with your table definitions, please?

dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

Post by dooh » Mon 09 Jul 2007 13:56

For your information, if i put this query into a store procedure it works perfectly, but i used a TableAdapter, i get the error.

I Will try send to you the tables definitions.

But please, by your way, try to get the solution.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 10 Jul 2007 08:27

Unfortunately, I can't reproduce any problem without your database structure (and probably without your data). So I'm looking forward to your feedback.

dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

Post by dooh » Tue 10 Jul 2007 15:09

CREATE TABLE `entradas` (
`matricula` varchar(10) NOT NULL,
`completada` varchar(5) default 'false',
`f_entrada` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`matricula`),
UNIQUE KEY `matricual_key` (`matricula`),
KEY `fbk_campa_id` (`campa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mov_taller` (
`movimiento_id` int(7) NOT NULL auto_increment,
`matricula` varchar(10) NOT NULL default '',
`taller_id` int(7) NOT NULL,
`f_asignacion` datetime default NULL,
`f_prevision` datetime default NULL,
`f_entrada` datetime default NULL,
`f_reparado` datetime default NULL,
`f_entrega` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`movimiento_id`),
KEY `matricula` (`matricula`),
KEY `mov_taller_ibfk_2` (`taller_id`),
KEY `mov_taller_ibfk_3` (`userid`),
CONSTRAINT `mov_taller_ibfk_1` FOREIGN KEY (`matricula`) REFERENCES `entradas` (`matricula`) ON UPDATE CASCADE,
CONSTRAINT `mov_taller_ibfk_2` FOREIGN KEY (`taller_id`) REFERENCES `talleres` (`taller_id`) ON UPDATE CASCADE,
CONSTRAINT `mov_taller_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `aspnet_users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `repair_incidencias` (
`incidencia_id` int(7) NOT NULL auto_increment,
`accion_id` int(7) NOT NULL,
`desperfecto_id` int(7) NOT NULL,
`localizacion_id` int(7) NOT NULL,
`observaciones` varchar(255) default NULL,
`matricula` varchar(10) NOT NULL,
`importe` double(7,2) NOT NULL default '0.00',
`recambio` varchar(5) default 'false',
`completado` varchar(5) NOT NULL default 'false',
`userid` varchar(40) NOT NULL,
`f_updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`incidencia_id`),
KEY `matricula` (`matricula`),
KEY `userid` (`userid`),
CONSTRAINT `repair_incidencias_ibfk_3` FOREIGN KEY (`matricula`) REFERENCES `entradas` (`matricula`) ON UPDATE CASCADE,
CONSTRAINT `repair_incidencias_ibfk_4` FOREIGN KEY (`userid`) REFERENCES `aspnet_users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



SELECT entradas.campa_id, entradas.f_entrada, entradas.matricula, mov_taller.f_entrega,
(SELECT COUNT(repair_incidencias.incidencia_id)
FROM repair_incidencias
WHERE repair_incidencias.matricula = entradas.matricula) AS desperfectos
FROM entradas LEFT OUTER JOIN
mov_taller ON entradas.matricula = mov_taller.matricula

If i run this query directly in to any MySQL Query, i get no errors and i get the correct result, but if i run this query into a tableadapter with corelab, i get the follow error:

Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 11 Jul 2007 14:26

Key column 'campa_id' doesn't exist in table
Key column 'userid' doesn't exist in table
Please provide me with valid table definitions.

dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

Post by dooh » Wed 11 Jul 2007 17:53

The same without the reference keys.


CREATE TABLE `entradas` (
`matricula` varchar(10) NOT NULL,
`completada` varchar(5) default 'false',
`f_entrada` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`matricula`),
UNIQUE KEY `matricual_key` (`matricula`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mov_taller` (
`movimiento_id` int(7) NOT NULL auto_increment,
`matricula` varchar(10) NOT NULL default '',
`taller_id` int(7) NOT NULL,
`f_asignacion` datetime default NULL,
`f_prevision` datetime default NULL,
`f_entrada` datetime default NULL,
`f_reparado` datetime default NULL,
`f_entrega` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`movimiento_id`),
KEY `matricula` (`matricula`),
KEY `mov_taller_ibfk_2` (`taller_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `repair_incidencias` (
`incidencia_id` int(7) NOT NULL auto_increment,
`accion_id` int(7) NOT NULL,
`desperfecto_id` int(7) NOT NULL,
`localizacion_id` int(7) NOT NULL,
`observaciones` varchar(255) default NULL,
`matricula` varchar(10) NOT NULL,
`importe` double(7,2) NOT NULL default '0.00',
`recambio` varchar(5) default 'false',
`completado` varchar(5) NOT NULL default 'false',
`userid` varchar(40) NOT NULL,
`f_updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`incidencia_id`),
KEY `matricula` (`matricula`),
KEY `userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 12 Jul 2007 15:23

I've finally reproduced this problem.
We will investigate it and I'll let you know the results.

dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

Post by dooh » Sun 22 Jul 2007 08:26

Alexy,

Do have result this issue?

Pls, is so important to me get this query to works

Regards

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 23 Jul 2007 06:37

The problem appears to be complicated, and finding a solution may take a lot of time.
Can't you use our Dataset Wizard to create that query?

dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

Post by dooh » Mon 23 Jul 2007 07:21

Right now all my dataset are created by the Normal DataSet Wizard!

Do you want that i try with MySQLDirect DataSetWizard?

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 23 Jul 2007 07:34

I suggested that just as work-around.
If standard dataset wizard raises the error and we cannot fix it right now, then you can currently use MySQLDirect .NET DataSet Wizard.

dooh
Posts: 30
Joined: Sun 14 Jan 2007 21:11

Post by dooh » Mon 23 Jul 2007 07:49

The same!

What can i do?

This issue represent that i cant show the data request by my clients!.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 23 Jul 2007 08:55

What do you mean the same? I've just successfully created dataset using your query. What version of MySQLDirect .NET are you using?

Post Reply