I am trying to pivot my data from a table for use in some components, but to do so required using multiple SQL statements to get the data into the format I needed. I am able to run this just fine directly against my DB, but not with Ignition (through the Database Query browser or the data binding on the component).
Here is my SQL code:
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(recipe_name = "', recipe_name, '", viscosity_average, NULL)) AS "', recipe_name, '"' ) ) INTO @sql FROM pm_viscosity_report WHERE recipe_name IS NOT NULL AND recipe_name != ''; SET @sql = CONCAT( 'SELECT date_end, ', @sql, ' FROM pm_viscosity_report WHERE date_end IS NOT NULL GROUP BY date_end' ); EXECUTE IMMEDIATE @SQL;
Is this not possible directly? If not, I think I may have to resort to letting the client do this in scripting.
The error I get is:
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: 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 'SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(recipe_name = "', recipe_n' at line 3
I’ve been hacking at this for some time and just cannot figure out what to do. I know these forums are full of people way smarter than me, so I thought I’d see what you all come up with.