UPDATE Problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

UPDATE Problem

phaworth
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.

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE Problem

Dinu
Hi Peter,

 From https://www.sqlite.org/datatype3.html:

"When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if
such conversion is lossless and reversible"; basically any fraction you
insert into a NUMERIC column with at most 15 digits will be converted do
REAL (float).

What you are looking for is DECIMAL data type, which is not supported by
sqlite, and NUMERIC is not supposed to be a replacement, NUMERIC is only
useful as an extension beyond the range of INTEGER or FLOAT for huge
numbers or extreme precision, in which case it will behave as TEXT and
it's the responsibility of the client app to convert back to some
numeric representation.

Do note however, that if you're dealing with only positive fractions,
you can safely use TEXT to emulate DECIMAL, as long as all strings are
the same length and right-aligned:
" 100.50" < "999.00"

On 12.03.2015 01:27, 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.
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE Problem

R Smith
In reply to this post by phaworth


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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE Problem

Keith Medcalf
In reply to this post by phaworth

When you store something that "looks like a number" in a column with numeric affinity in the table declaration, the value is converted to a numeric type.  That is, if the "something that looks like a number" can be stored as an integer, then it is stored as an integer (with no decimal point and no decimal places).  If the "something that looks like a number" cannot be stored as an integer but can be stored as a floating point number, then that is how it is stored.  So, if you store the string '10.0000000' in a numeric affinity column, the integer 10 is stored.  If you store the string '25.100000' in a numeric affinity column, then it is stored as the floating point value 25.1.

So your statement:

> UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'

does nothing other than convert a floating point value (say 10.1) to a text representation, then appending a '0' to that text so it is now '10.10'.  When that text value is stored back into the numeric affinity column it is converted back to the floating point value 10.1.

The select statement:

>SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.'

is simply showing you the text values before they are converted back into the original floating point value.  To see what will be stored in the database itself you probably need something like:

>SELECT cast(Value||0 as numeric) FROM Transactions WHERE substr(Value,-2,1)='.'

Displaying values with 2 decimal places is an "output to the user by the application" problem and you need to address it there (in the application -- right before displaying the value).  For example, you can use printf("%.2f", value) or its equivalent in your application language to format the output to your requirements for viewing.

SQLite itself is working as designed.

---
Theory is when you know everything but nothing works.  Practice is when everything works but no one knows why.  Sometimes theory and practice are combined:  nothing works and no one knows why.


>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Peter Haworth
>Sent: Wednesday, 11 March, 2015 17:28
>To: [hidden email]
>Subject: [sqlite] UPDATE Problem
>
>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.
>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE Problem

phaworth
In reply to this post by phaworth
Thanks to all for explaining my confusion.  The printf solution seems like
the best way to handle this since I don't need to worry about how many
decimal places are in the number.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users