On 2015-03-12 01:27 AM, Peter Haworth wrote:
> I have a table, Transactions, with a column , Value, of type NUMERIC. The
> Value column is supposed to have 2 decimal places in all rows but some have
> only one.
SQLite has no formatting inherent to the column value, there is no such
thing as "supposed to have 2 zeroes" - who supposes this?
The only way you can rightfully expect a column value to be exactly a
certain length of zeroes (before or after the decimal point) is if the
column is typed as TEXT - which is what happens when you issue:
UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
i.e those values might no longer be stored as NUMERIC values but now are
strings and this might be why your next SELECT seemed to work. (The
interface of choice might still elect to show them via a numeric
interpretation).
Further to this, it is often the interface used which decides how to
represent number values where you do not explicitly define the format to
use.
To force a format, you need to specify it when querying. Example:
WITH TX(x) AS (
SELECT 10.1
UNION ALL
SELECT x+0.1 FROM TX WHERE x<20
)
SELECT printf('%0.2f',x), printf('%10.4f',x) FROM TX;
>
> To correct this I issued the following command:
>
> UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
>
> No errors on execution but nothing was changed.
>
> To help me figure out why that didn't work, I issued the following command:
>
> SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'
>
> This time, all the values with only 1 decimal place were listed with a
> trailing zero added.
>
> On the face of it, the WHERE statement works fine in a SELECT statement but
> does not find any rows in an UPDATE statement. Either that or the SET is
> not calculating the correct value.
>
> Is this a bug or am I missing something?
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
