Re: [sqlite] ISNULL in sqlite

On Sat, Jun 25, 2011 at 04:55:13PM -0400, Igor Tandetnik scratched on the wall: > logan...@gmail.com wrote: > > How do I check for a null or empty string in SQLite. SQL server has ISNULL > > but it doesn't seem to be supported in SQLite. > > where MyField is null where MyField isnull is

Re: [sqlite] Substring question

If you can't have two : after each other then this should work: SELECT LTRIM(LTRIM('abc:xyz',REPLACE('abc:xyz',':','')),':') SELECT LTRIM(LTRIM(Field1,REPLACE(Field1,':','')),':') FROM Table1 -- View this message in context: http://old.nabble.com/Substring-question-tp31924687p31928255.html

Re: [sqlite] ISNULL in sqlite

logan...@gmail.com wrote: > How do I check for a null or empty string in SQLite. SQL server has ISNULL > but it doesn't seem to be supported in SQLite. where MyField is null -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] 3.7.7, URI filenames, and UNC paths

On Fri, Jun 24, 2011 at 11:36 PM, Roger Binns wrote: > On 06/24/2011 08:28 AM, Cheetah wrote: >> However, AFAICT, the standard form of a file: >> URI on windows for a UNC path uses exactly that. > > Are you proposing that SQLite somehow work out if the supplied name maps >

Re: [sqlite] question about sqlite aggregate funcitons

On Sat, Jun 25, 2011 at 7:25 AM, Luuk wrote: > I create a test database with almost 10 milion rows. > I'm surprised to see that the first SELECT below is much slower than the > sum of the next three SELECTs. > > Can anyone give a hint why this is the case? > > SQLite version

Re: [sqlite] EXT :Re: question about sqlite aggregate funcitons

On 25 Jun 2011, at 7:14pm, Black, Michael (IS) wrote: > I guess my point is why doesn't the optimizer recognize the almost-zero cost > of min/max in the compound query? It assumes instead to do it all in one scan. Because it's almost impossible to optimize sub-SELECTs. You pretty-much have

Re: [sqlite] ISNULL in sqlite

On Jun 25, 2011, at 3:33 PM, logan...@gmail.com wrote: > Hello, > > How do I check for a null or empty string in SQLite. SQL server has ISNULL > but it doesn't seem to be supported in SQLite. ifnull() and nullif() [http://www.sqlite.org/lang_corefunc.html]

[sqlite] ISNULL in sqlite

Hello, How do I check for a null or empty string in SQLite. SQL server has ISNULL but it doesn't seem to be supported in SQLite. Thanks, Hitesh ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] EXT :Re: question about sqlite aggregate funcitons

On 25-06-2011 20:14, Black, Michael (IS) wrote: > So these two queries should be equal in speed and plan: > > > > > > select * from (select min(i) from test) as a,(select max(i) from > test) as b, (select count(i) from test) as c; select > min(i),max(i),count(i) from test; and, to make the

Re: [sqlite] EXT :Re: question about sqlite aggregate funcitons

I guess my point is why doesn't the optimizer recognize the almost-zero cost of min/max in the compound query? It assumes instead to do it all in one scan. So these two queries should be equal in speed and plan: select * from (select min(i) from test) as a,(select max(i) from test) as b,

Re: [sqlite] The last records of a query

On Sat, 25 Jun 2011 19:42:11 +0200, Cecil Westerhof wrote: >Thanks for the answers. I had thought about both options, but was wondering >if I had missed something better. I original opted for the LIMIT/OFFSET >option (because the order is important), but after following

Re: [sqlite] The last records of a query

2011/6/25 Cecil Westerhof > With LIMIT you can get the first N records of a SELECT. Is it also possible > to get the last N records? > Thanks for the answers. I had thought about both options, but was wondering if I had missed something better. I original opted for the

Re: [sqlite] question about sqlite aggregate funcitons

On 25 Jun 2011, at 4:12pm, Black, Michael (IS) wrote: > Can the optimizer recognize these "instant" functions and perhaps lean > towards using them? It already does. That's why max(x) takes no time when you have an index on (x). > Are there cases where this would NOT be good? Theoretically,

Re: [sqlite] question about sqlite aggregate funcitons

Can the optimizer recognize these "instant" functions and perhaps lean towards using them? Are there cases where this would NOT be good? I did show you can construct a query which runs at the "correct" speed. sqlite> select * from (select min(i) from test) as a,(select max(i) from test)

Re: [sqlite] question about sqlite aggregate funcitons

