t-
> Von: Bart Smissaert [mailto:bart.smissaert at gmail.com]
> Gesendet: Montag, 16. Februar 2015 09:49
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Can this be sorted?
>
> Sorry, it looked OK on my side, but I suppose some of the Excel formatting
>
:
order by field3 desc, field2 asc
Trying to get the row with 47 at the bottom.
RBS
On Mon, Feb 16, 2015 at 2:58 AM, Igor Tandetnik wrote:
> On 2/15/2015 6:54 PM, Bart Smissaert wrote:
>
>> Result is shown below.
>>
>>
Have the following full SQL:
SELECT
DS.DRUG_NAME AS DRUG_NAME,
SUM(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) AS SENSITIVE,
SUM(INSTR(M.ASSOCIATED_TEXT, DS.RESISTANT_STRING) > 0) AS RESISTANT,
ROUND(TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) /
(TOTAL(INSTR(M.ASSOCIATED_TEXT,
Looked in the documentation and the answer seems to use total instead of
sum.
This is for the ratio field, not the 2 count fields.
Looks all sorted now and thanks again.
RBS
On Sat, Feb 14, 2015 at 5:13 PM, Bart Smissaert
wrote:
> One more thing. How would I get the ratio of the 2 counts,
ems join Descriptions
group by NAME
Gives integer values, so no decimals.
RBS
On Sat, Feb 14, 2015 at 4:49 PM, Igor Tandetnik wrote:
> On 2/14/2015 11:32 AM, Bart Smissaert wrote:
>
>> SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I
>> INNER JOIN DESCRIPTIONS
49 PM, Igor Tandetnik wrote:
> On 2/14/2015 11:32 AM, Bart Smissaert wrote:
>
>> SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I
>> INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0)
>> INNER JOIN DESCRIPTIONS D2 ON (INSTR(D2.FULL_TEXT,
Having problems with the following SQL:
2 tables, trying to count occurrence of field 2 and field 3 of table 1
in field 1 (only field) of table 2.
Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2
all text fields. Values in NAME are all unique.
Table 2 called DESCRIPTIONS with only
Best way to do this is I think with this wrapper:
http://www.vbrichclient.com/#/en/About/
It is very fast and has lots of other useful utilities other than SQLite, eg
fast collection and dictionary classes.
RBS
On Thu, Jan 22, 2015 at 1:04 AM, YAN HONG YE wrote:
> I don't know how to use sqlit
Not sure now if SQLite does events that can be picked up by your
application, but I use a VB wrapper (vbRichClient, written by Olaf Schmidt)
that does do SQLite events. If events can't be used then I presume you need
to poll the
database for changes.
RBS
On Fri, Feb 7, 2014 at 10:06 AM, Simon Sla
> The vbRichClient appears to be *very sparsely* documented
In practice this is not really a problem.
I have been using this library for a few years (in a commercial
application) and
never found it difficult to get the answers. This is because the SQLite
objects and
methods closely resemble ADO ob
>
> > > > Table will then be like this:
> > > >
> > > > FIELD1 FIELD2
> > > > -
> > > > 0 FIELD2
> > > > 1 ABC
> > > > 2 BCD
> > > > 3 CDE
>
> ---
> () ascii ribb
; it looks like by executing:
>
> Select * from QQQ;
>
> The error of your untested assumption should then be clear ...
>
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org
>
>
> > -Original Message-----
> > From: sqlite-users-
n...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: Saturday, June 08, 2013 9:58 AM
> To: rsm...@rsweb.co.za; General Discussion of SQLite Database
> Subject: Re: [sqlite] Strange table behaviour after text import with
> sqlite3.exe
>
>
field, but the real value of FIELD1 is "FIELD1" for the 0th
> record, since that is what was imported from the CSV. The formatted value
> is not always the same as the real value.
>
> try:
>
> select * from qqq where field1 = "FIELD1"
>
>
> It will po
#x27;,'FIELD2');
> INSERT INTO "QQQ" VALUES(1,'ABC');
> INSERT INTO "QQQ" VALUES(2,'BCD');
> INSERT INTO "QQQ" VALUES(3,'CDE');
> COMMIT;
>
>
>
> -Original Message-
> From: sqlite-users-boun...
Have table defined like this:
CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
Table is empty, so has no records.
Then I import a text file with this data:
FIELD1,FIELD2
1,ABC
2,BCD
3,CDE
This is via sqlite3.exe with:
.mode csv
.import textfilename QQQ
Table will then be like this:
FIELD1
le
is already there?
RBS
On Fri, May 31, 2013 at 11:21 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 31/05/13 05:34, Bart Smissaert wrote:
> > Importing a. csv file via the. import command of sqlite3.exe. As the
> > first line holds t
That modified version sounds useful. Could you mail it?
RBS
On May 31, 2013 6:14 PM, "Larry Brasfield"
wrote:
> Bart Smissaert wrote:
>
>> Importing a. csv file via the. import command of sqlite3.exe.
>> As the first line holds the field names I want to skip
Importing a. csv file via the. import command of sqlite3.exe.
As the first line holds the field names I want to skip that.
There is no problem achieving this in code, but could I do this
purely via sqlite3 commands?
RBS
___
sqlite-users mailing list
sqli
Hi Hitesh,
Here all the VB6 code to do with this. Ignore all the Debug stuff and also
all the RaiseEvent lines. Note that this uses the free VB SQLite wrapper
from Olaf Schmidt and if you don't use that then that is very much
recommended. Let me know if you want that and I will explain.
Also note
Hi Hitesh,
Attached all the VB6 code to do with this. Ignore all the Debug stuff and
also all the RaiseEvent lines. Note that this uses the free VB SQLite
wrapper from Olaf Schmidt and if you don't use that then that is very much
recommended. Let me know if you want that and I will explain.
Also
Have a look at this thread in the archive:
find sequential groups
It can be done with SQL, but it is slow and it can be done enormously
faster in code.
I did this in VB6 and let me know if you are interested and I mail you the
code off-list.
RBS
On Sat, Apr 27, 2013 at 8:10 AM, hiteshambaliya
w
Did a bit of further testing and when the age field is an integer field
and the band field is another field with text datatype then the method
with a lookup table is slightly faster. Either way there isn't much in it.
RBS
On 11/22/12, Simon Slavin wrote:
>
> On 22 Nov 2012, at 5
)
seems slightly faster and looks simpler.
RBS
On 11/22/12, Simon Slavin wrote:
>
> On 22 Nov 2012, at 10:47am, Bart Smissaert
> wrote:
>
>> Ignore this e-mail, it was a simple mistake from my side and nil to do
>> with SQLite.
>
> It takes a big man to admit a mist
but that would make
it all lot more complex coding-wise.
Will have a look though at the speed of creating a temp table in
memory and converting
that way, but my guess is that it is slower.
RBS
On Thu, Nov 22, 2012 at 12:58 PM, Simon Slavin wrote:
>
> On 22 Nov 2012, at 10:47am, Bart Smissae
The simple explanation was that I had make the field text instead of no
data affinity.
RBS
On 11/22/12, Bart Smissaert wrote:
> Ignore this e-mail, it was a simple mistake from my side and nil to do
> with SQLite.
> Apologies for this
Ignore this e-mail, it was a simple mistake from my side and nil to do
with SQLite.
Apologies for this.
RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Have a table with one field holding numbers from 0 to about 110.
The field has no data type, so it is not integer or text.
Now I run the following SQL:
UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10'
when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN
21 AN
Thanks.
Have a feeling I made this same mistake before and posted to this
forum as well ...
RBS
On Wed, Oct 17, 2012 at 4:13 AM, Igor Tandetnik wrote:
> Bart Smissaert wrote:
>> To do with the same, what is wrong with this update SQL?
>>
>> update final2
>> set gro
Smissaert
wrote:
> Without the concatenation it runs fine,
> enormously faster than with the concatenation.
> Have checked and the result is fine as well.
> Thanks again.
>
> RBS
>
>
> On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik wrote:
>> On 10/16/2
Without the concatenation it runs fine,
enormously faster than with the concatenation.
Have checked and the result is fine as well.
Thanks again.
RBS
On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik wrote:
> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>
>> Actually, it reall
27;ll see that # 7 gets a new Group_Marker instead of repeating group#2.
>
>
> 1|D|1
> 2|X|2
> 3|X|2
> 4|X|2
> 5|A|3
> 6|B|4
> 7|X|5
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> N
Thanks, will try that.
Yes, the ID field is an integer primary key autoincrement.
Still running the old sql with concatenation. Looks I may need
to kill that.
RBS
On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik wrote:
> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>
>> Actuall
Yes, that should be 4 groups, marked with 1, 2, 3 and 4.
RBS
On Tue, Oct 16, 2012 at 11:15 PM, Igor Tandetnik wrote:
> On 10/16/2012 6:08 PM, Black, Michael (IS) wrote:
>>
>> Do this work for you?
>>
>> CREATE TABLE Groups (Value);
>> insert into Groups select distinct(Value) from test;
>
>
> S
still running and looks will be a long time.
Will have to improve it with indexes and maybe avoiding the concatenation.
RBS
On Tue, Oct 16, 2012 at 10:53 PM, Igor Tandetnik wrote:
> On 10/16/2012 4:56 PM, Bart Smissaert wrote:
>>
>> Trying to make a query that can mark records,
Trying to make a query that can mark records, indicating them to
belong to a sequential group.
Giving the most simple example:
IDValue Group_Marker
---
1 D1
2 X 2
3 X 2
4 X 2
5 A 3
6 B
:38, schrieb Bart Smissaert:
>
>> Times I get (65000 records, subtracting 2 fields defined as text in
>> the same table)
>> gives me following times:
>> method with julianday 0.4 secs
>> method with unixepoch 0.6 secs
>> using ctime etc. via VB wrapper 1.2 secs
Hi Olaf,
Will give that a go.
I take it these files are not ready yet to put in a commercial app?
RBS
On Sun, Oct 7, 2012 at 10:23 PM, Olaf Schmidt wrote:
> Am 06.10.2012 19:38, schrieb Bart Smissaert:
>
>> Times I get (65000 records, subtracting 2 fields defined as text in
>&
es via your VB app?
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
>
> From: sqlite-users-boun...@sqlite.org [sql
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
>
> From: sqlite-users-boun...@sqlite.org [
> sqlite-users-boun...@sqlite.org ] on behalf of Bart
> Smissaert [bart.smiss
nced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Bart Smissaert [bart.smissa...@gmail.com]
> Sent: Saturday, October 06,
PM, Bart Smissaert wrote:
>>
>> Is there a way to subtract times in the text format hh:mm:ss
>> and return the difference in the same format?
>
>
> select time(julianday('03:22:11') - julianday('01:22:33') - .5);
> select time(strftime('%
ge (vb)
> Do you know where can i find a sample code.
> Thanks Again
>
> Sent from my iPhone
>
> On Oct 4, 2012, at 4:47 PM, Bart Smissaert
> wrote:
>
>> What is your coding language?
>> If VB then maybe via ODBC and ADO.
>>
>> RBS
>>
>>
What is your coding language?
If VB then maybe via ODBC and ADO.
RBS
On Thu, Oct 4, 2012 at 9:11 PM, Jorge wrote:
>> Hello,
> I am new in Sqlite. I would like to know what is the best option to update
> data in two directions from Sqlite to SQL Server 2005
>
> Thx
>
>>
> __
Now that is a proper bug!
Makes Excel crash OK here.
RBS
On 10/3/12, Klaus Keppler wrote:
> Hi,
>
> after upgrading from SQLite 3.7.13 to 3.7.14 our application crashed
> with a SEGFAULT located within SQLite.
> I boiled down the SQL statement and the tables used, and was able to
> reproduce th
Hi Olaf,
Thanks, will give that a try today and tell you if it is faster.
RBS
On Wed, Oct 3, 2012 at 3:55 AM, Olaf Schmidt wrote:
> Am 02.10.2012 21:23, schrieb Bart Smissaert:
>
>> Nice one, thanks for that.
>
>
> Just in case you use the COM-Wrapper - and this operati
Nice one, thanks for that.
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik wrote:
> On 10/2/2012 1:00 PM, Bart Smissaert wrote:
>>
>> Is there a way to subtract times in the text format hh:mm:ss
>> and return the difference in the same format?
>
>
> s
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format? I am sure it could be
done with various calculations and casts, but maybe there is a simple,
ready-made way to do this.
RBS
___
sqlite-users mailin
Why you need to convert?
What about the simple SQL I suggested?
RBS
On 9/24/12, Don Goyette wrote:
>
> Thank you for your reply and suggestions, Clemens.
>
> > With 60*60*24 seconds per day, the number of days since the Unix epoch
> is:
> sqlite> select strftime('%s', '2012-05-22') / (60*
Problem 1 seems simple:
delete from TableX where timestamp < 41115
I think the table name may need to be produced by code.
RBS
On Sun, Sep 23, 2012 at 12:06 PM, Don Goyette wrote:
> Hello Everyone,
>
>
>
> I'm using what has become a huge (3.5 GB) SQLite3 database that was created
> by an inv
t; ), COUNT( "Year2012" ), COUNT(
> "Year2013" ) FROM "Members"
>
> On 16 September 2012 17:48, Bart Smissaert wrote:
>
>> Hi John,
>>
>> Funny seeing you here on the SQLite forum.
>> Are these by any chance the ISUG members?
>> Doing a
Hi John,
Funny seeing you here on the SQLite forum.
Are these by any chance the ISUG members?
Doing a count without a WHERE clause is always likely to give different results
with the various SQL implications as far as I know.
Why not add a WHERE?
RBS
On Sun, Sep 16, 2012 at 5:17 PM, John Clegg
OK, thanks for confirming that.
RBS
On 9/12/12, Richard Hipp wrote:
> On Wed, Sep 12, 2012 at 6:15 AM, Bart Smissaert
> wrote:
>
>> Had a look at the new option as in the SQL above.
>> Noticed it will only return one record, even if there are more records
>> where
Had a look at the new option as in the SQL above.
Noticed it will only return one record, even if there are more records where
field1 equals max(field1).
I suppose it returns the first record it finds where field1 = max(field1).
Is this indeed how it works?
RBS
Hi Olaf,
I think it should be:
Select Left$(TheField, Instr(TheField,']')) From Tbl
Have tested you new dll's and all working fine as usual.
Bart
On 9/11/12, Olaf Schmidt wrote:
> Am 10.09.2012 17:17, schrieb Bart Smissaert:
>> Ah, OK. I have a feeling that needs t
Am 10.09.2012 17:17, schrieb Bart Smissaert:
>> Ah, OK. I have a feeling that needs to be done either in your
>> application code or with a user defined SQLite function.
>> Somebody may prove me wrong.
>
> Hi Bart,
>
> since I know you're using my COM-Wrapper, a la
Yes, but doesn't rtrim do an instr function with the same problems as
you mention?
RBS
On 9/10/12, Simon Slavin wrote:
>
> On 10 Sep 2012, at 5:36pm, Bart Smissaert wrote:
>
>> Yes, you are right there.
>> As rtrim incorporates an instr type of function I am not sur
tom]
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> You have to be very careful when parsing char sets like this to ensure your
> barriers are valid.
> __
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert wrote:
> Nice one! Works here.
>
> RBS
>
>
> On 9/10/12, Black, Michael (IS) wrote:
>> Does this work for you?
>>
>> SQLite
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) wrote:
> Does this work for you?
>
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(s);
> sqlite> insert into t values('Logging in user [aa
22.58]
>
> Sébastien Roux
>
>
> 2012/9/10 Bart Smissaert
>
>> Could your application supply the values in code?
>> What exactly are you trying to do?
>>
>> RBS
>>
>>
>>
>> On 9/10/12, Sébastien Roux wrote:
>> > Thanks Bart b
Could your application supply the values in code?
What exactly are you trying to do?
RBS
On 9/10/12, Sébastien Roux wrote:
> Thanks Bart but substr require hard coded positions I guess, I need to get
> this position dynamically!
>
> Sébastien Roux
>
> 2012/9/10 Bart Sm
http://www.sqlite.org/lang_corefunc.html
Look at substr
RBS
On 9/10/12, Sébastien Roux wrote:
> Hi,
>
> I'm looking for the[in]famous sqlite "*instr"* function which doesn't exist
> (searched the web so far without success). Also searched for a
> "*position"*function, without success too!
>
>
Thanks for that tip, useful to know that one.
RBS
On Tue, Jul 3, 2012 at 11:37 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 03/07/12 14:03, Bart Smissaert wrote:
>> OK, thanks, that confirms my suspicion then.
>
> SQLite can also
OK, thanks, that confirms my suspicion then.
RBS
On Tue, Jul 3, 2012 at 10:00 PM, Igor Tandetnik wrote:
> On 7/3/2012 4:53 PM, Bart Smissaert wrote:
>>
>> However if I do this:
>>
>> SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT
>> FROM
>> READCODE
>
Have the following table:
CREATE TABLE READCODE(
[SUBJECT_TYPE] TEXT,
[READ_CODE] TEXT,
[TERM30] TEXT,
[TERM60] TEXT,
[ENTRY_COUNT] INTEGER)
Records are ordered ascending on READ_CODE as the records are obtained
from an ordered array and inserted sequentially.
There is a non-unique index on Read
Should that zero not be a 1?
>From the documentation:
The left-most character of X is number 1
RBS
On 6/19/12, Yongil Jang wrote:
> Dear all,
>
> I've found following result when I try to use 'substr' function.
>
> sqlite> create table test (data text);
> sqlite> insert into test values ('01010
On 3/5/12, Fabio Spadaro wrote:
> Hi.
> Il giorno 05 marzo 2012 14:50, Rob Richardson ha
> scritto:
>
>> ... I keep hoping to find something better, because SQLite Spy does not
>> offer the ability to edit a table inside a grid ...
>> RobR
>>
>
> With sqlite root is possbile to edit the table ins
Yes, thanks had just figured that out the same.
Working fine now.
RBS
On Tue, Feb 7, 2012 at 11:17 PM, Richard Hipp wrote:
> On Tue, Feb 7, 2012 at 6:14 PM, Bart Smissaert
> wrote:
>
>> Have a table with an integer age field and a text age_group field.
>> Need to updat
Have a table with an integer age field and a text age_group field.
Need to update the age_group field according to the age.
Tried with several case when constructions, but sofar nil working, eg:
update pats set age_group =
(case
when age between(0 and 9) then '0 to 9'
when age between(10 and 19) t
http://www.sqlite.org/faq.html#q7
RBS
On 1/31/12, bhaskarReddy wrote:
>
> Hi Friends,
>
> Is there any way to find the number of columns and column
> types in a table.
>
>I tried with "select count(*) from tablename". But it is
> showing number of records.
>
> R
Jan 2012 at 21:58, Bart Smissaert wrote:
>
>> OK, so how you open those then with SQLiteRoot?
>
> I use PHP, thus:
>
> $dbh = new PDO ("sqlite:" . $db);
>
> where $db is a string like "/path/to/database".
>
> I don't know what SQL
OK, so how you open those then with SQLiteRoot?
RBS
On Mon, Jan 30, 2012 at 9:46 PM, Tim Streater wrote:
> On 30 Jan 2012 at 16:37, Bart Smissaert wrote:
>
>> How do you make it open database files that have an extension other than
>> .slt?
>
> Eh? None of my
How do you make it open database files that have an extension other than .slt?
RBS
On 1/30/12, Fabio Spadaro wrote:
> Hi
>
> 2012/1/30 bhaskarReddy
>
>>
>> Its working with only table name.
>>
>>
>> Regards,
>> Bhaskar.
>
>
> You could use a GUI to facilitate these requests, and your
> this re
> And FWIW, this query works as expected on MS SQL
Works on Firebird and produces one record with value 1.
RBS
On Wed, Nov 23, 2011 at 4:35 PM, Pavel Ivanov wrote:
> On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin wrote:
>> On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote:
>>
>>> sqlite> select
things are in fact simple.
RBS
On Sun, Nov 20, 2011 at 2:12 PM, Igor Tandetnik wrote:
> Bart Smissaert wrote:
>> In fact when doing something as you suggest:
>> select * from MyTable where ID <= 14 order by ID desc
>> I can make it a lot more efficient by adding a limi
any
records will be needed.
RBS
On Sun, Nov 20, 2011 at 12:15 AM, Igor Tandetnik wrote:
> Bart Smissaert wrote:
>> If we have the 14 (we know to start at 14) can we select the records
>> 14, 13, 12 and 11,
>> so the consecutive numbers, going down from 14?
>
> select * f
Say we have a table table1 with unique integer field ID.
Now we have the following data:
ID
1
2
3
4
11
12
13
14
If we have the 14 (we know to start at 14) can we select the records
14, 13, 12 and 11,
so the consecutive numbers, going down from 14?
RBS
__
That seems to be the answer and after some quick testing it looks it
makes it more efficient as well!
RBS
On Tue, Oct 18, 2011 at 1:36 PM, Kit wrote:
> 2011/10/16 Fabian :
>> How can you limit a count-query? I tried:
>> SELECT COUNT(*) FROM table LIMIT 5000
>
> SELECT min(COUNT(*),5000) FROM ta
He is trying to make it more efficient, so stop counting if count > X.
So setting the count after having counted the whole lot won't help.
RBS
On Sun, Oct 16, 2011 at 2:46 PM, Simon Slavin wrote:
>
> On 16 Oct 2011, at 1:21pm, Fabian wrote:
>
>> 2011/10/16 Frank Missel
>>
>>> What do you want
t 14, 2011 at 2:33 AM, Frank Missel wrote:
>> boun...@sqlite.org] On Behalf Of Bart Smissaert
>> Sent: 14 October 2011 04:05
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
> and
>> pivottable
On Thu, Oct 13, 2011 at 8:44 PM, Bart Smissaert
wrote:
>> My problem is that the data basis of the Pivottable will sometimes be
> millions of rows
>
> OK, I haven't got that problem and my pivots are based on a sheet range.
> Sheet range is based on a variant array obtaine
> My problem is that the data basis of the Pivottable will sometimes be
millions of rows
OK, I haven't got that problem and my pivots are based on a sheet range.
Sheet range is based on a variant array obtained from SQLite.
I will need to check, but I think you can use an array for the basis of a
, Frank Missel wrote:
> Hi Bart,
>
>> boun...@sqlite.org] On Behalf Of Bart Smissaert
>> No, the wrapper is not used that way and I don't think it can be used that
>> way.
>> The SQLite database is dealt with in VBA or VB6 code via this wrapper.
>> I su
ill use the ODBC driver.
>
> But are you saying that you are use the wrapper itself as a data source in
> Excel?
> And if so, how do you specify the wrapper as a data source?
>
>
> /Frank
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
database?
>
> /Frank
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Bart Smissaert
>> Sent: 14 October 2011 01:35
>> To: General Discussion of SQLite Database
>> Subject:
I use SQLite extensively as a data source in Excel and have never come
across this problem.
Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite.
If you can send me a workbook that clearly demonstrates the problem
then I can see if
I can deal with it with the above wrapper. I am sure
Yes, if the OP is not familiar with VBA then that is the best option.
If he is familiar with VBA then with the mentioned wrapper you could
write a simple
Excel add-in (.xla) that will allow you to dump data from SQLite to Excel.
RBS
On Sun, Jul 17, 2011 at 12:56 PM, Baruch Burstein wrote:
> Best
Best way to do this is probably with a VB SQLite wrapper and I would recommend
this one:
http://www.thecommon.net/3.html
Download from this link.
The Toolset-Binaries: (ca. 1.8MB)
There is example code showing you how to use it.
If you get stuck I can mail you a workbook that demonstrates it all.
gt; What happens when you let the query run the first time, delete the
> records, then re-run the query?
>
> If the same length of time is spent (50 seconds) then I'm at a loss.
> If the shorter length of time happens, I'd say blame caching.
>
> On 07/13/2011 06:00 A
Have the following query:
INSERT INTO A3Test7D4_J (PATIENT_ID, ENTRY_ID, READ_CODE, TERM_TEXT,
ADDED_DATE, START_DATE, NUMERIC_VALUE, TYPE_SPECIFIC_INFO) SELECT
E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.TERM_TEXT, E.ADDED_DATE,
E.START_DATE, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM A3Test7D4_E
> Try ON (EA.ENTRY_ID = +E.ENTRY_ID)
Yes, that works indeed nicely.
Thanks for that.
I keep forgetting these non-standard SQL tricks.
RBS
On Tue, Jul 12, 2011 at 9:28 PM, Igor Tandetnik wrote:
> On 7/12/2011 3:39 PM, Bart Smissaert wrote:
>> Joining a large table (ENTRY_ATTRI
Have checked and missing analyze is indeed not the cause of this
difference in query speed.
RBS
On Tue, Jul 12, 2011 at 9:06 PM, Bart Smissaert
wrote:
>> it thinks that scanning the whole ENTRY_ATTRIBUTES table will read
>> about 54855 rows. And you say that it has much more ro
; it thinks that scanning the whole ENTRY_ATTRIBUTES table will read
> about 54855 rows. And you say that it has much more rows. That's why
> optimizer selects sub-optimal plan.
> I think running ANALYZE on your database should fix selected plans and
> even first query will run muc
Joining a large table (ENTRY_ATTRIBUTES) and a small table
(BPNewENTRY) and putting the resulting records
in a third table, BP3. Large table may have a few million records and
small table a few hundred records.
The join field is called ENTRY_ID in both tables and this has a
non-unique index in the
Thanks, that works indeed fine.
Will use that.
RBS
On Thu, Mar 24, 2011 at 8:52 PM, Igor Tandetnik wrote:
> On 3/24/2011 4:47 PM, Bart Smissaert wrote:
>>> Deletes all but the most recent entry for each patient. Is this what you
>> are after?
>>
>> Yes, that is
e a few hundred out of a
few thousand.
RBS
On Thu, Mar 24, 2011 at 7:37 PM, Igor Tandetnik wrote:
> On 3/24/2011 3:32 PM, Bart Smissaert wrote:
>> Couldn't get this to work yet.
>> What would be the full SQL, including the order by clause?
>
> delete from xxx where
Couldn't get this to work yet.
What would be the full SQL, including the order by clause?
RBS
On Thu, Mar 24, 2011 at 7:13 PM, Igor Tandetnik wrote:
> On 3/24/2011 3:00 PM, Bart Smissaert wrote:
>> SQLite objects against this SQL, particularly the first t1 after xxx
>>
>
rom
> xxx t2
> where
> t1.patient_id = t2.patient_id
> order by
> t2.start_date desc limit 1))
>
> On 3/24/2011 12:00 PM, Bart Smissaert wrote:
>> delete
>> from
>> xxx t1
>> where not
>> t1.entry_id in(select
>> t2.entry_id
>>
SQLite objects against this SQL, particularly the first t1 after xxx
delete
from
xxx t1
where not
t1.entry_id in(select
t2.entry_id
from
xxx t2
where
t1.patient_id = t2.patient_id
order by
t2.start_date desc limit 1)
How could I achieve this with a different syntax?
RBS
401 - 500 of 535 matches
Mail list logo
