This caught me out today as I was using a table variable in my bind. The rounded value was subsequently written back to the open table. Dohhhh.

____________________

When using SQL to get a count of record back using the BEGIN, END statements be warned that any numeric variable passed in will be rounded removing any precision after the decimal point after the execution of the statement. Note the SQL will honor the full precision for a variable being passed in.

For example the following policy does have an INV_AMT of 318.01, so the following should return back a #COUNT of 1, however what it also does is change the value of #TEST from 318.01 to 318.00.

#TEST = 318.01

SQL

BEGIN

SELECT COUNT(*) INTO :#COUNT FROM INV_PBINV_TBL

WHERE INV_POLICY_ID = '8000255370' AND INV_AMT = :#TEST;

END;

ENDSQL

UMSG('#COUNT = ' + ALPHA(#COUNT) + ', #TEST = ' + ALPHA(#TEST),-1)

Also note that if you were trying to pull the value of INV_AMT into #COUNT that you would have to multiply it by 100 in SQL and divide by 100 in ProIV to get the correct value.

SQL

BEGIN

SELECT INV_AMT * 100 INTO :#COUNT FROM INV_PBINV_TBL

WHERE INV_POLICY_ID = '8000255370';

END;

ENDSQL

#COUNT = #COUNT / 100