+- CoreBOSBB (https://discussions.corebos.org)
+-- Forum: Support (https://discussions.corebos.org/forumdisplay.php?fid=17)
+--- Forum: User Support (https://discussions.corebos.org/forumdisplay.php?fid=6)
+--- Thread: comando sql (/showthread.php?tid=872)
comando sql - gmarcog - 01-30-2018
Hola a todos:
Estoy intentando crear un informe con un comando sql, pero no me admite la clausula WHERE. Si la sentencia mysql es, por ejemplo, SELECT * FROM `vtiger_accountscf` WHERE `nombre` = 'guillermo' no me devuelve ningun valor, sin embargo si es SELECT * FROM `vtiger_accountscf` me devuelve valores (todos los de la tabla). Esas sentencias tienen una sintaxis especial? he probado con y sin comillas, pero no me devuelve nada
Gracias y saludos
RE: comando sql - joebordes - 01-30-2018
Hi,
This functionality is still a WIP.
For the reporting extension to work correctly with the SQL command there are some rules that must be respected.
- each column must have an alias that is the module name followed by the field label with all spaces substituted by underscores.
- the last column must be the crmid of the record of the row with an alias of LBL_ACTION
- you must select the main module of the SQL command when creating the report in order to support adhoc conditions. THIS IS IMPORTANT
- you cannot use conditions on groups (having)
Code:
select assetname as 'Assets_Asset_Name', serialnumber as 'Assets_Serial_Number', vtiger_crmentity.crmid AS "LBL_ACTION"
from vtiger_assets
inner join vtiger_crmentity on crmid=assetsid where assetname like 's%'As you can see there is a "where" condition and it will work if you select Assets as the main module of the direct SQL.
I have planned and already started a blog series on the reporting functionality which I will release soon.
Give it a try and let me know how it goes.
RE: comando sql - gmarcog - 01-31-2018
Hi, Joe
It works. Thank you. This idea of a blog series I think is fantastic. I hope read it soon
Regards