On 25 Jun 2011, at 1:37pm, Luuk wrote: > Giving the SELECTs i a different order does not improve thing (much). > BTW, i'm doing this on a Windows 7 machine, and will test what happens > on a Linux machine too, to see if there's difference. > > SQLite version 3.7.2 > Enter ".help" for

Re: [sqlite] question about sqlite aggregate funcitons

On 25-06-2011 15:15, Black, Michael (IS) wrote: > OK...I generated 10M ints and imported them. I get times that I would expect. > > select with all 3 is slightly less then the sum of the other 3 individually. > > > > main() > { > int i; > for(i=0;i<1000;i++) { >

Re: [sqlite] question about sqlite aggregate funcitons

On 25-06-2011 15:37, Black, Michael (IS) wrote: > And...this select does it at the speed you expect since it uses the indexes > for min/max.\ > > > > This one I ran with 3.7.7 > > > > sqlite> select * from (select min(i) from test) as a,(select max(i) from > test) as b, (select count(i)

Re: [sqlite] question about sqlite aggregate funcitons

And...this select does it at the speed you expect since it uses the indexes for min/max.\ This one I ran with 3.7.7 sqlite> select * from (select min(i) from test) as a,(select max(i) from test) as b, (select count(i) from test) as c; min( max(i) coun - 37

Re: [sqlite] question about sqlite aggregate funcitons

OK...I generated 10M ints and imported them. I get times that I would expect. select with all 3 is slightly less then the sum of the other 3 individually. main() { int i; for(i=0;i<1000;i++) { int j=rand(); printf("%d\n",j); } }

Re: [sqlite] question about sqlite aggregate funcitons

sorry, my reaction is at the bottom ;) On 25-06-2011 13:59, Black, Michael (IS) wrote: > Ucaching? > > > > Try doing your first select last and see what happens. I'm betting your > first "select min(*) from test" will be a lot slower. > > > > Michael D. Black > > Senior Scientist

Re: [sqlite] question about sqlite aggregate funcitons

Ucaching? Try doing your first select last and see what happens. I'm betting your first "select min(*) from test" will be a lot slower. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

[sqlite] question about sqlite aggregate funcitons

I create a test database with almost 10 milion rows. I'm surprised to see that the first SELECT below is much slower than the sum of the next three SELECTs. Can anyone give a hint why this is the case? SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";"

Re: [sqlite] Plan not optimized ?

Hi Richard, Do you have a plan to implement "common subexpression elimination" ? In the attached database, when I try to execute "select * from v_category_display", performances are very bad and I don't know how to correct this problem. "common subexpression elimination" is the best solution

[sqlite] Substring question

I need to select a substring of a column that starts 1 character after a colon in the column and continues to the end of the column. For example, if the column contained "abc:xyz" I want the select statement to return only "xyz" (the characters after the colon). The substr function requires

Re: [sqlite] Substring question

On Sat, Jun 25, 2011 at 9:53 AM, Pete wrote: > I need to select a substring of a column that starts 1 character after a > colon in the column and continues to the end of the column. For example, > if > the column contained "abc:xyz" I want the select statement to return

Re: [sqlite] The last records of a query

On Sat, 25 Jun 2011 09:26:42 +0200, Cecil Westerhof wrote: > With LIMIT you can get the first N records of a SELECT. > Is it also possible to get the last N records? Yes, I can think of two ways: 1- Reverse the ORDER BY (make it DESC), so you can use the exact same

Re: [sqlite] The last records of a query

On 06/25/11 09:26 AM, Cecil Westerhof wrote: > With LIMIT you can get the first N records of a SELECT. Is it also possible > to get the last N records? Search for LIMIT/OFFSET on http://www.sqlite.org/lang_select.html /Roger ___ sqlite-users mailing

Re: [sqlite] The last records of a query

On Sat, Jun 25, 2011 at 11:26 AM, Cecil Westerhof wrote: > With LIMIT you can get the first N records of a SELECT. Is it also possible > to get the last N records? > > Use ORDER BY ... DESC. Sure, if you have a large dataset, it'd better be indexed. Max

[sqlite] The last records of a query

With LIMIT you can get the first N records of a SELECT. Is it also possible to get the last N records? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Turn off journal if no transaction

On Fri, Jun 24, 2011 at 22:41:16 -0700, Eugene Ong wrote: > I don't use transactions in my app, There is no way not to use transactions. If you don't start transaction explicitly, implicit transaction will be started for each statement. This ensures that when a statement fails (e.g. because of