Error in dataset result query with UNION

Questions, tips and tricks and techniques for scripting in Servoy

Error in dataset result query with UNION

Postby giovanni.zola » Wed Mar 06, 2013 7:28 pm

I created a dataset with this query contains the UNION operator then save to an HTML file.
Code: Select all
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
giovanni.zola
 
Posts: 3
Joined: Thu Feb 07, 2013 6:53 pm

Re: Error in dataset result query with UNION

Postby giovanni.zola » Thu Mar 07, 2013 1:09 pm

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

Code: Select all
         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
giovanni.zola
 
Posts: 3
Joined: Thu Feb 07, 2013 6:53 pm


Return to Methods

Who is online

Users browsing this forum: No registered users and 5 guests