Problems with browse SQL

[luca.ongaro]

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

Code: Select all

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

[mih]

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

[kev1n]

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.

[mih]

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 ?

[luca.ongaro]

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