Hi,
I have the next main query of a grid app:
[query]
SELECT
a.id,
u.nombre,
u.apellidos,
a.dispositivo_id,
a.id_contract,
a.opened,
a.idag_open,
a.estado,
a.agente_id,
a.epoch,
a.lat,
a.lon,
a.precision_pos,
a.soltada,
a.fecha,
a.hora,
a.prioridad,
a.tiempo_transcurrido,
a.tiempo_prioridad,
a.grupo,
a.tipo,
a.subtipo,
a.finalizada,
a.transferida,
a.validada,
a.id_cra,
a.fecha_finalizada,
a.hora_finalizada,
a.ha_finalizado,
a.calle,
a.numero,
a.floor,
a.ciudad,
a.pais,
a.agente_validador,
a.sistema_operativo,
a.num_pulsaciones,
a.copiada,
a.fecha_transferida,
a.cra_original,
a.id_original,
a.type,
a.cardio,
a.batt,
a.created_at_offset,
a.tz_origin
FROM alarmas a INNER JOIN dispositivos d ON a.dispositivo_id=d.id INNER JOIN usuarios u ON u.id = IFNULL(a.id_user_original,d.usuario_id)
WHERE
1
[/query]
This query creates a grid. The field a.id_contract is a integer key marked as index that is a foreign key of another table and accepts null values (for non-associated rows with the “contracts” table).
The problem is that when I click on the magnifier glass icon on a row that contains a NULL value for its id_contract field, it appears the next query error:
Error
Se produjo un error al acceder a la base de datos
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 'and a.prioridad = 1 and a.opened = '0' and a.estado = 5 and a.finalizada = 1' at line 1
SELECT a.id as a_id, u.nombre as u_nombre, u.apellidos as u_apellidos, a.dispositivo_id as a_dispositivo_id, a.id_contract as a_id_contract, a.opened as a_opened, a.idag_open as a_idag_open, a.estado as a_estado, a.agente_id as a_agente_id, a.epoch as a_epoch, a.lat as a_lat, a.lon as a_lon, a.precision_pos as a_precision_pos, a.soltada as a_soltada, a.fecha as a_fecha, a.hora as a_hora, a.prioridad as a_prioridad, a.tiempo_transcurrido as a_tiempo_transcurrido, a.tiempo_prioridad as a_tiempo_prioridad, a.grupo as a_grupo, a.tipo as a_tipo, a.subtipo as a_subtipo, a.finalizada as a_finalizada, a.transferida as a_transferida, a.validada as a_validada, a.id_cra as a_id_cra, a.fecha_finalizada as a_fecha_finalizada, a.hora_finalizada as a_hora_finalizada, a.ha_finalizado as a_ha_finalizado, a.calle as a_calle, a.numero as a_numero, a.floor as a_floor, a.ciudad as a_ciudad, a.pais as a_pais, a.agente_validador as a_agente_validador, a.sistema_operativo as a_sistema_operativo, a.num_pulsaciones as a_num_pulsaciones, a.copiada as a_copiada, a.fecha_transferida as a_fecha_transferida, a.cra_original as a_cra_original, a.id_original as a_id_original, a.type as a_type, a.cardio as a_cardio, a.batt as a_batt, a.created_at_offset as a_created_at_offset, a.tz_origin as a_tz_origin from alarmas a INNER JOIN dispositivos d ON a.dispositivo_id=d.id INNER JOIN usuarios u ON u.id = IFNULL(a.id_user_original,d.usuario_id) where u.nombre = 'Pruebas' and u.apellidos = 'Dws' and a.id = 31 and a.hora = '16:18:56' and a.dispositivo_id = 1112 and a.id_contract = and a.prioridad = 1 and a.opened = '0' and a.estado = 5 and a.finalizada = 1
The search works fine for all that rows that contains an integer value in id_contract field.
As you can see, the where condition for a.id_contract is "a.id_contract = " instead of “a.id_contract IS NULL” or “a.id_contract = NULL”.
I’ve tried to force to set to NULL the id_contract field in onRecord event as the next code:
if(empty({a.id_contract})) {
{a.id_contract} = NULL;
}
But still doesn’t works, the result is the same error.
How can I fix this? I mean, passing NULL value instead empty for integer fields for the magnifier glass search?