Good day.
Work with Xojo 2014r2.1 and Postgresql 9.3, all the fields the tables is numeric 18, 2
I have object with properties Ahorro_Total, Porcentaje, Intereses_Movimiento, Intereses_Saldo all of type double, Also i have several methods, including the DataGet, DataSave
DataGet()
'In this method the property values are correct.
dim sql as String
sql = "Select p.ahorros_saldo, COALESCE(i.porcentaje, 0) as porcentaje "_
- "From r.personal p LEFT JOIN LATERAL "_
-
"c.interesget(p.codigo, p.ahorros_saldo) i On TRUE "_
- “WHERE p.codigo = $1”
dim ps As PostgreSQL.PreparedSqlStatement = app.pasaDB.Prepare(sql)
dim r as RecordSet = ps.SQLSelect(txtcodigo.Text)
me.Porcentaje = r.Field(“porcentaje”).Value
sql = "Select p.ahorros - $1 as ahorros_total, "_
- "Round(p.ahorros * $2 * (CURRENT_DATE - p.ultimo_movimiento) / 36000, 2) as intereses_movimiento, "_
- "Round(p.interes + (p.ahorros * $2 * (CURRENT_DATE - p.ultimo_movimiento) / 36000), 2) as intereses_saldo "_
- "From r.personal p "_
- “Where p.codigo = $3”
ps = app.pasaDB.Prepare(sql)
r = ps.SQLSelect(val(Replace(TxtValor.Text, “,”, “”)), me.Porcentaje, txtcodigo.Text)
if app.pasaDB.Error Then
me.ErrMsg = “Error al calcular ahorros saldo e intereses…” + chr(13) + app.pasaDB.ErrorMessage
Return False
end if
me.Ahorro_Total = r.Field(“ahorros_total”).Value
me.Intereses_Movimiento = r.Field(“intereses_movimiento”).Value
me.Intereses_Saldo = r.Field(“intereses_saldo”).Value
DataSave()
'This method has problems when sabe
dim sql as String
sql = “Insert Into c.transacciones " _
+”(fecha, hora, codigo, numero_comprobante, retiros, saldo_ahorros, movimiento_interes, porcentaje, saldo_interes, comentario, tipo_movimiento) " _
+“Values “_
+”(CURRENT_DATE, SUBSTRING(CAST(CURRENT_TIME As VARCHAR) from 1 for 8), $1, $2, $3, $4, $5, $6, $7, $8, $9)”
dim ps As PostgreSQL.PreparedSqlStatement = app.pasaDB.Prepare(sql)
ps.SQLExecute(txtcodigo.Text, txtnumero.Text, val(Replace(TxtValor.Text, “,”, “”)), me.Ahorro_Total, me.Intereses_Movimiento, me.Porcentaje, me.Intereses_Saldo, if(me.Concepto = “”, TxtConcepto.Text, me.Concepto), me.Codigo)
ps.SQLExecute(txtcodigo.Text, txtnumero.Text, val(Replace(TxtValor.Text, “,”, “”)), str(me.Ahorro_Total, “#########.##”), me.Int ereses_Movimiento, me.Porcentaje, me.Intereses_Saldo, if(me.Concepto = “”, TxtConcepto.Text, me.Concepto), me.Codigo)
From the debugger i see that me.Ahorro_Total is -215773.59
In Postgresql
The firts sql round -215773.59 to -215773.6
2015-04-14 V5700 373 0 47.17 -215773.6 0 6 -4437.79
The seconds sql is correct with the exception of the sign
2015-04-14 V5700 373 0 47.17 215773.59 0 6 -4437.79
I don’t understand because of rounding, what’s the problem?
There is way to solve without using str()
Regards.
Mauricio
P.D. Excuse my english is basic