1 reply to this topic

[DARREN]

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

[Richard Bassett]

Hi Darren,

 

Actually, I think things are slightly subtler than that.  IIRC, ProIV documents that you cannot receive output values from full-function SQL into ProIV variables, it's officially *not* supported.  But, despite that, people commonly do this with numeric values and it mostly works (as you observed).

 

Past testing has strongly suggested to me that ProIV exchanges numeric values with Oracle in the form of binary floaing point numbers (i.e. C double type if you are familiar with that).  This means decimal places cannot be round-tripped completely accurately (the same reason people often ask of C/Java/etc.  why 0.1 + 0.2 is not equal to 0.3)

 

Consequently, the guidelines we adopted were that host variables should always be ProIV alpha variables but that it was acceptable when particularly advantageous, to use a :#var host variable to receive integer values, but nothing else.

 

 

> what it also does is change the value of #TEST from 318.01 to 318.00

 

Given what I recall above, I am a bit surprised by that - but I guess it's possible ProIV deliberately remove all the decimals because of these issues.  I would actually have expected only that the decimals might become slightly different due to the value being round-tripped in the binary floating point representation (e.g. 318.01 becomes 318.00999...)

 

(General caveat:  I never tested anything later than v5.5 in this respect)

Edited by Richard Bassett, 20 October 2014 - 02:02 PM.