Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

MS SQL DataServer

SYMPTOM(s):

The SQL Server DataServer is not updating the data in the SQL database

The fields are decimal data type in the SQL server database.

The fields are also null before assigning the data to it

The code is assigning the value to the field by using the similar code as the following:
DEFINE VAR dAmount AS DECIMAL NO-UNDO.
FIND FIRST table-name WHERE field-name = 10 EXCLUSIVE-LOCK.
IF AVAILABLE table-name THEN
DO:
UPDATE table-name.
ASSIGN table-name.field-name = total-amount + dAmount.
END.

CAUSE:

The fields are null in the SQL Server Database. Therefore, when find statement gets the data from SQL Server database, the data is coming as a null. Hence, the addition of a value to a null in the update statement did not work with the SQL Server DataServer.

FIX:

To make the code work, initialize the variable with zero (0) as shown below:
DEFINE VAR dAmount AS DECIMAL INITIAL 0 NO-UNDO.

FIND FIRST table-name WHERE field-name = 10 EXCLUSIVE-LOCK.
IF AVAILABLE table-name THEN
DO:
UPDATE table-name.
ASSIGN table-name.field-name = total-amount + dAmount.
END.
The following code also works:

DEFINE VAR dAmount AS DECIMAL NO-UNDO.
FIND FIRST table-name WHERE field-name = 10 EXCLUSIVE-LOCK.
IF AVAILABLE table-name THEN
DO:
UPDATE table-name.
ASSIGN table-name.field-name = dAmount.
END.