Re: [sqlite] Count(*) help

On Mon, Sep 17, 2012 at 06:03:12PM -0600, Keith Medcalf scratched on the wall: > > There it is again. What does length(NULL) mean ? Should it be NULL because > > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > > negative integer representing how long the NULL

Re: [sqlite] IN operator hits

I apologize and thank Paul for his answer. In fact, what I really meant to ask was how to get the original hit values for a MATCH operation when using FTS4 with a Porter tokenizer. I'm not sure what I was thinking in asking this question - please disregard it. On Mon, Sep 17, 2012 at 4:28 PM,

Re: [sqlite] Count(*) help

> There it is again. What does length(NULL) mean ? Should it be NULL because > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > negative integer representing how long the NULL value is ? I may have missed > it, but I don't think the answer is in SQL92. The only

Re: [sqlite] IN operator hits

E. Timothy Uy wrote: > Is there a way to figure out which of the terms in an IN operator actually > hit? I'm not sure I understand the question. What exactly are you trying to achieve? I suggest you describe the problem you are trying to solve, rather than your proposed

Re: [sqlite] Count(*) help

On Sep 17, 2012, at 5:09 PM, Simon Slavin wrote: > I suspect that this is what leads to different SQL engines implementing > count() in different ways. I suspect that you are indeed very confused :D The issue at hand is not whether or not count( * ) is implemented

Re: [sqlite] Count(*) help

On Sep 17, 2012, at 5:23 PM, "Keith Medcalf" wrote: > I don't know if there is a standard for treating empty character strings as > if they were not-present values without first applying one of the builtin > functions designed to deal with NULL. However, my opinion is

Re: [sqlite] Count(*) help

Simon Slavin wrote: > What does length(NULL) mean ? When there is no string, there is no string length. > I don't think the answer is in SQL92. | 6.6 | [...] | General Rules | [...] | 4) If a is specified, then |Case: |a) Let S be the . If the value of S is | not the null value,

Re: [sqlite] IN operator hits

On 17 September 2012 17:23, E. Timothy Uy wrote: > Is there a way to figure out which of the terms in an IN operator > actually hit? Perhaps making a temp table is the only way. Any help Do you mean... SELECT DISTINCT col FROM tbl WHERE col IN ('a', 'b', 'c', 'd'); Regards, Paul.

[sqlite] IN operator hits

Is there a way to figure out which of the terms in an IN operator actually hit? Perhaps making a temp table is the only way. Any help is much appreciated. Respectfully, Tim ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Count(*) help

On 17 Sep 2012, at 4:30pm, "Black, Michael (IS)" wrote: > sqlite> select count(*) from t where length(a) = 0; > 1 There it is again. What does length(NULL) mean ? Should it be NULL because anything derived from "Don't know" is "Don't know" ? Or should it be a

Re: [sqlite] Count(*) help

That's the nice thing about standards...there's so many to choose from. One man's NULL is another's length=0, is another's "empty" string of ''. http://en.wikipedia.org/wiki/Null_%28SQL%29 IMHO sqlite3 gets it right and Oracle 11.2.0.1.0 gets it wrong... Purportedly: "Null is defined by the ISO

Re: [sqlite] Count(*) help

On 17 Sep 2012, at 4:05pm, John Clegg wrote: > Yes, that was the problem thanks. Even though sqlitebrowser declared them > as empty with '' ! No, actually that's your problem. '' is exactly the way to define something as an empty string. To supply a null you would

Re: [sqlite] Count(*) help

On 17 Sep 2012, at 3:50pm, "Black, Michael (IS)" wrote: > I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the SQL > standard say anything about what a "NULL" value is? And who's correct here > if there is a standard? I believe that the values are

Re: [sqlite] Count(*) help

Yes, that was the problem thanks. Even though sqlitebrowser declared them as empty with '' ! On 17 September 2012 15:50, Black, Michael (IS) wrote: > Or just fix the existing table: > > update members set year2007=NULL where year2007=''; > update members set year2008=NULL

Re: [sqlite] Count(*) help

Or just fix the existing table: update members set year2007=NULL where year2007=''; update members set year2008=NULL where year2008=''; update members set year2009=NULL where year2009=''; update members set year2010=NULL where year2010=''; update members set year2011=NULL where year2011='';

Re: [sqlite] Count(*) help

John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select >

Re: [sqlite] heap consumption is growing

On Mon, Sep 17, 2012 at 6:19 AM, Eckert Martin (DC-IA/EWT1) < martin.eck...@boschrexroth.de> wrote: > Hi all, > I'm using sqlite 3.7.14. I have an open sqlite database. New datasets are > continously entered into this database. After having entered a new dataset > into the database heap

Re: [sqlite] Count(*) help

You have a bad table structure which is helping to cause your problem. It's pretty obvious that you don't want one column per year, you want want a membership table that has member,year, and paid status (or whatever info you're keeping for year). The way you have you have to modify your

[sqlite] heap consumption is growing

Hi all, I'm using sqlite 3.7.14. I have an open sqlite database. New datasets are continously entered into this database. After having entered a new dataset into the database heap consumption has grown. After each sqlite prepare and step cmd I use the sqlite3_finallize to free the recources.

Re: [sqlite] Rollback of nested savepoints with System.Data.SQLite

Joe Mistachkin wrote: > Sven Bloesl wrote: > > > BEGIN IMMEDIATE > > > > SAVEPOINT SP1 > > CREATE TABLE a(key number primary key); > > > > SAVEPOINT SP2 > > INSERT INTO a VALUES(666); > > SELECT count(1) FROM a Expected: 1, Got: 1 > > > > SAVEPOINT SP3 > > INSERT INTO a

Re: [sqlite] Rollback of nested savepoints with System.Data.SQLite

Sven Bloesl wrote: > > BEGIN IMMEDIATE > > SAVEPOINT SP1 > CREATE TABLE a(key number primary key); > > SAVEPOINT SP2 > INSERT INTO a VALUES(666); > SELECT count(1) FROM a Expected: 1, Got: 1 > > SAVEPOINT SP3 > INSERT INTO a VALUES(13); > SELECT count(1) FROM a

Re: [sqlite] Rollback of nested savepoints with System.Data.SQLite

On 17 Sep 2012, at 7:23am, Sven Bloesl wrote: > BEGIN IMMEDIATE > > SAVEPOINT SP1 > CREATE TABLE a(key number primary key); > > SAVEPOINT SP2 > INSERT INTO a VALUES(666); > SELECT count(1) FROM a Expected: 1, Got: 1 > > SAVEPOINT SP3 > INSERT INTO a

Re: [sqlite] Count(*) help

Hi John, If you zip the file (it sounds it is only small) and mail it to me or to this list and tell what answer you want I (or somebody else) will post the SQL. RBS On Mon, Sep 17, 2012 at 7:59 AM, John Clegg wrote: > OK thanks folks. Here is the full query (which is

Re: [sqlite] Count(*) help

OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have removed the double-quoted but it makes no difference. SELECT COUNT( "Year2007" ), COUNT( "Year2008" ), COUNT( "Year2009" ), COUNT( "Year2010" ), COUNT( "Year2011" ), COUNT(

[sqlite] Rollback of nested savepoints with System.Data.SQLite

Hello, I'm using System.Data.SQLite 1.0.82.0 and can't get nested savepoint work in my application. Rollbacks to the nested savepoint do not have any effects, only rollbacks to the outermost savepoint undo my changes. According to the SQLite documentation this should work and I verified that