nuBuilder Forum

Posted: **Sat Apr 22, 2023 7:18 pm**

Hi,
the following SQL (a little bit complicated, yes) works smoothly in phpMyAdmin:

SELECT * FROM tblContratti cnt JOIN( SELECT can.* FROM tblCanoni can JOIN( SELECT can_id_contratto, MAX(can_data_decorrenza) AS latestDate FROM tblCanoni GROUP BY can_id_contratto) t ON t.can_id_contratto = can.can_id_contratto AND t.latestDate = can.can_data_decorrenza ) fpm ON fpm.can_id_contratto = cnt.cnt_contratti_id WHERE cnt.cnt_in_vigore=1;

When opening a form for browse, it is re-written in this way (from debug):

Code: Select all

SELECT cnt_contratti_id,cnt_nome_contratto,cnt_tipo_contratto
 FROM tblContratti cnt JOIN( SELECT can.* FROM tblCanoni can JOIN( SELECT can_id_contratto, MAX(can_data_decorrenza) AS latestDate FROM tblCanoni GROUP BY can_id_contratto) t ON t.can_id_contratto = can.can_id_contratto AND t.latestDate = can.can_data_decorrenza ) fpm ON fpm.can_id_contratto = cnt.cnt_contratti_id
 WHERE cnt.cnt_in_vigore=1
 GROUP BY can_id_contratto) t ON t.can_id_contratto = can.can_id_contratto AND t.latestDate = can.can_data_decorrenza ) fpm ON fpm.can_id_contratto = cnt.cnt_contratti_id WHERE cnt.cnt_in_vigore=1 LIMIT 0, 20

and obviously cause the error:

Code: Select all

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') t ON t.can_id_contratto = can.can_id_contratto AND t.latestDate = can.can_data' at line 4

Any help? Thanks

Posted: **Sat Apr 22, 2023 8:01 pm**

Try create view for your joins and them select with where clause from it. Seems nuBuilder code modifies select expression according to browse fields.

Posted: **Sat Apr 22, 2023 8:40 pm**

In addition to mih's suggestion, you may also utilise a temporary table as outlined here.

Another option is to use a subquery by encapsulating the SQL statement within a SELECT * FROM (...) subquery.

Posted: **Sat Apr 22, 2023 11:56 pm**

Yes, when proposing the view, I did not take into account that some providers do not allow you to create views, so temp table is more flexible.

Also, maybe it's more reasonable don't rewrite manually created SQL query ?

Posted: **Sun Apr 23, 2023 9:07 am**

Hi, solved using a view. Simple and straightforward.
Thank you, have a good sunday.

nuBuilder Forum

Problems with browse SQL

Problems with browse SQL

Re: Problems with browse SQL

Re: Problems with browse SQL

Re: Problems with browse SQL

Re: Problems with browse SQL