> through a version of dijkstra's routing algorithm
Just out of interest, what data is this working on?
RBS
On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri
wrote:
> [Not at all expert in sqlite but here's a practical example of speed up
> using ":memory:" and perhaps a slightly different strate
As you are using Acccess maybe you are using Olaf Schmidt's VB wrapper
dhRichclient3 and in that case you do things like this:
select
(cast(left$('16:30:00', 2) as integer) +
cast(mid$('16:30:00', 4, 2) as real) / 60) -
(cast(left$('08:00:00', 2) as integer) +
cast(mid$('08:00:00', 4, 2) as real)
Thanks, that one works indeed and will use that.
RBS
On Wed, Nov 24, 2010 at 11:08 AM, Swithun Crowe
wrote:
> Hello
>
> BS> Thanks, I tried that and it gives no error, but only gives the first
> BS> lot, not the bit after the union all.
>
> Ah. I hadn't tried with data. I don't know why the LIM
Thanks, I tried that and it gives no error, but only gives the first
lot, not the bit after the union all.
RBS
On Wed, Nov 24, 2010 at 10:27 AM, Swithun Crowe
wrote:
> Hello
>
> BS> select
> BS> patient_id
> BS> from
> BS> table1
> BS> where
> BS> age = 50
> BS> limit 6
> BS> union all
> BS> se
Trying to run this SQL:
select
patient_id
from
table1
where
age = 50
limit 6
union all
select
patient_id
from
table1
where
age = 60
limit 4
But it fails due to the limit clause before the union.
Would there be a way round this?
RBS
___
sqlite-users ma
Tried your SQL, but it doesn't run.
Will fiddle it and see if I can make it work.
RBS
On Wed, Nov 17, 2010 at 9:00 AM, luuk34 wrote:
> On 17-11-10 09:58, Bart Smissaert wrote:
>> What do you suggest should be the full SQL then?
>>
> select t1.patient_id
>
What do you suggest should be the full SQL then?
RBS
On Wed, Nov 17, 2010 at 8:16 AM, luuk34 wrote:
> On 17-11-10 00:17, Petite Abeille wrote:
>> select t1.patient_id
>> from table1 t1
>> join (
>> select table1.address,
>> min( table1.date_of_bi
Tried your SQL, but it doesn't look right and didn't run. Will see if
I can alter it.
RBS
On Tue, Nov 16, 2010 at 11:17 PM, Petite Abeille
wrote:
>
> On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote:
>
>> This seems to work fine,
>
> Then you are golden :)
>
That is a strange construction and for now I haven't got it to work
yet in my VB application.
It does run though in Firefox SQLite manager. Maybe after all the SQL
I came up with in the end wasn't that bad.
RBS
On Wed, Nov 17, 2010 at 12:09 AM, Igor Tandetnik wrote:
> Bart Smi
Have (simplified) a table like this:
CREATE TABLE TABLE1(
[PATIENT_ID] INTEGER PRIMARY KEY,
[ADDRESS] TEXT,
[DATE_OF_BIRTH] TEXT)
DATE_OF_BIRTH is in the ISO8601 format -mm-dd
Now I need a SQL to find the oldest patie
To use SQLite in classic VB or VBA you need this free wrapper:
http://www.thecommon.net/3.html
I have been using it for a few years now (in a commercial application)
and it is fast and easy plus excellent support from the author Olaf
Schmidt.
RBS
On Sat, Sep 11, 2010 at 9:27 PM, Bob Keeland wro
Glob works fine as well with the [*] and is still case-sensitive and
that is how it is intended.
So this works fine:
select field1 from table1 where field1 glob '*FH*[*]'
RBS
On Wed, May 26, 2010 at 8:22 PM, Olaf Schmidt wrote:
>
> "Bart Smissaert"
> "...Where SomeColumnContent Like '%someother[*]part%'
Thanks Olaf, that works fine.
As my customers won't get this I think I might let my code take care of this.
How would it work with glob?
RBS
On Wed, May 26, 2010 at 7:05 PM, Olaf Schmidt wrote:
>
> &
Yes, it must be either my code or the wrapper to blame.
Thanks for confirming.
RBS
On Wed, May 26, 2010 at 6:18 PM, Black, Michael (IS)
wrote:
> This works for me:
>
> sqlite> create table t(t varchar);
> sqlite> insert into t values('Testing*with asterisk');
> sqlite> insert into t values('Te
> I use vb
What wrapper is that?
RBS
On Wed, May 26, 2010 at 6:14 PM, ro...@zhole.com wrote:
> I use vb or in SQLlite Expert the * does not seem to work
> From my testing * doesn't seem to work in the likeif I use '%33' it returns
> everything with test33if I use '33%' it returns everything wi
Yes, thanks, it looks it might indeed be the (VB) wrapper that is to blame here.
Will contact the developer.
RBS
On Wed, May 26, 2010 at 5:53 PM, Jean-Christophe Deschamps
wrote:
>
>>How do I search for the asterix character * ??
>>
>>This doesn't work:
>>select field1 from table1 where field1
How do I search for the asterix character * ??
This doesn't work:
select field1 from table1 where field1 like '%FH%*%'
as the * character here seems to be ignored.
Using the latest version of SQLite.
RBS
___
sqlite-users mailing list
sqlite-users@sqli
If arrDates(c + btLBAdd) Then
2100.SetText c + btLBAdd, Chr(32) 'this is to prevent
date formatting in the Excel sheet
2110 Else
2120.SetNull c + btLBAdd
2130 End If
2140End If
2150 Next c
2160 .Execute
2210
nault wrote:
> On Tue, 18 May 2010 11:48:06 +0100, Bart Smissaert
> wrote:
>>You can run SQL on sheet ranges.
>>Just need to make sure that the workbook is saved and closed as there is a bug
>>to do with ADO causing a memory leak.
>
> Thanks for the tip. I'll inves
You can run SQL on sheet ranges.
Just need to make sure that the workbook is saved and closed as there is a bug
to do with ADO causing a memory leak.
RBS
On Tue, May 18, 2010 at 11:19 AM, Gilles Ganault wrote:
> On Mon, 17 May 2010 22:55:47 -0700, Matt Young
> wrote:
>>I have that problem. A s
Thanks; yes, that works.
Will need to add maybe a compound index to make it faster.
RBS
On Fri, Apr 23, 2010 at 11:38 PM, Igor Tandetnik wrote:
> Bart Smissaert wrote:
>> Simplified there is a table like this:
>>
>> create table xxx(
>> [entry_id] integer pr
ect entry_id from xxx
> where person_id = ?
> order by entry_id desc
> limit 3;
>
>
> Pavel
>
> On Fri, Apr 23, 2010 at 6:03 PM, Bart Smissaert
> wrote:
>> Simplified there is a table like this:
>>
>> create table xxx(
>> [entry_id] integer primar
Simplified there is a table like this:
create table xxx(
[entry_id] integer primary_key,
[person_id] integer)
Now I need to retrieve the rows with the 3 highest entry_id numbers
for each person_id.
so for example (in reality entry_id can have gaps):
entry_id person_id
INDEX
IDX_A3Morb5B4_E_PATIENT_ID ORDER BY
RBS
On Thu, Mar 4, 2010 at 11:32 AM, Simon Davies
wrote:
> On 4 March 2010 10:54, Bart Smissaert wrote:
>> One table with this create SQL:
>>
>> CREATE TABLE Table1([PATIENT_ID] INTEGER,
>> [ENTRY_ID] INTEGER PRIMA
One table with this create SQL:
CREATE TABLE Table1([PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)
and a non-uique index on PATIENT_ID
Then the following 2 queries, that give the same result:
select *
from
Table1 t1
where
t1.entry_i
E
> ENTRY_ID NOT IN (
> SELECT T1.ENTRY_ID FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON
> (T1.PATIENT_ID = T2.PATIENT_ID)
> WHERE
> DATE(T1.ADDED_DATE, '+15 month') > T2.ADDED_DATE)
>
>
> From: Bart Smissaert
> To: Jon Dixon
month') > T2.ADDED_DATE)
This will use the index on the single field PATIENT_ID, which I need
in any case for other
queries. So, I now have a faster, simpler query and also less indexes needed.
Thanks again for all the suggestions.
RBS
On Sat, Sep 19, 2009 at 12:03 AM, Bart Smissaert
wrote:
aster.
RBS
On Fri, Sep 18, 2009 at 10:48 PM, Jon Dixon wrote:
> From:
> "Bart Smissaert"
> Then the SQL I was trying to improve:
>
> DELETE
> FROM
> TABLE2
> WHERE
> PATIENT_ID NOT IN (
> SELECT
> TABLE1.PATIENT_ID
> FROM
> TABLE1
is in the query inside function call to julianday(). Index
> on 2 fields could help only if your condition was e.g. T2.START_DATE >
> T1.START_DATE.
> And in most cases there's no benefit creating index on 1 field
> (PATIENT_ID) when there's already index on 2 fields (PATIENT_
; on 2 fields could help only if your condition was e.g. T2.START_DATE >
> T1.START_DATE.
> And in most cases there's no benefit creating index on 1 field
> (PATIENT_ID) when there's already index on 2 fields (PATIENT_ID,
> START_DATE) where PATIENT_ID is the first field.
>
&
This is what I am dealing with:
2 tables with exactly the same schema (but could be slightly
different, so can't put in same table):
CREATE TABLE TABLE1(
[PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)
The last 2 date fi
Have tried INDEXED BY and it does indeed work and force the use of the
specified index.
It didn't however make the query faster, so maybe the SQLite plan
generator is better than I thought!
RBS
On Thu, Sep 17, 2009 at 10:07 AM, Dan Kennedy wrote:
>
> On Sep 17, 2009, at 5:02 AM, Bar
Is it possible to tell SQLite to use a specified index?
I know you can use the + to excludes fields being used in an index,
but this doesn't help me in this particular case. I remember a
discussion about this and that this option might be added to SQLite,
but couldn't find it anywhere.
RBS
___
This is a VB function I use that will give you the idea.
I think I got this with help from Dennis Cote.
Public Function AgeAtISO8601Date(ByVal strDOB As String, _
ByVal strDate As String, _
Optional ByVal strAlias As String) As Str
Is it possible to tell SQLite to use a specified index?
I know you can use the + to excludes fields being used in an index,
but this doesn't help me in this particular case. I remember a
discussion about this and that this option might be added to SQLite,
but couldn't find it anywhere.
RBS
___
501 - 535 of 535 matches
Mail list logo