Issue with lookup

[Giu]

Hi,

I'm struggle several hours with an issue I don't understand. I have a launch form with a lookup. This lookup points to a Browse with this SQL

Code: Select all

SELECT zzzzsys_user_id,
sus_code,
sus_name,
puntos
 FROM (SELECT
    zzzzsys_user.zzzzsys_user_id,
zzzzsys_user.sus_code,
    zzzzsys_user.sus_name,
    fichas_grm.puntos,
    (fichas_grm.puntos - (SELECT fichas_grm.puntos 
                          FROM fichas_grm 
                          WHERE fichas_grm.usuario = '#USER_ID#')) AS diferencia_puntos
FROM
    zzzzsys_user
JOIN
    fichas_grm ON zzzzsys_user.zzzzsys_user_id = fichas_grm.usuario
WHERE
fichas_grm.usuario <> '#USER_ID#' AND 
    (ABS(fichas_grm.puntos -(SELECT fichas_grm.puntos 
                          FROM fichas_grm 
                          WHERE fichas_grm.usuario = '#USER_ID#')) <= 125)) t order by diferencia_puntos asc

Browse opens and show the data perfectly, but when I choose a record to be returned, lookup keeps blank, and an error is recorded under debug.

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 7

===SQL===========

SELECT
zzzzsys_user_id,
sus_code,
sus_name
 FROM (SELECT     zzzzsys_user.zzzzsys_user_id, zzzzsys_user.sus_code,     zzzzsys_user.sus_name,     fichas_grm.puntos,     (fichas_grm.puntos - (SELECT fichas_grm.puntos                            FROM fichas_grm                           
WHERE
`zzzzsys_user_id` = ?

===BACK TRACE====

/home/vol1_3/infinityfree.com/if0_36561587/htdocs/dansu/core/nuform.php - line 797 (nuRunQuery)

/home/vol1_3/infinityfree.com/if0_36561587/htdocs/dansu/core/nuform.php - line 760 (nuGetLookupValues)

/home/vol1_3/infinityfree.com/if0_36561587/htdocs/dansu/core/nuapi.php - line 99 (nuGetAllLookupValues)

Some idea?

[Giu]

Ok, right now I solved it removing the wraping SELECT. Don't remember why I use it but I think it was for some kind of error. I think I added to be able to order by the calc