Advanced SQL issue

[juergen_mueller]

Hello

I'm using an Advanced SQL query like this one:

---
Select year AS Jahr,sum( expense ) AS Betriebskosten, (Select share FROM flats WHERE user_group = 2 ) AS Anteil,(Select flat FROM flats WHERE user_group = 2 ) AS Wohnung,ROUND( sum(( Select SHARE FROM flats WHERE user_group = 2 )* expense ), 2 ) AS Betrag,ROUND(( Select sum( income ) FROM transactions WHERE user_id = 2 AND cost_center_id = 1 ), 2 ) AS Einzahlung,ROUND((Select sum( income ) FROM transactions WHERE user_id = 2 AND cost_center_id = 1 ) - sum(( Select share FROM flats WHERE user_group = 2 )* expense ),2) AS Differenz FROM transactions WHERE cost_center_id = 1
---

It works apart from the last statement (Differenz) ; the result is not shown.

However, when using the same query for creating a view or a query in my MySQL application (Navicat) I get everything as expected.

This is my DaDaBik setup:

Your current DaDaBIK version​

You are using DaDaBIK version 11.9-Elba platinum, installed on 24.03.2023 (installation code: 18715640b0045366e6), the latest version of DaDaBIK is 11.9-Elba released on 23.03.2023

You are running the latest release of DaDaBIK

In case you want to upgrade to a more powerful edition (from Pro to Enterprise/Platinum, from Enterprise to Platinum) please contact us.

System info​

PHP Version: 8.1.16

mysql version: 10.5.18-MariaDB-0+deb11u1-log

Web server: Apache

Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.4 Safari/605.1.15



Tanks for any hint,

Juergen

PS: $disable_mysql_multiple_statements = 0;

 

[eugenio]

Hello Juergen,
are you using it for a Pivot table? If yes, at the moment there is a max number of columns handled (5), that's probably the reason.
$disable_mysql_multiple_statements does not impact on this and I suggest for security reasons to keep it 1.

Best,

 

[juergen_mueller]

Hi Eugenio

Thanks for your reply. Yes, it's for Pivot table. Are you planning to increase the number of columns? Meanwhile, I can manage to use a view instead for the time being.

Best, Juergen

 

[eugenio]

Hi,
I am pretty sure that the limit is only due to historical reasons so yes, at some point I will increase it.

best,