Lookup Fails When Casting Id To An Integer.

[GirdUpYourLoins]

I have the following code which is supposed to for each through a database table and retrieve records based on id:

FOR EACH Proj_Mstr WHERE Proj_Mstr.Proj_client_ID = INTEGER(get-value("clientId")) AND
Proj_Mstr.Proj_deleted = ? NO-LOCK BREAK BY Proj_Mstr.Proj_sort:

jObj:add(STRING(projNumber), Proj_Mstr.Proj_name).
projNumber = projNumber + 1.
END. /* admin 4ea */

There are records that match the criteria, but no records are returned. If I change the variable in the search criteria to a literal, all appropriate records are returned:

FOR EACH Proj_Mstr WHERE Proj_Mstr.Proj_client_ID = 1 AND
Proj_Mstr.Proj_deleted = ? NO-LOCK BREAK BY Proj_Mstr.Proj_sort:

jObj:add(STRING(projNumber), Proj_Mstr.Proj_name).
projNumber = projNumber + 1.
END. /* admin 4ea */

When I test to see if INTEGER(get-value("clientId")) = 1, the return value is true. Why does casting the string to an integer cause the lookup to fail?

 

[GirdUpYourLoins]

How silly. Assigning the html parameter to a variable first fixed the problem.

clientId = get-value("clientId").

FOR EACH Proj_Mstr WHERE Proj_Mstr.Proj_client_ID = INTEGER(clientId) AND
Proj_Mstr.Proj_name <> "Regular / Salary" AND
Proj_Mstr.Proj_deleted = ? NO-LOCK BREAK BY Proj_Mstr.Proj_sort:

jObj:add(STRING(projNumber), Proj_Mstr.Proj_name).
projNumber = projNumber + 1.
END. /* admin 4ea */

 

[jdpjamesp]

Your query will most likely be more efficient with the second example anyway.

 

[Marco Mendoza]

I have the following code which is supposed to for each through a database table and retrieve records based on id:

FOR EACH Proj_Mstr WHERE Proj_Mstr.Proj_client_ID = INTEGER(get-value("clientId")) AND
Proj_Mstr.Proj_deleted = ? NO-LOCK BREAK BY Proj_Mstr.Proj_sort:

jObj:add(STRING(projNumber), Proj_Mstr.Proj_name).
projNumber = projNumber + 1.
END. /* admin 4ea */

There are records that match the criteria, but no records are returned. If I change the variable in the search criteria to a literal, all appropriate records are returned:

FOR EACH Proj_Mstr WHERE Proj_Mstr.Proj_client_ID = 1 AND
Proj_Mstr.Proj_deleted = ? NO-LOCK BREAK BY Proj_Mstr.Proj_sort:

jObj:add(STRING(projNumber), Proj_Mstr.Proj_name).
projNumber = projNumber + 1.
END. /* admin 4ea */

When I test to see if INTEGER(get-value("clientId")) = 1, the return value is true. Why does casting the string to an integer cause the lookup to fail?

get-value is an user-defined function. As is pointed on this article, the results can be unpredictable Progress KB - User Defined Functions (UDF) in WHERE clauses (FIND , CAN-FIND or FOR EACH) can be unpredictable