Re: [sqlite] I suspect not - but is the database separator configurable?

Thanks for everyone's help. Unfortunately, as Ryan highlighted, the double quotes around the table 'database..name' cannot be handled by sybase. It's a shame as I rather like Sqlite and works soo well with Python. All the best, Marc On 02/02/13 07:56, Mohit Sindhwani wrote: Hi Ryan, On

Re: [sqlite] trying to exclude records which have a field that is null

Keith, Petite I'm really grateful for your assistance. I've tried your solutions and they don't quite give me what I want so... sorry for misleading you. Secondly your suggestion that I explain in words is a good one. Here goes... I've got a table of FIRMS and a table of CALLS made to those

Re: [sqlite] trying to exclude records which have a field that is null

I've done most of it with this select f.* from firms f inner join (select * from calls where by_or_on <> '') c on f.id = c.firm_id Phew! On 3 February 2013 09:40, e-mail mgbg25171 wrote: > Keith, Petite > I'm really grateful for your assistance. I've tried your

Re: [sqlite] trying to exclude records which have a field that is null

I need to test this but this is looking promising select f.* from firms f inner join (select firm_id, max(by_or_on) from calls where by_or_on <> '' group by firm_id order by by_or_on) c on f.id = c.firm_id What do you think On 3 February 2013 09:55, e-mail mgbg25171

Re: [sqlite] trying to exclude records which have a field that is null

On Feb 3, 2013, at 10:55 AM, e-mail mgbg25171 wrote: > (select * from calls where by_or_on <> '') c For the record… one thing to watch out… the empty string (aka '') and null are not the same… so if you are looking to eliminate nulls you have to use 'foo is not

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven: On 2/2/2013 6:46 PM, Gabor Grothendieck wrote: In 3.7.11 there was a change to support the feature in the subject which refers to guaranteeing that y comes from the same row having maximum x.. See:

Re: [sqlite] trying to exclude records which have a field that is null

I thought I tried where by_or_on is not null to start with and it didn't SEEM to work hence the <> ''. However...I've just replaced <> '' with IS NOT NULL and it works fine so I'm a bit mystified Thanks for the advice all the same though On 3 February 2013 10:08, Petite Abeille

Re: [sqlite] I suspect not - but is the database separator configurable?

Just had a thought; You could do a few things, unfortunately all at the code base level; 1> I don't know if Python will handle it, but I know most other languages have a string-replace function, or, more specifically, in Delphi, there is a "format" command in which you would create a string,

[sqlite] sqlite 2.1 visual basic 5 or 6

Hello I use sqlite 2.1 and vb 5/6 with psvutils32.dll witch is a wrapper compiled by pivotal solution (O hara) many years ago may I use sqlite3 with same VB ? Advantages ?? Inconvenients ? Remarq : I use only open close select from where insert update delete begin commit It's suffisant

Re: [sqlite] FTS problem with 'NOT term' queries

Hello! > >> And as result it's impossible to search docs in some situations: >> SELECT * FROM docs WHERE docs MATCH 'NOT sqlite'; >> Error: malformed MATCH expression: [NOT sqlite] >> > > As far as I can tell, in MATCH syntax NOT is a binary operator, denoting > set difference. You are trying to

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma wrote: Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven: On 2/2/2013 6:46 PM, Gabor Grothendieck wrote: In 3.7.11 there was a

Re: [sqlite] select max(x), y from table

On 3 Feb 2013, at 6:40pm, "E.Pasma" wrote: > OK, but if one does not assume any specific (non SQL standard) features, the > query is something like: > > SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y) Your query is not standard SQL92. SQL92

Re: [sqlite] populating a table as quickly as possible

Simon Slavin wrote: > > On 3 Feb 2013, at 6:34pm, Paul Sanderson wrote: > >> I want to populate a large table (millions of rows) as quickly as possible, >> >> The data set will not be operated on until the table is fully populated and >> if

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 20:01 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 1:40 PM, E.Pasma wrote: Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma

[sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

Could this be a bug? SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent FROM moz_places , moz_bookmarks WHERE moz_places.id = moz_bookmarks.fkand moz_bookmarks.parent = (select id from moz_bookmarks where title like '%arbeit%') returns

[sqlite] sorting two distinct groups

SELECT f.id FROM firms AS f WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL union SELECT f2.id FROM firms AS f2 WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2) order by (c1.last is null, c2.last is not null) I have two tables firms and calls. I'd like to list all the

Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

> SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent > FROM moz_places, moz_bookmarks > WHERE moz_places.id = moz_bookmarks.fk >and moz_bookmarks.parent = (select id > from moz_bookmarks > where

Re: [sqlite] select max(x), y from table

On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck wrote: > What is the SQLite consortium? http://www.sqlite.org/consortium.html ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

Am Sonntag, den 03.02.2013, 14:00 -0700 schrieb Keith Medcalf: > > SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent > > FROM moz_places, moz_bookmarks > > WHERE moz_places.id = moz_bookmarks.fk > >and moz_bookmarks.parent = (select id > >

Re: [sqlite] select max(x), y from table

Op 3 feb 2013, om 22:06 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeille wrote: On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck wrote: What is the SQLite consortium?

Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does

All that is weird is that SQLite appends a LIMIT 1 to the subquery to ensure it only returns  one row.  This can be seen clearly if you look at the EXPLAIN ouput. Some other SQL engines just explode ... some throw an error. I suppose it all depends on your interpretation of "do as I mean, not

Re: [sqlite] populating a table as quickly as possible

currently using : journal_mode = off page_size=16386 cache_size = 1 synchronous = off I load lots of similar data sets (each into a separate db) and load time is definitely an issue. There are processing delays and loading a db can take 30+ minutes, if I can shave off even a few minutes on

Re: [sqlite] populating a table as quickly as possible

On 4 Feb 2013, at 12:02am, Paul Sanderson wrote: > currently using : > journal_mode = off > page_size=16386 > cache_size = 1 > synchronous = off Reasonable. > I load lots of similar data sets (each into a separate db) and load time is > definitely an issue.

Re: [sqlite] sqlite 2.1 visual basic 5 or 6

On Sun, Feb 3, 2013 at 11:43 AM, Jean-Claude Faure wrote: > Hello > > I use sqlite 2.1 and vb 5/6 with psvutils32.dll witch is a wrapper > compiled by pivotal solution (O hara) many years ago > > may I use sqlite3 with same VB ? > No. The file format changed going from

Re: [sqlite] select max(x), y from table

On 2/3/2013 1:58 PM, Simon Slavin wrote: SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y) Your query is not standard SQL92. SQL92 does not allow SELECT commands as part of expressions. Does too: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 6.11

Re: [sqlite] select max(x), y from table

On 4 Feb 2013, at 2:38am, Igor Tandetnik wrote: > On 2/3/2013 1:58 PM, Simon Slavin wrote: >>> SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y) >> >> Your query is not standard SQL92. SQL92 does not allow SELECT commands as >> part of expressions.

Re: [sqlite] sorting two distinct groups

On 2/3/2013 3:43 PM, e-mail mgbg25171 wrote: SELECT f.id FROM firms AS f WHERE f.id NOT IN (SELECT c1.firm_id FROM calls AS c1) OR f.id IS NULL union SELECT f2.id FROM firms AS f2 WHERE f2.id IN (SELECT c2.firm_id FROM calls AS c2) order by (c1.last is null, c2.last is not null) I have two

Re: [sqlite] FTS problem with 'NOT term' queries

On 02/04/2013 12:18 AM, Alexey Pechnikov wrote: Hello! And as result it's impossible to search docs in some situations: SELECT * FROM docs WHERE docs MATCH 'NOT sqlite'; Error: malformed MATCH expression: [NOT sqlite] As far as I can tell, in MATCH syntax NOT is a binary operator,

Re: [sqlite] sorting two distinct groups

Igor Thank you! Your query is extremely close but for the repetition of the c.ids at the end I've tried putting group by (c,id) but got an error Indeed my weakness seems to be having little idea of how to insert the stuff for a single query into compound queries. e.g. If I want to introduce an