in an application type GRID when the topic query contains a subquery (SELECT COUNT()…) for example:
SELECT
sc.Codice,
sc.Citta,
(SELECT COUNT() FROM Magaz m WHERE m.CodSito=sc.Codice) AS numdis,
c.RagSoc
FROM SitCli sc
in ADVANCED SEARCH the fields type text with the flag on USE AUTOCOMPLETE, it generates ERROR:
Query: select distinct numdis from (SELECT sc.Codice, (SELECT COUNT() FROM Magaz m WHERE m.CodSito=sc.Codice) AS numdis, c.RagSoc ,sc.Citta FROM SitCli sc LEFT JOIN Clienti c ON c.Codice=sc.CodCliente LEFT JOIN mio_filtri mf ON mf.codice=sc.mio_categoria LEFT JOIN mio_filtri mf1 ON mf1.codice=sc.mio_codfiltro1 LEFT JOIN mio_filtri mf2 ON mf1.codice=sc.mio_codfiltro2 WHERE sc.CodCliente>0 ) nm_sel_esp where distinct numdis = ‘’ failed. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘distinct numdis = ‘’’ at line 1
1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘distinct numdis = ‘’’ at line 1
ADOConnection._Execute(select distinct numdis from (SELECT sc.Codice, (SELECT COUNT() FROM Magaz m WHERE m.CodSito=sc.Codice) AS numdis, c.RagSoc ,s…, false)[SIZE=-1] % line 1095, file: adodb.inc.php[/SIZE] ADOConnection.Execute(select distinct numdis from (SELECT sc.Codice, (SELECT COUNT() FROM Magaz m WHERE m.CodSito=sc.Codice) AS numdis, c.RagSoc ,s…)[SIZE=-1] % line 1950, file: test_111_pesq.class.php[/SIZE] test_111_pesq.monta_form()[SIZE=-1] % line 82, file: test_111_pesq.class.php[/SIZE] test_111_pesq.monta_formulario()[SIZE=-1] % line 71, file: test_111_pesq.class.php[/SIZE] test_111_pesq.monta_busca()[SIZE=-1] % line 2220, file: index.php[/SIZE] [TABLE=“class: scErrorTable, align: center, cellpadding: 0, cellspacing: 0, width: 1, height: 30”]
[TR]
[TD=“class: scErrorTitle, align: left”]Errore[/TD]
[/TR]
[TR]
[TD=“class: scErrorMessage, align: center”]Errore durante l’accesso alla banca dati:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘distinct numdis = ‘’’ at line 1
select distinct numdis from (SELECT sc.Codice, (SELECT COUNT() FROM Magaz m WHERE m.CodSito=sc.Codice) AS numdis, c.RagSoc ,sc.Citta FROM SitCli sc LEFT JOIN Clienti c ON c.Codice=sc.CodCliente LEFT JOIN mio_filtri mf ON mf.codice=sc.mio_categoria LEFT JOIN mio_filtri mf1 ON mf1.codice=sc.mio_codfiltro1 LEFT JOIN mio_filtri mf2 ON mf1.codice=sc.mio_codfiltro2 WHERE sc.CodCliente>0 ) nm_sel_esp where distinct numdis = ‘’[/TD]
[/TR]
[/TABLE]
From our analysis, the error is due to a wrong compilation of the query (mysql) because running the query with adminquery returns an error:
2 Every derived table must have its own alias SQL3.sql 1 1