SQLExec for Number Column

The Snag

Today I was hitting a problem using the SQLExec function with a query against a single number column.  I needed to retrieve a specific value for an employee and if no rows are found my program would skip this employee.  The logic seemed simple enough something like the following:

SQLExec(SQL.MCM_SNLF_BN_RCD_NBR, &employee, &EmplRcd);
If None(&benefitEmplRcd) Then
   MessageBox(0, "", 20002, 4, " No Employee Record skip employee");
   &record = Null;
   Return &record;
End-If;

BAD! 

When you run this code the query is against a Number column and that number column even if no record is found will return a 0 not a null and your IF condition will never be true.  Also if zero is a valid value for Employee record then code after using that value could execute against all the wrong data.

Solution

My solution to this is pretty easy simply include another key value of your query in my case Employee ID which will never be empty and also a String value.  Use that column as your check for existence instead of the number column.

Local  &emplidExists;
SQLExec(SQL.MCM_SNLF_BN_RCD_NBR, &employee, &emplidExists, &EmplRcd);
If None(&emplidExists) Then
   MessageBox(0, "", 20002, 4, " No Employee Record skip employee");
   &record = Null;
   Return &record;
End-If;