Error in dataset result query with UNION

Classic Servoy
1

I created a dataset with this query contains the UNION operator then save to an HTML file.

var query = "SELECT num_sezione AS 'Sez. n.', last_maschi AS 'M. iscritti', last_femmine AS 'F. iscritte', " +
						"maschi_meno AS 'M -', femmine_meno AS 'F -', maschi_piu AS 'M +', femmine_piu AS 'F +', " +
						"tot_maschi AS 'Tot. Maschi', tot_femmine AS 'Tot. Femmine', tot_elettori AS 'Tot. Elettori' " +
						"FROM sez_revisione WHERE fkid_comune = 23 AND fkid_rev_comune = 15 " +
						" UNION " + 
						"SELECT 'TOTALE', SUM(last_maschi), SUM(last_femmine), " + 
						"SUM(maschi_meno), SUM(femmine_meno), " +
						"SUM(maschi_piu), SUM(femmine_piu), " +
						"SUM(tot_maschi), SUM(tot_femmine), SUM(tot_elettori) " +
						"FROM sez_revisione WHERE fkid_comune = 23 AND fkid_rev_comune = 15"
						
			var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, null, -1);
			var htmlTable = dataset.getAsHTML(true, true, false, true, true);
			var success = plugins.file.writeTXTFile("c:\\Temp\\tableData.htm", htmlTable);

The result is visible in the html table here.
http://www.comune.nuoro.it/varieweb/tabledata.htm

As you can see the column “Sec. N.” has an error. This does not happen by running two separate queries joined by UNION but even if you delete the part of the query “SELECT ‘TOTAL’” and it is changed, for example, with “SELECT 1”.

Has anyone had such a problem and, if so, how it resolved?

Thanks.

Giovanni Zola
Comune di Nuoro
Servoy 5.2.8
MySql 5

2

I solved with a couple of lines of code as follows:

			var query = "SELECT num_sezione AS 'Sez. n.', last_maschi AS 'M. iscritti', last_femmine AS 'F. iscritte', " +
						"maschi_meno AS 'M -', femmine_meno AS 'F -', maschi_piu AS 'M +', femmine_piu AS 'F +', " +
						"tot_maschi AS 'Tot. Maschi', tot_femmine AS 'Tot. Femmine', tot_elettori AS 'Tot. Elettori' " +
						"FROM sez_revisione WHERE fkid_comune = " + globals.currComuneId + " AND fkid_rev_comune = " + rev_comune_id +
						" UNION " + 
						"SELECT 170757, SUM(last_maschi), SUM(last_femmine), " + 
						"SUM(maschi_meno), SUM(femmine_meno), " +
						"SUM(maschi_piu), SUM(femmine_piu), " +
						"SUM(tot_maschi), SUM(tot_femmine), SUM(tot_elettori) " +
						"FROM sez_revisione WHERE fkid_comune = " + globals.currComuneId + " AND fkid_rev_comune = " + rev_comune_id
						
			var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, null, -1);
			var htmlTable = dataset.getAsHTML(true, true, false, true, true);
			
			htmlTable = utils.stringReplace(htmlTable, '170757', 'TOTALI')

‘SELECT 170757’ is just a placeholder. It 's my birthday
:-D