Re: [sqlite] cast problems sqlite3

[drh]

Tom Shaw <[EMAIL PROTECTED]> wrote:
> 
> Using sqlite 3.3.5

You really should try to use something more recent.

> 
> UPDATE av_summary SET rank=((det*100.0)/(tot));
> 
> sets first row to an integer (serendipity?) and then all the other 
> rows are real or text which caused problems since I was expecting 
> that column was integer since that is how the table was created. I 
> see when I export that the numbers are real in the text exported. 
> This must have been what confused me.  There is obviously something 
> here that I don't grok.
> 

SQLite, unlike many other SQL database engines, strives to
avoid throwing away information.  If you have a value 1.234
and you store that value in an INTEGER column, SQLite tries
to convert the value to an integer.  But it sees that this
conversion would loss information - specifically the fraction
part 0.234.  So it stores the original floating point value
instead.  Other database engines would silently discard the
fractional part.

If you store 1.0 into a column marked INTEGER, it will convert
the value to 1, since no information is lost.

If you really have to have an integer in your column, then
use CAST() or ROUND() to round of the value to an integer first.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] cast problems sqlite3

[Tom Shaw]

At 1:40 PM + 3/12/07, [EMAIL PROTECTED] wrote:

Tom Shaw <[EMAIL PROTECTED]> wrote:

 Here ya go.

 >Tom Shaw <[EMAIL PROTECTED]> wrote:
 >>  UPDATE av_summary SET  rank=((det*100.0)/(tot)); only sets the
 >>  first row correctly then all the others have bogus data in rank
 >
 >That seems wrong.  Can you post a sample database that demonstrates
 >this behavior?
 >


I tried this on the database you sent me.  The answers
all look right to me.


Using sqlite 3.3.5

UPDATE av_summary SET rank=((det*100.0)/(tot));

sets first row to an integer (serendipity?) and then all the other 
rows are real or text which caused problems since I was expecting 
that column was integer since that is how the table was created. I 
see when I export that the numbers are real in the text exported. 
This must have been what confused me.  There is obviously something 
here that I don't grok.


Thanks,

Tom




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] cast problems sqlite3

[drh]

Tom Shaw <[EMAIL PROTECTED]> wrote:
> Here ya go.
> 
> >Tom Shaw <[EMAIL PROTECTED]> wrote:
> >>  UPDATE av_summary SET  rank=((det*100.0)/(tot));  only sets the
> >>  first row correctly then all the others have bogus data in rank
> >
> >That seems wrong.  Can you post a sample database that demonstrates
> >this behavior?
> >

I tried this on the database you sent me.  The answers
all look right to me.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] cast problems sqlite3

[drh]

Tom Shaw <[EMAIL PROTECTED]> wrote:
> UPDATE av_summary SET  rank=((det*100.0)/(tot));  only sets the 
> first row correctly then all the others have bogus data in rank

That seems wrong.  Can you post a sample database that demonstrates
this behavior?

> [I]s there a "cast" operator in the SQL
> 

CAST( AS )

Example:

   UPDATE av_summary SET rank=CAST(det AS real)/total;


--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] cast problems sqlite3

[Tom Shaw]

Help is appreciated. I have a table with integer columns rank, tot, 
det with values in tot and det and I want to put an integer percent 
(0-100) into rank


UPDATE av_summary SET  rank=(det/tot)*100;	returns 0 I assume 
because the arithmetic is in integer
UPDATE av_summary SET  rank=((det*100.0)/(tot));	only sets the 
first row correctly then all the others have bogus data in rank

UPDATE av_summary SET  rank=ROUND((det*100.0)/(tot));   works over all rows

Could someone explain. Also is there a "cast" operator in the SQL 
that SQLite executes?


TIA,

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-