[sqlite] Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

[e-mail]

This "view" works in "DB Browser for SQLite" to get my Firefox Bookmarks . . . 
I then export them to Desktop as a .CSV, and run an Excel macro on them . . .

SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date
FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -

But then I thought, why not use SQLite3 in a .BAT file, and make it "one-click" 
?

The following code is what I have put into a .BAT file - I don't see any 
errors, and both just create a blank .CSV on my Desktop.1.) Broken out, for 
examination
2.) Quotes and apostrophes - not sure what's necessary - it seems like quotes 
are probably necessary, if there's a space in the path
3.) Just has apostrophes - not sure what's necessary
Any idea why this isn't working?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv

"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"

"SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id"

> "C:\Users\SLUDGE\Desktop\output.csv"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv 
"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"
 "SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id" > 
"C:\Users\SLUDGE\Desktop\output.csv"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv 
C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite
 SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,'unixepoch','localtime') AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id > 
C:\Users\SLUDGE\Desktop\output.csv
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with query

[e-mail mgbg25171]

Simon...

Yes I forgot the "where sn.nm='std1';" restriction and...

also see you've used 2 inner joins.

Thank you very much for your thoroughness.

It's very much appreciated.

> 
> On 06 April 2016 at 12:41 Simon Davies 
> wrote:
> 
> 
>     On 6 April 2016 at 12:22, e-mail mgbg25171 
> wrote:
> > Here are my tables specified as... tbl_nm | col1, col2...
> > std_nms | id, nm
> > raw_nms | id, nm
> > nm_pairs | id, std_nms_id, raw_nms_id
> >
> > I'm wondering how to supply a single std_nms.nm and get back a list of
> > pairs
> > i.e. std_nm.nm, raw_nms.nm
> > that reflect each record in nm_pairs with a std_nms_id = std_nms.id
> 
> SQLite version 3.8.11.1 2015-07-29 20:00:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> sqlite> create table std_nms( id integer, nm text );
> sqlite> create table raw_nms( id integer, nm text );
> sqlite>
> sqlite> create table nm_pairs( id integer, std_nms_id integer,
> raw_nms_id integer );
> sqlite>
> sqlite> insert into std_nms( id, nm ) values( 1, 'std1' ),( 2, 'std2'
> ),( 3, 'std3' );
> sqlite> insert into raw_nms( id, nm ) values( 1, 'raw1' ),( 2, 'raw2'
> ),( 3, 'raw3' );
> sqlite>
> sqlite> insert into nm_pairs( id, std_nms_id, raw_nms_id ) values( 1,
> 1, 1 ),( 2, 2, 2 ),( 3, 3, 3 ),( 4, 1, 3 );
> 
> 
> sqlite> select sn.nm, rn.nm
> from std_nms sn
> inner join nm_pairs nmp on nmp.std_nms_id=sn.id
> inner join raw_nms rn on nmp.raw_nms_id=rn.id
> where sn.nm='std1';
> std1|raw1
> std1|raw3
> 
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

[sqlite] help with my query...cracked it!

[e-mail mgbg25171]

Orig question

Here are my tables specified as... tbl_nm | col1, col2...
std_nms | id, nm
raw_nms | id, nm
nm_pairs | id, std_nms_id, raw_nms_id

I'm wondering how to supply a single std_nms.nm and get back a list of pairs
i.e. std_nm.nm, raw_nms.nm
that reflect each record in nm_pairs with a std_nms_id = std_nms.id

(of the record containing the supplied single std_nms.nm).
Thank you in anticipation.


and here's what I came up with...


select std_nms.nm, raw_nms.nm from std_nms,raw_nms
inner join nm_pairs on std_nms.id = nm_pairs.std_nms_id and raw_nms.id =
nm_pairs.raw_nms_id

[sqlite] help with query

[e-mail mgbg25171]

Here are my tables specified as... tbl_nm | col1, col2...
std_nms | id, nm
raw_nms | id, nm
nm_pairs | id, std_nms_id, raw_nms_id

I'm wondering how to supply a single std_nms.nm and get back a list of pairs
i.e. std_nm.nm, raw_nms.nm
that reflect each record in nm_pairs with a std_nms_id = std_nms.id

(of the record containing the supplied single std_nms.nm).
Thank you in anticipation.

Re: [sqlite] unique id for table

[e-mail mgbg25171]

Yes thanks for the advice...I've already combined the "raw" data i.e.
vectors straight out of the annual reports and the calculated vectors e.g..
"noplat" derived from it which I've given a type "calcd" in the type field
of the same table "itms".
There is some market wide data however, which might not fit into this
vector table Even though the individual items are stored as individual
records.I'm just at the stage of integrating SQLITE with forth and
everything's up for grabs at the moment.



On 23 June 2013 12:42, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 23 Jun 2013, at 12:26pm, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>
> wrote:
>
> > The "table as a number" idea is necessary to fit my model
> > where word definitions are stored as streams of code pointers (NUMBERS)
> > that just get called.
> > i.e. EVERYTHING has to be a number
>
> That makes perfect sense.
>
> If these tables all have the same fields in you might want to refactor
> your SQLite schema so that these tables are actually all one big table.
>  The thing you currently consider to be a table name would just be a field
> in the big table.
>
> This would give you a single rowid which would indicate both the table and
> the (currently) row in that table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique id for table

[e-mail mgbg25171]

I'm writing a variation of forth (It's got an IDE and 3-level stepping
debugger already)
that's actually a company valuation programming language (Can't get on with
spreadsheets hiding everything)
It accomodates words that are financial report line items (vectors) and
their manipulation
and I'm using SQLITE to store such vectors scraped from pdfs
at various stages i.e. until they fit a standard form allowing comparison
between companies.
The "table as a number" idea is necessary to fit my model
where word definitions are stored as streams of code pointers (NUMBERS)
that just get called.
i.e. EVERYTHING has to be a number
I'm therefore proposing to get at line items and any other piece of
financial info using a number for the table and one for the first row-id
from which you can get the name and type to search for the other values in
the vector.
That's about it and thanks for your interest!







On 23 June 2013 10:06, RSmith <rsm...@rsweb.co.za> wrote:

> There is of course no straight-forward way to do this, as I've noted Simon
> said already. But my curiosity is very piqued - Mind sharing what kind of
> application requires number-only calling? You using this on a calculator of
> sorts? - If so (or otherwise), some details would be interesting to know.
> (I do some embedded systems, never tried SQLite on it but might, hence the
> interest).
>
> I reply On-list as others might be interested, but will take the
> discussion off-list as it may not really be in the scope of the list (I
> think) if your awaited reply prompts further discussion.
>
> Have a great day!
>
> On 2013/06/22 20:27, e-mail mgbg25171 wrote:
>
>> I need to access a row just using numbers.
>> The row id's fine but can I specify the table using a numeric id too?
>> If not I'll just create a look-up table so there's no problem.
>> I'm just wondering...
>> Any help much appreciated.
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique id for table

[e-mail mgbg25171]

Thanks very much Simon
Looks like plan B then.


On 22 June 2013 19:40, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 22 Jun 2013, at 7:27pm, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>
> wrote:
>
> > I need to access a row just using numbers.
> > The row id's fine but can I specify the table using a numeric id too?
>
> This command can be used to get tables numbered:
>
> SELECT rowid,name FROM sqlite_master WHERE type='table'
>
> However, if you have tables A, B and C and delete one of them, the numbers
> for other tables can change.  Even creating or deleting indexes can change
> rowids.  So it’s valid only until your schema changes in some way.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unique id for table

[e-mail mgbg25171]

I need to access a row just using numbers.
The row id's fine but can I specify the table using a numeric id too?
If not I'll just create a look-up table so there's no problem.
I'm just wondering...
Any help much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting transactions working with prepare, step & finallise

[e-mail mgbg25171]

Simon
Thanks very much for the direction


On 12 June 2013 17:32, Simon Davies <simon.james.dav...@gmail.com> wrote:

> On 12 June 2013 16:50, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>
> wrote:
> > Tbank you in anticipation for any forthcoming advice
> > I include my program below in Powerbasic (not disimilar to C)
> > and wonder what I need to do in order to "wrap"
> > multiple statements (which use prepare,step & finalise) as transactions?
> >
> .
> .
> .
> .
> >
> > FUNCTION exec_sql( _
> >hDB AS DWORD, _
> >hD AS DWORD, _
> >BYVAL pzSql AS ASCIZ PTR, _
> >BYVAL pzErr_msg AS ASCIZ PTR _
> >) AS LONG
> >
> >LOCAL lresult   AS LONG
> >LOCAL pzTailAS ASCIZ PTR
> >LOCAL pzPreparedAS ASCIZ PTR
> >LOCAL pzStmtAS ASCIZ PTR
> >LOCAL azcols()  AS ASCIZ PTR  ' array of dword pointers to
> > column name strings
> >LOCAL azvals()  AS ASCIZ PTR  ' array of dword pointers to
> > column values
> >LOCAL ncols AS LONG   ' the column count
> >LOCAL nErmsgAS LONG
> >LOCAL szErmsg   AS ASCIZ * 512
> >LOCAL nRetryAS LONG
> >LOCAL s AS STRING
> >LOCAL i, l  AS LONG
> >
> >lresult = %SQLITE_OK
> >
> >IF @pzSql = "" THEN
> >   FUNCTION = %SQLITE_OK
> >   EXIT FUNCTION ' called with null SQL statement
> >END IF
> >
> >lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )
>
> Your exec_sql function is passed multiple sql statements in a single
> string, but you are only ever preparing the first statement.
>
> Reread http://www.sqlite.org/c3ref/prepare.html with particular focus on
> pzTail
>
> .
> .
> .
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] getting transactions working with prepare, step & finallise

[e-mail mgbg25171]

Tbank you in anticipation for any forthcoming advice
I include my program below in Powerbasic (not disimilar to C)
and wonder what I need to do in order to "wrap"
multiple statements (which use prepare,step & finalise) as transactions?

#COMPILE EXE
#DIM ALL
#INCLUDE "sqlite3.inc" 'created 09 May 2013, 15:34:42

GLOBAL ghDb AS DWORD
GLOBAL gDBOPEN AS LONG

SUB Open_Database (fl_nm AS ASCIZ * 512, hd AS DWORD )
   IF gDBOPEN = 1 THEN
  sqlite3_close(ghDb)
   END IF
   IF sqlite3_open(fl_nm, BYREF ghDb) <> %SQLITE_OK THEN
   ? "can't open database",,"error"
   gDBOPEN = 0
   EXIT SUB
   END IF
   gDBOPEN = 1
END SUB


FUNCTION exec_sql( _
   hDB AS DWORD, _
   hD AS DWORD, _
   BYVAL pzSql AS ASCIZ PTR, _
   BYVAL pzErr_msg AS ASCIZ PTR _
   ) AS LONG

   LOCAL lresult   AS LONG
   LOCAL pzTailAS ASCIZ PTR
   LOCAL pzPreparedAS ASCIZ PTR
   LOCAL pzStmtAS ASCIZ PTR
   LOCAL azcols()  AS ASCIZ PTR  ' array of dword pointers to
column name strings
   LOCAL azvals()  AS ASCIZ PTR  ' array of dword pointers to
column values
   LOCAL ncols AS LONG   ' the column count
   LOCAL nErmsgAS LONG
   LOCAL szErmsg   AS ASCIZ * 512
   LOCAL nRetryAS LONG
   LOCAL s AS STRING
   LOCAL i, l  AS LONG

   lresult = %SQLITE_OK

   IF @pzSql = "" THEN
  FUNCTION = %SQLITE_OK
  EXIT FUNCTION ' called with null SQL statement
   END IF

   lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )

   IF lresult <> %SQLITE_OK THEN
  GOTO finish
   END IF

   IF pzPrepared = 0 THEN
  GOTO finish
   END IF

   ncols = sqlite3_column_count(pzPrepared)
   REDIM azcols(0 TO ncols-1)
   FOR i = 0 TO ncols -1
   azcols(i) = sqlite3_column_name(pzPrepared, i)
   ? @azcols(i),,"col heading"
   NEXT

   REDIM azvals(0 TO ncols-1)
   DO WHILE 1
  lresult = sqlite3_step(pzPrepared)
s = ""
  SELECT CASE lresult
 CASE %SQLITE_ROW
FOR i = 0 TO ncols - 1
   azvals(i) = sqlite3_column_text(pzPrepared, i)
   s = s + @azvals(i) + ","
NEXT
? s,,"row"
 CASE %SQLITE_DONE
GOTO Finish
CASE ELSE
   ? "unexpected result = " + STR$(lresult)
   GOTO finish
END SELECT
LOOP
Finish:
   IF pzPrepared <> 0 THEN
  sqlite3_finalize(pzPrepared)
   END IF
   IF lresult <> %SQLITE_OK THEN
  lresult = sqlite3_errcode(hDB)
  pzErr_msg = sqlite3_errmsg(hDB)
   END IF
   FUNCTION = lresult
END FUNCTION


FUNCTION PBMAIN()
   LOCAL sSQL AS STRING
   LOCAL pzErr_msg AS ASCIZ PTR

'I don't seem to be able to make transactions work
'ie I was expecting to see a msgbox for each data row inserted
'but I don't
   sSQL =   "begin transaction;" + $CRLF +  _
"create table if not exists membership (member, date,
annualsubs, payments);" +  $CRLF + _
"insert into membership values ('Fred','10-APR-2010', 123,
54);" + $CRLF +  _
"insert into membership values ('James',   '10-APR-2010', 123,
0);" + $CRLF +  _
"insert into membership values ('Molly',   '10-MAY-2010', 123,
0);" + $CRLF +  _
"insert into membership values ('Angus',   '10-APR-2010',  90,
10);" + $CRLF +  _
"insert into membership values ('Patrick', '10-JUN-2010', 123,
0);" + $CRLF +  _
"select member, date ""date due"", annualsubs - payments
""amount due"" from membership;"  + _
"commit transaction;"

   open_database("test1.db",ghDb)

   sSQL =   "create table if not exists membership (member, date,
annualsubs, payments);"
   exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )

'no this doesn't seem to work either
'   sSQL =   "begin transaction; " & "insert into membership values
('Fred','10-APR-2010', 123, 54);" & " commit transaction;"
'if I replace the above line with this one...it's fine
'I don't understand what it is that's missing to make transactions work

'btw...this replacement line works fine
   sSQL =   "insert into membership values ('Fred','10-APR-2010', 123,
54);"
   exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )

   sSQL =   "select member, date ""date due"", annualsubs - payments
""amount due"" from membership;"
   exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )

   sqlite3_free(BYVAL pzErr_msg)

   sqlite3_close(ghDb)
END FUNCTION
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] I asked for an email sending me a new password/letting me reset

[e-mail mgbg25171]

I just got confirmation re sending me a new password/reset email but it
hasn't arrived,
Not being able to log in and not getting emails seems very strange
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Igor, Michael
This syntax "create table copy as select * from input.t1;" is very new to
me but certainly does "the business" in conjuntion with "attach"
SQLIte Manager doesn't know what "dump" is
Irrespective...thank you very much for your help.


On 20 February 2013 13:56, Michael Black <mdblac...@yahoo.com> wrote:

> Sqlite3 run without any arguments
> But did I discover a bug too?
> Why doesn't .dump allow input.t1 to be shown?
>
> SQLite version 3.7.14.1 2012-10-04 19:37:12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> attach database "blah.db" as input;
> sqlite> create table input.t1(n);
> sqlite> insert into input.t1 values(1);
> sqlite> insert into input.t1 values(2);
> sqlite> create table copy as select * from input.t1;
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE copy(n);
> INSERT INTO "copy" VALUES(1);
> INSERT INTO "copy" VALUES(2);
> COMMIT;
> sqlite> .dump input.t1
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite> select * from t1;
> 1
> 2
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> Sent: Wednesday, February 20, 2013 7:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] got selected items ok but can't update using them
>
> Igor, Michael
> Thank you very much for your advice re how to use Update with compound
> queries
>
> I'm currently trying to copy the rows of stmnts with a blank itm field to a
> temporary table.
> I don't think virtual tables are implemented in my programming language's
> interface and so wanted to
> copy the stmnts table from the real database where itm='' to a table in a
> :memory: database.
> I appreciate that the statement is normally
> insert into tbl values (fld1 integer, fld2 text);
> but how do I fill a table in a :memory: db with the contents of another in
> a real database?
> I'll keep looking into it.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Thanks Simon...
Your's works a treat
Re your question about my error

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)

and here's my error

SQLiteManager: Likely SQL syntax error: UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)
 [ near "SELECT": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

Thanks for your help

On 20 February 2013 12:01, Simon Davies <simon.james.dav...@gmail.com>wrote:

> On 20 February 2013 11:36, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>
> wrote:
> > After a bit of a struggle I've got the fields I want but I'm failing to
> use
> > them in an update operation.
> > This
> >
> > SELECT i,o,n FROM
> > (
> > SELECT alias_id i,
> > (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> > (SELECT itm FROM std_itms where ID = std_id) n
> > FROM
> > (SELECT std_id, alias_id FROM alias_itms)
> > )
> >
> > works fine and gives me
> > i, o, n  where i, o, n equals id, old, new
> > 
> > 0, turnover, sales
> > 1, cost of sales, cogs
> >
> > now I want to say
> > update stmnts set itm = n where id = i
> > but I'm getting errors
>
> It would help to be clear what the errors are.
>
> >
> > Table fields
> > stmnts__id, orig_itm, itm
> > alias_itmsid, std_id, alias_id
> > std_itms_id, itm
> >
> > Any help much appreciated
>
> Maybe:
> update stmnts set itm=(select itm from std_itms s, alias_itms a on
> s.id=a.std_id where a.alias_id=stmnts.id);
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

After a bit of a struggle I've got the fields I want but I'm failing to use
them in an update operation.
This

SELECT i,o,n FROM
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)

works fine and gives me
i, o, n  where i, o, n equals id, old, new

0, turnover, sales
1, cost of sales, cogs

now I want to say
update stmnts set itm = n where id = i
but I'm getting errors

Table fields
stmnts__id, orig_itm, itm
alias_itmsid, std_id, alias_id
std_itms_id, itm

Any help much appreciated
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with select

[e-mail mgbg25171]

Yes that works great. Thanks Igor!

On 11 February 2013 22:24, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 2/11/2013 10:45 AM, e-mail mgbg25171 wrote:
>
>> Perhaps I'm making a meal of my question...
>> "All" I want to do is
>> select only records that have field f1 = ''  where it's other field f2
>> matches the value of f3 only in other records (1 or more) which have a
>> non-'' f1 value.
>>
>
> select * from MyTable where f1 = '' and f2 in
>   (select f3 from MyTable where f1 != '');
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with select

[e-mail mgbg25171]

I think this does it
sql_s = "select * from " & _
"(select * from tbl_tv a where new_tnode = '') " & _
"where pnode = 0 or pnode in " & _
"(select tnode from tbl_tv where new_tnode <> ''  )" & _
"and anode =  0 or anode in " & _
"(select tnode from tbl_tv where new_tnode <> ''  )"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with select

[e-mail mgbg25171]

Perhaps I'm making a meal of my question...
"All" I want to do is
select only records that have field f1 = ''  where it's other field f2
matches the value of f3 only in other records (1 or more) which have a
non-'' f1 value.

To explain the f1 field shows whether or not the record has been written to
a treeview or not i.e. non-'' means it has been written
and let's say that f2 is the parent node...and f3 is the old node handle
for one of the nodes just written.

This is probably a better summary of what I'm trying to do.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help with select

[e-mail mgbg25171]

Here's a chopped down query that demonstrates my difficulty
do
sql_s = "select * from tbl_tv where id in " & _
"(" & _
"select id from tbl_tv where " & _
"new_tnode = '' and "  & _
"pnode = 0 or " & _
"pnode in (select tnode from tbl_tv where new_tnode <>
'')"  & _
")limit 1;"
exit loop when you've got no more records with an empty new_tnode
loop

I'm trying to draw a treeview using a stored table
i.e.
CREATE TABLE tbl_tv(
idinteger primary key,
pnode  text,
anode  text,
tnode  text,
new_tnode  text,
txttext,
cnodes text)

pnode is parent node
anode is after node...ie node that comes before the one your going to draw
tnode is old this node
new_tnode is handle of treeview node you've JUST drawn
txt is label of treeview node
cnodes are old handles of child nodes

All the old node handles are useful because they show the tree structure

If I limit the query to
sql_s = "select * from tbl_tv where id in " & _
"(" & _
"select id from tbl_tv where " & _
"new_tnode = '' " & _
) limit 1;"

and write a 1 in each returned rec's new_tnode then I can cycle through all
nodes fine.

Unfortunately the more complete query at the top seems to keep returning
the first non-0 parent, non-0 after node record even though it's got a 1 in
the new_tnode field which I THOUGHT I'd precluded i.e. only want to return
records which have a '' newtnode but which have pnode and anode values that
match tnodes in records which have a new_tnode of 1.

Hope I explained my self

Heres the full procedure just in case it helps

SUB aaTbl_to_tv(frm$,tv_id&)
DEF_SQL_VARS
LOCAL i&, hTv&, ub&
LOCAL form$, props$
LOCAL t AS tTv_rec
form$  = gTv.form
props$ = gTv.props
sqlite3_open("db_spread",sql_h???)
DO
sql_s = "select * from tbl_tv where id in " & _
"(" & _
"select id from tbl_tv where " & _
"new_tnode = '' and "  & _
"pnode = 0 or " & _
"pnode in (select tnode from tbl_tv where new_tnode <>
'')"  & _
")limit 1;"
'"and " & _
'"pnode = 0 " & _
'"or " & _
'"pnode in " & _
'"(select tnode from tbl_tv where new_tnode <> '') " & _
'"and " & _
'"anode = 0 " & _
'"or " & _
'"anode in " & _
'"(select tnode from tbl_tv where new_tnode <> '') " & _
'") limit 1;"
do_it

IF UBOUND(sql_a) <> -1 THEN
aaSql_to_tTv_rec(sql_a(),t)

sql_s = "UPDATE tbl_tv SET new_tnode=fld_val WHERE id=id_val;"
REPLACE "fld_val" WITH $SQ & "1" & $SQ IN sql_s
REPLACE "id_val" WITH t.id IN sql_s
DO_IT

'aaTest_tTv_rec(t)

'now find new parent and after nodes
IF t.pnode <> "0" THEN
sql_s = "select new_tnode from tbl_tv " & _
"where tnode = tnode_val;"
REPLACE "tnode_val" WITH t.pnode IN sql_s
do_it
IF UBOUND(sql_a) THEN
? t.lbl & " setting parent to " & sql_a(1)
t.pnode = sql_a(1)
ELSE
? t.lbl & " parent is 0"
END IF
ELSE

END IF

IF t.anode <> "0" THEN
sql_s = "select new_tnode from tbl_tv " & _
"where tnode = tnode_val;"
REPLACE "tnode_val" WITH t.anode IN sql_s
do_it
IF UBOUND(sql_a) THEN
t.anode = sql_a(1)
? t.lbl & " setting t.anode to " & sql_a(1)
ELSE
? t.lbl & "no rec returned for anode"
END IF
ELSE
?  t.lbl & "changing t.anode from 0 to " & t.pnode
t.anode = t.pnode
END IF

hTV& = EZ_AddTVItem( _
form$, gTv.tv_id,  _
VAL(t.pnode), VAL(t.pnode), BYCOPY t.lbl, _
0,0,"" _
)
ELSE
? "no more blank new_tnode recs",,FUNCNAME$
EXIT DO
END IF
LOOP
sqlite3_close(sql_h???)
END SUB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] match on single column but with multiple value

[e-mail mgbg25171]

That's great Mike. Thanks very much!

On 10 February 2013 20:26, Mike King <making1...@gmail.com> wrote:

> Select * from tbl where col1 in ('a', 'b', 'c')
>
>
>
> On Sunday, 10 February 2013, e-mail mgbg25171 wrote:
>
> > Sorry if this is a very basic question but I'm just wondering if there's
> a
> > more elegant way of doing this
> >
> > select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c'
> >
> > i.e. selecting rows if a particular column has one of SEVERAL values.
> >
> > Any help much appreciated.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org <javascript:;>
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] match on single column but with multiple value

[e-mail mgbg25171]

Sorry if this is a very basic question but I'm just wondering if there's a
more elegant way of doing this

select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c'

i.e. selecting rows if a particular column has one of SEVERAL values.

Any help much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sorting two distinct groups

[e-mail mgbg25171]

Sorry for not responding sooner...unavoidably distracted...
Yes one firm has many calls records...
and you're looking for the time associated with the record of the LAST
call  you made to each firm.
Having obtained these "latest call" times for each FIRM
you then want to organise the firms in order of EARLIEST of those "latest
call" times first
i.e. the firm you called longest ago, first
I think your query does this and thank you very much for it


On 4 February 2013 14:25, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 2/4/2013 2:40 AM, e-mail mgbg25171 wrote:
>
>> Your query is extremely close
>> but for the repetition of the c.ids at the end
>>
>
> I'm not sure I understand. What is the relationship of "firms" and "calls"
> tables? Is it one-to-many (multiple calls for each firm)? If so, what is
> the meaning of calls.last? Are you updating this field for all calls for a
> firm whenever a new call is inserted?
>
> Perhaps you are looking for something like this:
>
>
> select f.id from firms f left join calls c on (f.id = c.firm_id)
> group by f.id order by min(c.last);
>
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sorting two distinct groups

[e-mail mgbg25171]

This seems to have answered part of my problem

SELECT id FROM firms f
left outer join
(select firm_id from calls group by firm_id) c
on f.id = c.firm_id
order by firm_id

On 4 February 2013 07:40, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>wrote:

> 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 "order by clause" for the firms stuff I can't
> quite see how to insert it without again causing an error.
>
> Any advice greatfully received.
> Dean
>
>
>
> On 4 February 2013 03:40, Igor Tandetnik <i...@tandetnik.org> wrote:
>
>> 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 tables firms and calls.
>>> I'd like to list all the firms for which no calls records exist
>>> then
>>> I'd like to list all firms for which calls records DO exist
>>> in order of earliest last call first
>>>
>>
>> select f.id from firms f left join calls c on (f.id = c.firm_id)
>> order by c.last;
>>
>> NULL compares less than any other value, so rows for which there are no
>> calls will sort at the top.
>> --
>> Igor Tandetnik
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sorting two distinct groups

[e-mail mgbg25171]

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 "order by clause" for the firms stuff I can't
quite see how to insert it without again causing an error.

Any advice greatfully received.
Dean


On 4 February 2013 03:40, Igor Tandetnik <i...@tandetnik.org> wrote:

> 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 tables firms and calls.
>> I'd like to list all the firms for which no calls records exist
>> then
>> I'd like to list all firms for which calls records DO exist
>> in order of earliest last call first
>>
>
> select f.id from firms f left join calls c on (f.id = c.firm_id)
> order by c.last;
>
> NULL compares less than any other value, so rows for which there are no
> calls will sort at the top.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sorting two distinct groups

[e-mail mgbg25171]

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 firms for which no calls records exist
then
I'd like to list all firms for which calls records DO exist
in order of earliest last call first i.e. the one I'd called furthest back
first
if that makes sense.
Igore helped me last time with an order by (), coalesce() structure
but I can't see how to map this to my current problem
Any advice very much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[e-mail mgbg25171]

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 <petite.abei...@gmail.com> wrote:

>
> On Feb 3, 2013, at 10:55 AM, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>
> 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 null'… as oppose to "foo != ''" for empty strings… in general,
> if you wish to maintain your sanity, do *not* use empty strings… just
> saying…
>
> http://en.wikipedia.org/wiki/Null_(SQL)
>
> All in all, you could rewrite your join along the following lines:
>
> select  firms.*
> fromfirms
>
> joincalls
> on  calls.firm_id = firms.id
> and nullif( calls.by_or_on, '' ) is not null -- FIXME: suspicious use
> of the empty string...
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[e-mail mgbg25171]

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 <mgbg25...@blueyonder.co.uk>wrote:

> 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 <mgbg25...@blueyonder.co.uk>wrote:
>
>> 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 firms.
>> In the table of calls to the firms, two of the fields are
>> unsurprisingly...FIRM_ID and
>> a a field called BY_OR_ON that records the date by which something must
>> be done.
>> BY_OR_ON is mostly null.
>> I want to FILTER the FIRMS records to EXCLUDE any which do not have a
>> single calls record with a non-null by field
>> I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which
>> will exclude many of the calls records.
>> I then want to SORT the FIRMS records to order them by earlest...latest
>> BY_OR_ON date for each firm, first...
>> if earlies...latest BY_OR_ON makes sense.
>>
>> I hope this helps to explain
>> Best Regards
>>
>>
>>
>>
>>
>> On 3 February 2013 03:39, Keith Medcalf <kmedc...@dessus.com> wrote:
>>
>>> > Mayhaps you mean:
>>> >
>>> >  Select f.*
>>> >from firms f
>>> > left join (select firm_id, max(by_or_on) as boo
>>> >  from calls
>>> >  group by firm_id
>>> >having by_or_on is not null
>>> >  order by by_or_on desc) c
>>> >   on c.firm_id = f.id
>>> > order by boo desc;
>>>
>>> Should be:
>>>
>>>  Select f.*
>>>from firms f
>>> left join (select firm_id, max(by_or_on) as boo
>>>  from calls
>>>  group by firm_id
>>>having max(by_or_on) is not null
>>>      order by max(by_or_on) desc) c
>>>   on c.firm_id = f.id
>>> order by boo desc;
>>>
>>> or the equivalent:
>>>
>>>  Select f.*
>>>from firms f
>>> left join (select firm_id, max(by_or_on) as boo
>>>  from calls
>>>  group by firm_id
>>>having boo is not null
>>>  order by boo desc) c
>>>   on c.firm_id = f.id
>>> order by boo desc;
>>>
>>> ---
>>> ()  ascii ribbon campaign against html e-mail
>>> /\  www.asciiribbon.org
>>>
>>>
>>> > -Original Message-
>>> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>> > boun...@sqlite.org] On Behalf Of Keith Medcalf
>>> > Sent: Saturday, 02 February, 2013 17:11
>>> > To: General Discussion of SQLite Database
>>> > Subject: Re: [sqlite] trying to exclude records which have a field
>>> that is
>>> > null
>>> >
>>> >
>>> > I'm surprised you are getting anything at all since the statement is
>>> > semantically invalid.
>>> >
>>> >  Select f.*
>>> >from firms f
>>> > left join (select firm_id, max(by_or_on) as boo
>>> >  from calls
>>> >  group by firm_id
>>> >having by_or_on is not null
>>> >  order by by_or_on desc)
>>> >   on c.firm_id = f.id
>>> > order by boo desc;
>>> >
>>> > Perhaps you can describe the result you are attempting to obtain.
>>> >
>>> > **the having clause filters the "return rows" of a "group by"
>>> (aggregate)
>>> > select.  Therefore the references in the HAVING can only test against
>>> > "returned columns", not against the "source columns" from which the
>>> result
>>> > is derived.  Once uses the WHERE clause to apply conditions to the
>>> input
>>> > o

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

[e-mail mgbg25171]

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 <mgbg25...@blueyonder.co.uk>wrote:

> 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 firms.
> In the table of calls to the firms, two of the fields are
> unsurprisingly...FIRM_ID and
> a a field called BY_OR_ON that records the date by which something must be
> done.
> BY_OR_ON is mostly null.
> I want to FILTER the FIRMS records to EXCLUDE any which do not have a
> single calls record with a non-null by field
> I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which will
> exclude many of the calls records.
> I then want to SORT the FIRMS records to order them by earlest...latest
> BY_OR_ON date for each firm, first...
> if earlies...latest BY_OR_ON makes sense.
>
> I hope this helps to explain
> Best Regards
>
>
>
>
>
> On 3 February 2013 03:39, Keith Medcalf <kmedc...@dessus.com> wrote:
>
>> > Mayhaps you mean:
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having by_or_on is not null
>> >  order by by_or_on desc) c
>> >   on c.firm_id = f.id
>> > order by boo desc;
>>
>> Should be:
>>
>>  Select f.*
>>from firms f
>> left join (select firm_id, max(by_or_on) as boo
>>  from calls
>>  group by firm_id
>>having max(by_or_on) is not null
>>  order by max(by_or_on) desc) c
>>   on c.firm_id = f.id
>> order by boo desc;
>>
>> or the equivalent:
>>
>>  Select f.*
>>from firms f
>> left join (select firm_id, max(by_or_on) as boo
>>  from calls
>>  group by firm_id
>>having boo is not null
>>  order by boo desc) c
>>   on c.firm_id = f.id
>> order by boo desc;
>>
>> ---
>> ()  ascii ribbon campaign against html e-mail
>> /\  www.asciiribbon.org
>>
>>
>> > -Original Message-
>> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> > boun...@sqlite.org] On Behalf Of Keith Medcalf
>> > Sent: Saturday, 02 February, 2013 17:11
>> > To: General Discussion of SQLite Database
>> > Subject: Re: [sqlite] trying to exclude records which have a field that
>> is
>> > null
>> >
>> >
>> > I'm surprised you are getting anything at all since the statement is
>> > semantically invalid.
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having by_or_on is not null
>> >  order by by_or_on desc)
>> >   on c.firm_id = f.id
>> > order by boo desc;
>> >
>> > Perhaps you can describe the result you are attempting to obtain.
>> >
>> > **the having clause filters the "return rows" of a "group by"
>> (aggregate)
>> > select.  Therefore the references in the HAVING can only test against
>> > "returned columns", not against the "source columns" from which the
>> result
>> > is derived.  Once uses the WHERE clause to apply conditions to the input
>> > of the aggregate, and HAVING to apply conditions to the output.
>> >
>> > **the same applies to the "order by" of a "group by" (aggregate) select.
>> > You can only order by the "returned result columns", not by the "source
>> > data columns".
>> >
>> > Mayhaps you mean:
>> >
>> >  Select f.*
>> >from firms f
>> > left join (select firm_id, max(by_or_on) as boo
>> >  from calls
>> >  group by firm_id
>> >having by_or_on is not null
>> >      order by by_or_on desc) c
>> >   on c.firm_id = f.id
>> > order by boo desc;
>> >
>> > of cour

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

[e-mail mgbg25171]

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 firms.
In the table of calls to the firms, two of the fields are
unsurprisingly...FIRM_ID and
a a field called BY_OR_ON that records the date by which something must be
done.
BY_OR_ON is mostly null.
I want to FILTER the FIRMS records to EXCLUDE any which do not have a
single calls record with a non-null by field
I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which will
exclude many of the calls records.
I then want to SORT the FIRMS records to order them by earlest...latest
BY_OR_ON date for each firm, first...
if earlies...latest BY_OR_ON makes sense.

I hope this helps to explain
Best Regards




On 3 February 2013 03:39, Keith Medcalf <kmedc...@dessus.com> wrote:

> > Mayhaps you mean:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having by_or_on is not null
> >  order by by_or_on desc) c
> >   on c.firm_id = f.id
> > order by boo desc;
>
> Should be:
>
>  Select f.*
>from firms f
> left join (select firm_id, max(by_or_on) as boo
>  from calls
>  group by firm_id
>having max(by_or_on) is not null
>  order by max(by_or_on) desc) c
>   on c.firm_id = f.id
> order by boo desc;
>
> or the equivalent:
>
>  Select f.*
>from firms f
> left join (select firm_id, max(by_or_on) as boo
>  from calls
>  group by firm_id
>having boo is not null
>  order by boo desc) c
>   on c.firm_id = f.id
> order by boo desc;
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Keith Medcalf
> > Sent: Saturday, 02 February, 2013 17:11
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] trying to exclude records which have a field that
> is
> > null
> >
> >
> > I'm surprised you are getting anything at all since the statement is
> > semantically invalid.
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having by_or_on is not null
> >  order by by_or_on desc)
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > Perhaps you can describe the result you are attempting to obtain.
> >
> > **the having clause filters the "return rows" of a "group by" (aggregate)
> > select.  Therefore the references in the HAVING can only test against
> > "returned columns", not against the "source columns" from which the
> result
> > is derived.  Once uses the WHERE clause to apply conditions to the input
> > of the aggregate, and HAVING to apply conditions to the output.
> >
> > **the same applies to the "order by" of a "group by" (aggregate) select.
> > You can only order by the "returned result columns", not by the "source
> > data columns".
> >
> > Mayhaps you mean:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> >  group by firm_id
> >having by_or_on is not null
> >  order by by_or_on desc) c
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > of course, the order by inside the table subquery is useless, so this
> > would become:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >      from calls
> >  group by firm_id
> >having boo is not null) c
> >   on c.firm_id = f.id
> > order by boo desc;
> >
> > but perhaps you really mean:
> >
> >  Select f.*
> >from firms f
> > left join (select firm_id, max(by_or_on) as boo
> >  from calls
> > where by_or_on is not null
> >  group by firm_id) c
> >   on c.firm_id = f.id
&g

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

[e-mail mgbg25171]

wSQL = _
"Select f.* " & _
"from firms f " & _
"left join " & _
"(" & _
"select firm_id, max(by_or_on) as boo " & _
"from calls " & _
"group by firm_id " & _
"having by_or_on is not null " & _<==THIS ISN'T DOING IT FOR ME
AND I'D LIKE TO KNOW WHY
"order by by_or_on desc" & _
") c " & _
"on c.firm_id = f.id " & _
"order by boo desc;"

Any help much appreciated
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering result sets

[e-mail mgbg25171]

Igor
>case when f.nexttime IS NOT NULL then 0 else 1 end
explains it very well and I see that your alternative is indeed an elegant
shortcut

Additionally...
thank you for putting me straight re NULL in SQL
I didn;t appreciate that

Your help is very much appreciated
Dean


On 19 December 2012 19:19, Igor Tandetnik  wrote:

> case when f.nexttime IS NOT NULL then 0 else 1 end
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering result sets

[e-mail mgbg25171]

Thank Igor
Again...your solution works a treat...

Re my confusion...please contrast these two which both work
i.e.
order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),
f.lastdate
vs
order by (case
when nexttime is not null then 0
when lasttime is not null then 1
else 2 end), nexttime, lasttime

You are of course correct re my requirement
i.e. Allow me to quote: "order results firstly by earlest *non-null/empty
string* next time"

Given this I fully understand Ordering by that which I require i.e.
non-null...non empty string and the latter of the above does precisely that
i.e. when nexttime is not null then 0... and... when lasttime is not null
BY CONTRAST the first query SEEMS TO MY NAIVE EYE to contradict my
requirement of not null i.e.
ORDER BY f.nexttime IS NULL

I HOPE THIS EXPLAINS MY CONFUSION
AS STATED BOTH WORK
IT'S JUST THAT THE FIRST ONE SEEMS COUNTER INTUITIVE
AND I'D LIKE TO UNDERSTAND THIS

BTW NULL = CHR$(0) vs '' = '' YES???


On 19 December 2012 13:42, Igor Tandetnik <i...@tandetnik.org> wrote:

> e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> >> order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime,
> ''),f.lastdate
> >
> > This worked fine re making sure that non-null nexttimes come BEFORE null
> > nexttimes.
> >
> > How would I extend this so that AFTER non-null nexttimes I get NON-NULL
> > lasttimes and then...
> > null nexttimes and lastimes in any order
>
> order by (case
> when nexttime is not null then 0
> when lasttime is not null then 1
> else 2 end), nexttime, lasttime
>
> > Also can I ask why you are ordering by nextime is null/'' at the
> > beginning...
>
> Because that's what you asked for. Allow me to quote: "order results
> firstly by earlest *non-null/empty string* next time" (emphasis mine). You
> do realize that NULL and empty string are two distinct values, right?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ordering result sets

[e-mail mgbg25171]

>order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime,
''),f.lastdate

This worked fine re making sure that non-null nexttimes come BEFORE null
nexttimes.

How would I extend this so that AFTER non-null nexttimes I get NON-NULL
lasttimes and then...
null nexttimes and lastimes in any order

Also can I ask why you are ordering by nextime is null/'' at the
beginning...
I don't really understand this

Regards Dean
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering result sets

[e-mail mgbg25171]

Igor
>I'm not sure I completely understand
I'm not surprised re-reading my requirement. It isn't clear at all.
I'll have a play with what you've given me first.
Thank you very much...not only for the solution but also for your
explanation which I really appreciate.
Dean

On 17 December 2012 00:39, Igor Tandetnik <i...@tandetnik.org> wrote:

> e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> > I have modified my program to have next time (not shown) as well as next
> > date
> > What I'd to know is...
> > How do I order results firstly by earlest non-null/empty string next time
> > (ALL DAYS HERE WILL BE TODAY) order and then BY earliest non-null/empty
> > string nextdate order
> > so...
> > I end up with a list of records in call time order followed by a list of
> > records beginning with the one called longest ago.
>
> I'm not sure I completely understand the requirement, but play with
> something like this:
>
> order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),
> f.lastdate
>
> The first expression is boolean, its result is 0 or 1, so what it does is
> separate all records into two groups, and sort the first one (the one for
> which the expression is false) ahead of the second one (where the
> expression is true). The other two expressions are each designed to sort
> one of these groups without affecting the other.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ordering result sets

[e-mail mgbg25171]

I am returning records if today is more than x days from lastdate or I have
previously specified a next date and that date is today.
Here's my query

"select f.*, lastdate, nextdate from" & _
"(" & _
"SELECT f.*, lastdate, nextdate" & _
"FROM firms f" & _
"LEFT JOIN" & _
"(SELECT firm_ID, MAX(contacted) AS lastdate,  MAX(arranged) AS
nextdate FROM calls group by firm_id) c" & _
"ON c.firm_id = f.id" & _
"Where (julianday(current_date) - julianday(lastdate) > 6)" & _
"OR lastdate IS NULL" & _
"OR (julianday(current_date) = julianday(nextdate))" & _
"OR nextdate IS NULL" & _
") f where f.status = 'ok'"

I have modified my program to have next time (not shown) as well as next
date
What I'd to know is...
How do I order results firstly by earlest non-null/empty string next time
(ALL DAYS HERE WILL BE TODAY) order and then BY earliest non-null/empty
string nextdate order
so...
I end up with a list of records in call time order followed by a list of
records beginning with the one called longest ago.
I hope that makes sense.
BTW Sorry if I haven't thanked you for helping me before.
I do get emails from the mailing list but...they're never mine or answers
to mine.
I'm just going to check the archives instead so just to let you know...I'm
ALWAYS appreciative of all help I get!!!
Thank you in anticipation
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test

[e-mail mgbg25171]

please ignore
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test ignore

[e-mail mgbg25171]

test
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] tesing please ignore

[e-mail mgbg25171]

please ignore
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] just a test...ignore

[e-mail mgbg25171]

just a test please ignore
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another example of windows users needing help. Fwd: sqlite.dll

[e-mail mgbg25171]

It's not easy being an incon 

On 4 December 2012 10:44, Richard Hipp  wrote:

> Here is another example of the kind of email I get on a regular basis.
>
> Note that I also get phone calls about this.  Sometimes at odd hours.
>
> -- Forwarded message --
> From: Åke Halvarson 
> Date: Tue, Dec 4, 2012 at 5:22 AM
> Subject: sqlite.dll
> To: d...@hwaci.com
>
>
> **
> Dear Sirs
>
> I am asked to download a file/program sqlite.dll to my computer to make it
> run better.
>
> How do I do that? I tried to do it from your download side but I got a
> file sqlite3.dll  is that the right one?
>
> Sincerely yours
>
> Åke Halvarson
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] just a test...please ignore

[e-mail mgbg25171]

test
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] just a test

[e-mail mgbg25171]

I've posted a couple of mails lately...I'm not getting them via the list or
any responses.
Admin says Igor responded to one of them...Thanks Igor!
This is just a test to see if the mail is coming to me (as a member of the
list).
Therefore please just ignore this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] get records for foreign keys which come from various places

[e-mail mgbg25171]

I've only just discovered that you can get rows for foreign keys doing this

SELECT f.*
FROM firms f
inner JOIN calls c
ON f.id = c.firm_id group by c.firm_id;

that's fine where the foreign keys are from a single field in a single
table
(i.e. firm_id in calls)
but
what if the foreign keys are from different flelds in different tables
all unioned together
i.e. id (only some of them) in the firms and firm_id in calls
What's the syntax for this?
Thank you in anticipation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] finding duplicate records i.e. records with same values across 4 colums...

[e-mail mgbg25171]

I'm using SQLIte.
All columns can have duplicate values but it would be helpful to report
on those rows which are identical across all columns.
More specifically I'm looking for matching itm values where the first 3
cols ALSO match but am not sure of the sqlite select query to do this.

epic, yr, statement, itm
==
mcro, 2002, income, revs
mcro, 2002, income, cogs
mcro, 2002, income, sg
mcro, 2002, income, cogs
mcro, 2003, balance, gdwil
etc

in the example I'm looking for a query that would return
mcro, 2002, income, cogs
mcro, 2002, income, cogs

any help much appreciated

BTW no field can be unique in the createtable statement
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] getting list of column names

[e-mail mgbg25171]

>
> http://www.sqlite.org/pragma.html#pragma_table_info
>

I wasn't aware of that.
Thank  you very much!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] getting list of column names

[e-mail mgbg25171]

Here's what I'm doing to get a list of column names as a first step in my
"add column" routine.
I see that limit 1 isn't limiting the output to the first row ie the column
names...
like it did when I used SQLitening.
Is there a better way to do this?
Any advice much appreciated.


int Append_column(char* col_nm, char* tbl_nm ){ //tbl comes last cos might
be optional

int max_lines = 5, max_length = 150, i=0, j;
char** ln;
ln = (char**)malloc(sizeof(char) * max_lines * max_length); //WOW...I
had to do an explict case her
 //
ln[0] = Join_pChars( 3, "SELECT * from ", tbl_nm , " LIMIT 1" );

do query
char** results = NULL;
int rows=0, columns=0;
res = sqlite3_get_table(handle, ln[0], , , , );
if (res){ fprintf(stderr, "SQL error: %s\n", err); sqlite3_free(err);
return 1; }


//test this gets the colnames for you tick
display query result

//limit 1 is giving the col names AND the 1st row that I don't want
//for (int rowCtr = 0; rowCtr <= rows; ++rowCtr){
//this is giving me just the col names
int rowCtr=0;

for (int colCtr = 0; colCtr < columns; ++colCtr){
int cellPosition = (rowCtr * columns) + colCtr;
printf( "%s\t", results[cellPosition] );
}
printf( "\n");
//}


return 0;
}


return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Luuk, Roger
Yes it is like a pivot table.
I was stuck on how you delete dimensions you decide you don't want anymore.
After a day thinking about it I think I've cracked it so...
Thanks very much for your assistance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

19901991  year   <= dimension

north sales108
cogs (5)   (4)
southsales 6 5
cogs (2)   (1)

regionline item   <==  dimensions

I just want to be able to specify different dimensional values and get back
rectangles of data that I can manipulate. I've already done the manipulation
stuff so it's just how to layout the data.
Query examples would be...

total = north + south returning 9, 8
total cogs = north.cogs + south.cogs returning -7, -5
1990   returning 10,-5,6,-2
north:1991   returning 8,-4
north:1990..1991  returning 10,8

Once you've created region I don't think you can delete it and all it's
entries cos that would delete everything i.e. I believe you have to leave at
least one value in the column whether it be null or north.



>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Simon only just saw your post so hope my image didn't come through.
It didn't on mine but makes my post pretty meaningless.
I'll try to do it in text
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

On 6 July 2011 15:39, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:

> I've done it in the email body
>



eg total income = north.total + south.total

where north and south are from dimension "region" and
sales, cogs are from dimension "line item"
ie all I need the queries to do is return "rectangles" data cells as a
result of various means of specifying them such as

1 the method above or
2 north.income.sales:1990 --->returns 1
3 north.total > returns 5000,4000

The dimensions will be dynamic so I want to be able to return the data to
it's form before the extra dimensions eg in this case region was added i.e.

Edit...nce you've created an extra dimension it doesn't look like you can
dimension it seems to me that you can't just delete it cos everything would
disappear.
It seems the best you can do is just reduce it down to one value or as you
say null but even then you need one entry.

Hope this helps.
BTW "income" is not a dimension value but just a convenience to
automatically select sales and cogs. I haven't quite worked out how to
represent this yet.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Thank you for your response
I didn't quite catch what you mean't but would be happy to give examples of
the app.
A picture paints 1000 words
Would it be ok to attach .png files of what I'm trying to do?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multidimensional representation

[e-mail mgbg25171]

Luuk
Reading your email again...I think you've misunderstood me
Each element of yy represent an instance of the WHOLE original array before
the dimension was added.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multidimensional representation

[e-mail mgbg25171]

Thanks for your response.
I think your question is my point i.e. indeed...which one do you keep?
It's a little ambiguous isn't it?
I suppose it makes most sense to return back to the data BEFORE you added
the yy dimension
which result in returning to...
xid   yid  data
1  1 3
2  1 4
1  2 5
2  2 6

So to "remove" the dimension you just seem to need to reduce it's elements
to ONE
otherwise you'd be removing all data.

An explanation of multidimensional arrays explained that the elements of
each new dimension held an array in itself.
If they were separate arrays they'd be fairly easy to get rid of.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multidimensional representation

[e-mail mgbg25171]

The layout where x and y are dimensions eg

 x
 12

y  1  | 3   4
2  | 5  6

might be represented the following fact table

xid   yid  data
1  1 3
2  1 4
1  2 5
2  2 6

If I added another dimension eg yy
then the following layout
 x
 1   2
yy   y
11  3   4
  2  5   6
21  7   8
  2  9  10

might be represented by this extended fact table
yy   xid   yid  data
 1  1  1 3
 1  2  1 4
 1  1  2 5
 1  2  2 6
+
 2  1  1 7
 2  2  1 8
 2  1  2 9
 2  2  2 10

That's all well and good until you decide you want to DROP the yy dimension.
How do you best organise table to represent a star schema type arrangement
so that you can easily INSERT/DELETE dimensions and the new data that
accompanies them.
Hope I've made myself clear.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Thank you Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Pavel and David...
I just moved some of the table insertions around to change their rowid
values and the results are STILL coming out in pos order so...
which wasn't what I was getting with my attempts so...
Thank you very much indeed for your advice and solution.
It is appreciated!

On 1 July 2011 18:41, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:

> > If you believe that
> result of a query differs depending on what order SQLite processes
> rows in then you are wrong.
>
> I am wrong!
>
>
> On 1 July 2011 18:38, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
>
>> Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
>> looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY
>> pos then it's going to return 1 2 3
>> Neither of which is what I want.
>> By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt =
>> 1990 and 1991" would return 1 2 and this is what I want.
>>
>>
>>
>>
>> On 1 July 2011 18:24, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>wrote:
>>
>>> Here's an example of what I'm trying to do with my query
>>> t_x
>>> rowid=1,pos=1, txt=1990
>>> rowid=2,pos=3, txt=1992
>>> rowid=3,pos=2, txt=1991
>>>
>>> t_y
>>> rowid=1,pos=3,txt="sg expenses"
>>> rowid=2,pos=2,txt="cogs"
>>> rowid=3,pos=1,txt='revenue'
>>>
>>> t_d
>>> rowid=1,xpos=1,ypos=1,d=$1
>>> rowid=2,xpos=1,ypos=2,d=$2
>>> rowid=3,xpos=1,ypos=3,d=$3
>>> rowid=4,xpos=3,ypos=1,d=$7
>>> rowid=5,xpos=3,ypos=2,d=$8
>>> rowid=6,xpos=3,ypos=3,d=$9
>>> rowid=7,xpos=2,ypos=1,d=$4
>>> rowid=8,xpos=2,ypos=2,d=$5
>>> rowid=9,xpos=2,ypos=3,d=$6
>>>
>>>
>>> So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order
>>>
>>> you'd see t_y as
>>> revenue
>>> cogs
>>> sg expenses
>>> cos that's pos order
>>>
>>> and you'd see t_d as
>>> 1990  1991   1992
>>> revenue1  23
>>> cogs4  56
>>> sg7 89
>>>
>>> ie the order in which rows are added i.e. rowid order is not the order
>>> of the row's position in the GUI (pos order is the GUI order)
>>>
>>> The query is to return those data cells encapsulated by the margin text
>>> values so...
>>> Select all cells BETWEEN 1990 and 1991 and cogs and sg
>>> The answer would be 4 5 7 8.
>>>
>>> Does this help to visualise what I'm trying to do?
>>>
>>>
>>>
>>>
>>> On 1 July 2011 18:14, Pavel Ivanov <paiva...@gmail.com> wrote:
>>>
>>>> > Its not a very good example because the two are adjacent and 'x1' and
>>>> 'x2'
>>>> > sound like they're adjacent too.
>>>>
>>>> They are not adjacent - 'x1123456' and a lot of other strings starting
>>>> with 'x1' are between them.
>>>>
>>>> > I'm only interested in the results of BETWEEN when you're looking at
>>>> x1 and
>>>> > x2 from the pos order perspective
>>>>
>>>> Then David's query is a way to go.
>>>>
>>>>
>>>> Pavel
>>>>
>>>>
>>>> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
>>>> <mgbg25...@blueyonder.co.uk> wrote:
>>>> > Pavel, David
>>>> > Thanks for bearing with me...
>>>> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
>>>> txt
>>>> > = 'x1' and the row where txt = 'x2'
>>>> > Yes that's right.
>>>> > Its not a very good example because the two are adjacent and 'x1' and
>>>> 'x2'
>>>> > sound like they're adjacent too.
>>>> > I'm only interested in the results of BETWEEN when you're looking at
>>>> x1 and
>>>> > x2 from the pos order perspective
>>>> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
>>>> rowid
>>>> > perspective/order.
>>>> > I'll write a better description of what I'm trying to do and come
>>>> back.
>>>> >
>>>> > On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote:
>>>> >
>>>> >> > I'll certainly try
>>>> >> >>SELECT pos FROM t

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

> If you believe that
result of a query differs depending on what order SQLite processes
rows in then you are wrong.

I am wrong!

On 1 July 2011 18:38, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:

> Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
> looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY
> pos then it's going to return 1 2 3
> Neither of which is what I want.
> By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt =
> 1990 and 1991" would return 1 2 and this is what I want.
>
>
>
>
> On 1 July 2011 18:24, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
>
>> Here's an example of what I'm trying to do with my query
>> t_x
>> rowid=1,pos=1, txt=1990
>> rowid=2,pos=3, txt=1992
>> rowid=3,pos=2, txt=1991
>>
>> t_y
>> rowid=1,pos=3,txt="sg expenses"
>> rowid=2,pos=2,txt="cogs"
>> rowid=3,pos=1,txt='revenue'
>>
>> t_d
>> rowid=1,xpos=1,ypos=1,d=$1
>> rowid=2,xpos=1,ypos=2,d=$2
>> rowid=3,xpos=1,ypos=3,d=$3
>> rowid=4,xpos=3,ypos=1,d=$7
>> rowid=5,xpos=3,ypos=2,d=$8
>> rowid=6,xpos=3,ypos=3,d=$9
>> rowid=7,xpos=2,ypos=1,d=$4
>> rowid=8,xpos=2,ypos=2,d=$5
>> rowid=9,xpos=2,ypos=3,d=$6
>>
>>
>> So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order
>>
>> you'd see t_y as
>> revenue
>> cogs
>> sg expenses
>> cos that's pos order
>>
>> and you'd see t_d as
>> 1990  1991   1992
>> revenue1  23
>> cogs4  56
>> sg7 89
>>
>> ie the order in which rows are added i.e. rowid order is not the order
>> of the row's position in the GUI (pos order is the GUI order)
>>
>> The query is to return those data cells encapsulated by the margin text
>> values so...
>> Select all cells BETWEEN 1990 and 1991 and cogs and sg
>> The answer would be 4 5 7 8.
>>
>> Does this help to visualise what I'm trying to do?
>>
>>
>>
>>
>> On 1 July 2011 18:14, Pavel Ivanov <paiva...@gmail.com> wrote:
>>
>>> > Its not a very good example because the two are adjacent and 'x1' and
>>> 'x2'
>>> > sound like they're adjacent too.
>>>
>>> They are not adjacent - 'x1123456' and a lot of other strings starting
>>> with 'x1' are between them.
>>>
>>> > I'm only interested in the results of BETWEEN when you're looking at x1
>>> and
>>> > x2 from the pos order perspective
>>>
>>> Then David's query is a way to go.
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
>>> <mgbg25...@blueyonder.co.uk> wrote:
>>> > Pavel, David
>>> > Thanks for bearing with me...
>>> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
>>> txt
>>> > = 'x1' and the row where txt = 'x2'
>>> > Yes that's right.
>>> > Its not a very good example because the two are adjacent and 'x1' and
>>> 'x2'
>>> > sound like they're adjacent too.
>>> > I'm only interested in the results of BETWEEN when you're looking at x1
>>> and
>>> > x2 from the pos order perspective
>>> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
>>> rowid
>>> > perspective/order.
>>> > I'll write a better description of what I'm trying to do and come back.
>>> >
>>> > On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> >
>>> >> > I'll certainly try
>>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>>> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
>>> than
>>> >> the
>>> >> > results just sorted by pos.
>>> >>
>>> >> Maybe I've missed something in this conversation? Please clarify how
>>> >> "results sorted by pos" will be different from "x1 and x2 to be
>>> >> ordered before BETWEEN sees". And tell us more clearly what results
>>> >> you want to see from your query. We certainly see that you want to get
>>> >> value of pos from all rows where value of txt lies between 'x1' and
>>> >> 'x2'. Now do you want those results to be order by value of pos (add
&g

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY
pos then it's going to return 1 2 3
Neither of which is what I want.
By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt =
1990 and 1991" would return 1 2 and this is what I want.



On 1 July 2011 18:24, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:

> Here's an example of what I'm trying to do with my query
> t_x
> rowid=1,pos=1, txt=1990
> rowid=2,pos=3, txt=1992
> rowid=3,pos=2, txt=1991
>
> t_y
> rowid=1,pos=3,txt="sg expenses"
> rowid=2,pos=2,txt="cogs"
> rowid=3,pos=1,txt='revenue'
>
> t_d
> rowid=1,xpos=1,ypos=1,d=$1
> rowid=2,xpos=1,ypos=2,d=$2
> rowid=3,xpos=1,ypos=3,d=$3
> rowid=4,xpos=3,ypos=1,d=$7
> rowid=5,xpos=3,ypos=2,d=$8
> rowid=6,xpos=3,ypos=3,d=$9
> rowid=7,xpos=2,ypos=1,d=$4
> rowid=8,xpos=2,ypos=2,d=$5
> rowid=9,xpos=2,ypos=3,d=$6
>
>
> So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order
>
> you'd see t_y as
> revenue
> cogs
> sg expenses
> cos that's pos order
>
> and you'd see t_d as
> 1990  1991   1992
> revenue1  23
> cogs4  56
> sg7 89
>
> ie the order in which rows are added i.e. rowid order is not the order
> of the row's position in the GUI (pos order is the GUI order)
>
> The query is to return those data cells encapsulated by the margin text
> values so...
> Select all cells BETWEEN 1990 and 1991 and cogs and sg
> The answer would be 4 5 7 8.
>
> Does this help to visualise what I'm trying to do?
>
>
>
>
> On 1 July 2011 18:14, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > Its not a very good example because the two are adjacent and 'x1' and
>> 'x2'
>> > sound like they're adjacent too.
>>
>> They are not adjacent - 'x1123456' and a lot of other strings starting
>> with 'x1' are between them.
>>
>> > I'm only interested in the results of BETWEEN when you're looking at x1
>> and
>> > x2 from the pos order perspective
>>
>> Then David's query is a way to go.
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
>> <mgbg25...@blueyonder.co.uk> wrote:
>> > Pavel, David
>> > Thanks for bearing with me...
>> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
>> txt
>> > = 'x1' and the row where txt = 'x2'
>> > Yes that's right.
>> > Its not a very good example because the two are adjacent and 'x1' and
>> 'x2'
>> > sound like they're adjacent too.
>> > I'm only interested in the results of BETWEEN when you're looking at x1
>> and
>> > x2 from the pos order perspective
>> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
>> rowid
>> > perspective/order.
>> > I'll write a better description of what I'm trying to do and come back.
>> >
>> > On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote:
>> >
>> >> > I'll certainly try
>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
>> than
>> >> the
>> >> > results just sorted by pos.
>> >>
>> >> Maybe I've missed something in this conversation? Please clarify how
>> >> "results sorted by pos" will be different from "x1 and x2 to be
>> >> ordered before BETWEEN sees". And tell us more clearly what results
>> >> you want to see from your query. We certainly see that you want to get
>> >> value of pos from all rows where value of txt lies between 'x1' and
>> >> 'x2'. Now do you want those results to be order by value of pos (add
>> >> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
>> >> in a random order (do not add ORDER BY at all)? If you believe that
>> >> result of a query differs depending on what order SQLite processes
>> >> rows in then you are wrong.
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
>> >> <mgbg25...@blueyonder.co.uk> wrote:
>> >> > I'll certainly try
>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> >> > but I need

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Here's an example of what I'm trying to do with my query
t_x
rowid=1,pos=1, txt=1990
rowid=2,pos=3, txt=1992
rowid=3,pos=2, txt=1991

t_y
rowid=1,pos=3,txt="sg expenses"
rowid=2,pos=2,txt="cogs"
rowid=3,pos=1,txt='revenue'

t_d
rowid=1,xpos=1,ypos=1,d=$1
rowid=2,xpos=1,ypos=2,d=$2
rowid=3,xpos=1,ypos=3,d=$3
rowid=4,xpos=3,ypos=1,d=$7
rowid=5,xpos=3,ypos=2,d=$8
rowid=6,xpos=3,ypos=3,d=$9
rowid=7,xpos=2,ypos=1,d=$4
rowid=8,xpos=2,ypos=2,d=$5
rowid=9,xpos=2,ypos=3,d=$6


So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order

you'd see t_y as
revenue
cogs
sg expenses
cos that's pos order

and you'd see t_d as
1990  1991   1992
revenue1  23
cogs4  56
sg7 89

ie the order in which rows are added i.e. rowid order is not the order
of the row's position in the GUI (pos order is the GUI order)

The query is to return those data cells encapsulated by the margin text
values so...
Select all cells BETWEEN 1990 and 1991 and cogs and sg
The answer would be 4 5 7 8.

Does this help to visualise what I'm trying to do?



On 1 July 2011 18:14, Pavel Ivanov <paiva...@gmail.com> wrote:

> > Its not a very good example because the two are adjacent and 'x1' and
> 'x2'
> > sound like they're adjacent too.
>
> They are not adjacent - 'x1123456' and a lot of other strings starting
> with 'x1' are between them.
>
> > I'm only interested in the results of BETWEEN when you're looking at x1
> and
> > x2 from the pos order perspective
>
> Then David's query is a way to go.
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
> <mgbg25...@blueyonder.co.uk> wrote:
> > Pavel, David
> > Thanks for bearing with me...
> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
> txt
> > = 'x1' and the row where txt = 'x2'
> > Yes that's right.
> > Its not a very good example because the two are adjacent and 'x1' and
> 'x2'
> > sound like they're adjacent too.
> > I'm only interested in the results of BETWEEN when you're looking at x1
> and
> > x2 from the pos order perspective
> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
> rowid
> > perspective/order.
> > I'll write a better description of what I'm trying to do and come back.
> >
> > On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote:
> >
> >> > I'll certainly try
> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
> than
> >> the
> >> > results just sorted by pos.
> >>
> >> Maybe I've missed something in this conversation? Please clarify how
> >> "results sorted by pos" will be different from "x1 and x2 to be
> >> ordered before BETWEEN sees". And tell us more clearly what results
> >> you want to see from your query. We certainly see that you want to get
> >> value of pos from all rows where value of txt lies between 'x1' and
> >> 'x2'. Now do you want those results to be order by value of pos (add
> >> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
> >> in a random order (do not add ORDER BY at all)? If you believe that
> >> result of a query differs depending on what order SQLite processes
> >> rows in then you are wrong.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
> >> <mgbg25...@blueyonder.co.uk> wrote:
> >> > I'll certainly try
> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
> than
> >> the
> >> > results just sorted by pos.
> >> >
> >> > I've just done this.
> >> >const char* sqlSelect ="SELECT d FROM t_d "
> >> >"WHERE xpos in "
> >> >"(SELECT pos FROM (SELECT * from t_x ORDER
> BY
> >> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
> >> >//bit for t_y omitted.
> >> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's
> results
> >> to
> >> > SELECT BETWEEN in pos order.
> >> > I am concerned about having to specify both xpos and pos and am not
> sure
> >> how
> >> > these two get reconciled.
> >> > I am getting results but want to add more data to the 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Pavel, David
Thanks for bearing with me...
> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt
= 'x1' and the row where txt = 'x2'
Yes that's right.
Its not a very good example because the two are adjacent and 'x1' and 'x2'
sound like they're adjacent too.
I'm only interested in the results of BETWEEN when you're looking at x1 and
x2 from the pos order perspective
ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the rowid
perspective/order.
I'll write a better description of what I'm trying to do and come back.

On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote:

> > I'll certainly try
> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
> the
> > results just sorted by pos.
>
> Maybe I've missed something in this conversation? Please clarify how
> "results sorted by pos" will be different from "x1 and x2 to be
> ordered before BETWEEN sees". And tell us more clearly what results
> you want to see from your query. We certainly see that you want to get
> value of pos from all rows where value of txt lies between 'x1' and
> 'x2'. Now do you want those results to be order by value of pos (add
> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
> in a random order (do not add ORDER BY at all)? If you believe that
> result of a query differs depending on what order SQLite processes
> rows in then you are wrong.
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
> <mgbg25...@blueyonder.co.uk> wrote:
> > I'll certainly try
> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
> the
> > results just sorted by pos.
> >
> > I've just done this.
> >const char* sqlSelect ="SELECT d FROM t_d "
> >"WHERE xpos in "
> >"(SELECT pos FROM (SELECT * from t_x ORDER BY
> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
> >//bit for t_y omitted.
> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's results
> to
> > SELECT BETWEEN in pos order.
> > I am concerned about having to specify both xpos and pos and am not sure
> how
> > these two get reconciled.
> > I am getting results but want to add more data to the tables to see whats
> > going on.
> >
> > Thank you for your assistance though.
> >
> > On 1 July 2011 17:07, Pavel Ivanov <paiva...@gmail.com> wrote:
> >
> >> > It strikes me that
> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> > needs to be operating on the results returned by
> >> > SELECT * FROM t_x BY ORDER BY pos
> >> > ie another level of query is required but I'm not sure of how you
> insert
> >> it.
> >>
> >> I don't understand what you are talking about here. You should write
> >> it like this:
> >>
> >> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
> >> <mgbg25...@blueyonder.co.uk> wrote:
> >> > Thx for your suggestion...
> >> > Yes "BY ORDER BY pos" has to be in there somewhere.
> >> > It strikes me that
> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> > needs to be operating on the results returned by
> >> > SELECT * FROM t_x BY ORDER BY pos
> >> > ie another level of query is required but I'm not sure of how you
> insert
> >> it.
> >> > I'll have a play.
> >> >
> >> >
> >> >
> >> > On 1 July 2011 16:12, Pavel Ivanov <paiva...@gmail.com> wrote:
> >> >
> >> >> > What I want to do is...make sure that when I say BETWEEN I really
> mean
> >> eg
> >> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by
> pos
> >> >> and
> >> >> > not rowid.
> >> >>
> >> >> So, can you add "ORDER BY pos" to your queries?
> >> >>
> >> >>
> >> >> Pavel
> >> >>
> >> >>
> >> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> >> >> <mgbg25...@blueyonder.co.uk> wrote:
> >> >> > Thank you 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

I'll certainly try
>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
but I need x1 and x2 to be ordered before BETWEEN sees them rather than the
results just sorted by pos.

I've just done this.
const char* sqlSelect ="SELECT d FROM t_d "
"WHERE xpos in "
"(SELECT pos FROM (SELECT * from t_x ORDER BY
pos) WHERE txt BETWEEN 'x1' AND 'x2')";
//bit for t_y omitted.
in the hope that (SELECT * from t_x ORDER BY pos) presents it's results to
SELECT BETWEEN in pos order.
I am concerned about having to specify both xpos and pos and am not sure how
these two get reconciled.
I am getting results but want to add more data to the tables to see whats
going on.

Thank you for your assistance though.

On 1 July 2011 17:07, Pavel Ivanov <paiva...@gmail.com> wrote:

> > It strikes me that
> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> > needs to be operating on the results returned by
> > SELECT * FROM t_x BY ORDER BY pos
> > ie another level of query is required but I'm not sure of how you insert
> it.
>
> I don't understand what you are talking about here. You should write
> it like this:
>
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
> <mgbg25...@blueyonder.co.uk> wrote:
> > Thx for your suggestion...
> > Yes "BY ORDER BY pos" has to be in there somewhere.
> > It strikes me that
> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> > needs to be operating on the results returned by
> > SELECT * FROM t_x BY ORDER BY pos
> > ie another level of query is required but I'm not sure of how you insert
> it.
> > I'll have a play.
> >
> >
> >
> > On 1 July 2011 16:12, Pavel Ivanov <paiva...@gmail.com> wrote:
> >
> >> > What I want to do is...make sure that when I say BETWEEN I really mean
> eg
> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> >> and
> >> > not rowid.
> >>
> >> So, can you add "ORDER BY pos" to your queries?
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> >> <mgbg25...@blueyonder.co.uk> wrote:
> >> > Thank you all for your responses.
> >> > I had to go out after posting and have just come back.
> >> > My concern is with...
> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> > and
> >> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
> >> >
> >> > t_x and t_y are dimension tables.
> >> > that hold the x and y margins of a spreadsheet.
> >> > The margins will have an implied order shown by pos
> >> > which will differ from the order in which rows are added (represented
> by
> >> > rowid).
> >> >
> >> > What I want to do is...make sure that when I say BETWEEN I really mean
> eg
> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> >> and
> >> > not rowid. I hope that helps explain why pos exists and is not rowid
> i.e.
> >> I
> >> > want to be able to "insert" and "delete" records "!in between" the
> >> existing
> >> > ones or at least make it look like that even if the records are
> >> physically
> >> > appended to the tables.
> >> > Hope this clarifies things and look forward to your thoughts.
> >> >
> >> >
> >> > On 1 July 2011 15:30, Pavel Ivanov <paiva...@gmail.com> wrote:
> >> >
> >> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >> >
> >> >> > It will work just fine, in that the results you see will appear in
> the
> >> >> ORDER you asked for.
> >> >>
> >> >> I believe that's not always true and is not required by SQL standard.
> >> >> Most probably 'select * from view_name' will return rows in the order
> >> >> written in the view. But 'select * from view_name where some_column =
> >> >> some_value' can already return rows in completely different order.
> And
> >> >> 'select * from table_name, view_name where some_condition' will
> almost
> >> >> certainly ignore any ORDER BY in the view.
> >> >>
> >> >> So ORDER BY in the view doesn't guarantee yo

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Thx for your suggestion...
Yes "BY ORDER BY pos" has to be in there somewhere.
It strikes me that
SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
needs to be operating on the results returned by
SELECT * FROM t_x BY ORDER BY pos
ie another level of query is required but I'm not sure of how you insert it.
I'll have a play.



On 1 July 2011 16:12, Pavel Ivanov <paiva...@gmail.com> wrote:

> > What I want to do is...make sure that when I say BETWEEN I really mean eg
> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> and
> > not rowid.
>
> So, can you add "ORDER BY pos" to your queries?
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> <mgbg25...@blueyonder.co.uk> wrote:
> > Thank you all for your responses.
> > I had to go out after posting and have just come back.
> > My concern is with...
> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> > and
> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
> >
> > t_x and t_y are dimension tables.
> > that hold the x and y margins of a spreadsheet.
> > The margins will have an implied order shown by pos
> > which will differ from the order in which rows are added (represented by
> > rowid).
> >
> > What I want to do is...make sure that when I say BETWEEN I really mean eg
> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> and
> > not rowid. I hope that helps explain why pos exists and is not rowid i.e.
> I
> > want to be able to "insert" and "delete" records "!in between" the
> existing
> > ones or at least make it look like that even if the records are
> physically
> > appended to the tables.
> > Hope this clarifies things and look forward to your thoughts.
> >
> >
> > On 1 July 2011 15:30, Pavel Ivanov <paiva...@gmail.com> wrote:
> >
> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >
> >> > It will work just fine, in that the results you see will appear in the
> >> ORDER you asked for.
> >>
> >> I believe that's not always true and is not required by SQL standard.
> >> Most probably 'select * from view_name' will return rows in the order
> >> written in the view. But 'select * from view_name where some_column =
> >> some_value' can already return rows in completely different order. And
> >> 'select * from table_name, view_name where some_condition' will almost
> >> certainly ignore any ORDER BY in the view.
> >>
> >> So ORDER BY in the view doesn't guarantee you anything.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin <slav...@bigfraud.org>
> >> wrote:
> >> >
> >> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
> >> >
> >> >> 2011/7/1 Simon Slavin <slav...@bigfraud.org>
> >> >>
> >> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
> >> >>>
> >> >>>> Isn't it possible to use a view for that?
> >> >>>
> >> >>> You can use a VIEW if you want, but VIEWs don't sort the table
> either.
> >>  A
> >> >>> VIEW is just a way of saving a SELECT query.  When you consult the
> VIEW
> >> >>> SQLite executes the SELECT.
> >> >>
> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >
> >> > It will work just fine, in that the results you see will appear in the
> >> ORDER you asked for.
> >> >
> >> > However, it has no influence on how data is stored.  In fact no table
> >> data is stored for a VIEW at all.  The thing stored is the parameters
> given
> >> when you created the VIEW.  Every time you refer to a VIEW in a SQL
> >> statement SQL goes back and looks at the VIEW specification again.
> >> >
> >> > Simon.
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Thank you all for your responses.
I had to go out after posting and have just come back.
My concern is with...
SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
and
SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.

t_x and t_y are dimension tables.
that hold the x and y margins of a spreadsheet.
The margins will have an implied order shown by pos
which will differ from the order in which rows are added (represented by
rowid).

What I want to do is...make sure that when I say BETWEEN I really mean eg
BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and
not rowid. I hope that helps explain why pos exists and is not rowid i.e. I
want to be able to "insert" and "delete" records "!in between" the existing
ones or at least make it look like that even if the records are physically
appended to the tables.
Hope this clarifies things and look forward to your thoughts.


On 1 July 2011 15:30, Pavel Ivanov  wrote:

> >> Putting the 'ORDER BY' clause in view won't work?
> >
> > It will work just fine, in that the results you see will appear in the
> ORDER you asked for.
>
> I believe that's not always true and is not required by SQL standard.
> Most probably 'select * from view_name' will return rows in the order
> written in the view. But 'select * from view_name where some_column =
> some_value' can already return rows in completely different order. And
> 'select * from table_name, view_name where some_condition' will almost
> certainly ignore any ORDER BY in the view.
>
> So ORDER BY in the view doesn't guarantee you anything.
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin 
> wrote:
> >
> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
> >
> >> 2011/7/1 Simon Slavin 
> >>
> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
> >>>
>  Isn't it possible to use a view for that?
> >>>
> >>> You can use a VIEW if you want, but VIEWs don't sort the table either.
>  A
> >>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
> >>> SQLite executes the SELECT.
> >>
> >> Putting the 'ORDER BY' clause in view won't work?
> >
> > It will work just fine, in that the results you see will appear in the
> ORDER you asked for.
> >
> > However, it has no influence on how data is stored.  In fact no table
> data is stored for a VIEW at all.  The thing stored is the parameters given
> when you created the VIEW.  Every time you refer to a VIEW in a SQL
> statement SQL goes back and looks at the VIEW specification again.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
queried and am not sure of the syntax.
Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
(by column pos)  BEFORE I do the SELECT BETWEEN on THEM
i.e. I purposefully inserted t_d row 1,1,1 to see if it would come out first
ie as 1. It doesn't any help much appreciated.
Apologies if my question isn't clear.

#include "stdafx.h"
#include "stdio.h"
#include "sqlite3.h"
#include //malloc
#include //strlen
#include //assert

/*i've made these global so...both Create_database AND Query_database can
SEE them ie it shows what vars need to be common*/
int res, ind = 0;
char** sql;
sqlite3_stmt *stmt;
sqlite3* db=NULL;
char* err=0;

static int Open_db(char* flnm){
if (!strlen(flnm)){ res=sqlite3_open(":memory:",); }
else{ res=sqlite3_open(flnm,); }
if (!db){ printf("Open_db() failed\n"); }
return res;
}


void Close_db(){
res = sqlite3_close(db);
}


int Exec(char * s){
res = sqlite3_exec(db,s,0,0,0);
if ( res ){
printf( "Exec error re %s %s\n", s, sqlite3_errmsg(db)  );
}
assert(res==0); //so you can concentrate on 1st error
return res;
}


int _tmain(int argc, _TCHAR* argv[]){ //default project main
Open_db("");
//===
Exec("CREATE TABLE IF NOT EXISTS t_x("
"pos integer UNIQUE NOT NULL,"
"txt text NOT NULL"
")"
);
Exec( "INSERT INTO t_x VALUES(1,'x1')" );
Exec( "INSERT INTO t_x VALUES(2,'x2')" );
//===
Exec("CREATE TABLE IF NOT EXISTS t_y("
"pos integer UNIQUE NOT NULL,"
"txt text NOT NULL"
")"
);
Exec( "INSERT INTO t_y VALUES(1,'y1')" );
Exec( "INSERT INTO t_y VALUES(2,'y2')" );
//===
Exec("CREATE TABLE IF NOT EXISTS t_d("
"xpos integer NOT NULL,"
"ypos integer NOT NULL,"
"d float "
")"
);
/*table layout
see onenote thoughts diary me at 30/06/2011 08:42
yx->
|1,2
V3,4  xy data
  VV V  */

Exec( "INSERT INTO t_d VALUES(1,2,3)" );
Exec( "INSERT INTO t_d VALUES(2,1,2)" );
Exec( "INSERT INTO t_d VALUES(2,2,4)" );
Exec( "INSERT INTO t_d VALUES(1,1,1)" );
//===

//
http://dcravey.wordpress.com/2011/03/21/using-sqlite-in-a-visual-c-application/
//= this block from url albeit modified by me
==
const char* sqlSelect ="SELECT d FROM t_d "
"where xpos in "
"(SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND
'x2') ";
"AND ypos in "
"(SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND
'y2')";

char** results = NULL;
int rows, columns;
res = sqlite3_get_table(db, sqlSelect, , , , );
if (res){
//printf( "error in sqlite3_get_table %s\n", s, sqlite3_errmsg(db)
);
sqlite3_free(err);
}
else{
// Display Table
for (int rowCtr = 0; rowCtr <= rows; ++rowCtr){
for (int colCtr = 0; colCtr < columns; ++colCtr){
int cellPosition = (rowCtr * columns) + colCtr;
printf( "%s\t", results[cellPosition] );
}
printf( "\n");

}
}
sqlite3_free_table(results);

//==


Close_db();
sqlite3_free(err);
getchar();
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

David, Simon...that's good to know...Thank you very much indeed!


On 21 June 2011 13:19, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 21 Jun 2011, at 12:59pm, e-mail mgbg25171 wrote:
>
> > I was looking at prepare/step/finalise as a means of avoiding the
> callback
> > inherent in sqlite3_exec().
>
> You do not need to use the callback if you don't want it to do anything.
>  Just pass a NULL there.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

Thank you Igor


On 21 June 2011 12:52, Igor Tandetnik <itandet...@mvps.org> wrote:

> e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> > Howto...multi-sqlite command string through sqlite3_prepare_v2() to
> create SINGLE statement
>
> You can't do that.
>
> > sql = "BEGIN";   //you need to add newline here
> > sql += "create table episodes (id integer primary key, season int, name
> > text)";
> > sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> > sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> > sql += "COMMIT";
>
> sqlite3_exec can be used to execute multiple statements like this in a
> single call. You do need to terminate each statement with a semicolon.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

Thank you for the clarification re...
sqlite3_prepareXXX() only processing 1 statement at a time as opposed to
sqlite3_exec() which...
can handle "combined multi statements" in one shot.
I was looking at prepare/step/finalise as a means of avoiding the callback
inherent in sqlite3_exec().
In the example I saw...the "combined multi statements" string was processed
by SQLite3_Get_Table which...
I assume can also handle "combined multi statements"
Thank you both for your assistance.
As you can probably gather...this is very new to me.

On 21 June 2011 12:48, David Bicking <dbic...@yahoo.com> wrote:

> On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote:
> > The commented out lines work.
> > I'm wondering...
> > a) is it possible to do what's not commented out
> > b) what's the syntax re the "sql =..." and "sql +=..." lines
> > Any help much appreciated!
> > >
> > sql = "BEGIN";   //you need to add newline here
> > sql += "create table episodes (id integer primary key, season int, name
> > text)";
> > sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> > sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> > sql += "COMMIT";
> > rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ),,);
> > rc = sqlite3_step(stmt);
>
> You will need to add semicolons within the quotes between each statement
> as someone has already pointed out.
>
> Secondly, prepare only prepares one statement, so you would have to loop
> through the statements. My C is rusty, but I think it is something like:
>
> tail = sql.c_str();
> while (tail)
> {
>   rc = sqlite3_prepare(db, tail, strlen(tail), , );
>   rc = sqlite3_step(stmt);
>   rc = sqlite3_finalize(stmt);
> }
>
>
> Alternatively, you can run the combined multi statements through
> sqlite3_exec() in one shot.
>
> David
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

Thank you very much for the response.
Unless I'm doing something foolish (always a possibility) that doesn't seem
to work so...
here's the whole test program.

#include "stdafx.h"
#include "sqlite3.h"
#include "stdio.h"
#include "string.h"

#include "string"
#include "iostream"
using namespace std;
int _tmain(int argc, _TCHAR* argv[]) //default project main
{
int rc, i, ncols;
sqlite3 *db;
sqlite3_stmt *stmt;
//char *sql;
//replaced by
string sql;

const char *tail;


rc = sqlite3_open("foods.db", );
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(1);
}
//== my additions to get thie definitive guide example working

//sql = "create table episodes (id integer primary key, season int, name
text);";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(2,3,'bob')";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);

sql = "BEGIN;";   //you need to add newline here
sql += "create table episodes (id integer primary key, season int, name
text);";
sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
sql += "COMMIT;";
rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), , );
rc = sqlite3_step(stmt);

//

sql = "select * from episodes;";
//rc = sqlite3_prepare(db, sql, strlen(sql), , );
//replaced by
rc = sqlite3_prepare(db, sql.c_str(), strlen(sql.c_str()), , );

if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
}

rc = sqlite3_step(stmt);
ncols = sqlite3_column_count(stmt);
while(rc == SQLITE_ROW) {

for(i=0; i < ncols; i++) {
fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
}
fprintf(stderr, "\n");
rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(db);

//if( remove( "foods.db" ) != 0 ) perror( "Error deleting file" ); else
puts( "File successfully deleted" );

getchar();

return 0;
}




On 21 June 2011 12:32, Black, Michael (IS) <michael.bla...@ngc.com> wrote:

> I believe this will work if you put the SQL-required semi-colons at the end
> of your statements.
>
>
>
> sql = "BEGIN;";   //you need to add newline here
> sql += "create table episodes (id integer primary key, season int, name
> text);";
> sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
> sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
> sql += "COMMIT;";
>
> I would say most people don't do this as any error returns won't tell you
> much.  You'd normally prepare each seperately.
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of e-mail mgbg25171 [mgbg25...@blueyonder.co.uk]
> Sent: Tuesday, June 21, 2011 6:22 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Howto...multi-sqlite command string through
> sqlite3_prepare_v2() to create SINGLE statement
>
> The commented out lines work.
> I'm wondering...
> a) is it possible to do what's not commented out
> b) what's the syntax re the "sql =..." and "sql +=..." lines
> Any help much appreciated!
> [code]
> //sql = "create table episodes (id integer primary key, season int, name
> text);";
> rc = sqlite3_prepare(db, sql, strlen(sql), , );
> replaced by
> //rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
> );
> //rc = sqlite3_step(stmt);
> //
> //sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
> rc = sqlite3_prepare(db, sql, strlen(sql), , );
> replaced by
> //rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
> );
> //rc = sqlite3_step(stmt);
> //
> //sql = "i

[sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

The commented out lines work.
I'm wondering...
a) is it possible to do what's not commented out
b) what's the syntax re the "sql =..." and "sql +=..." lines
Any help much appreciated!
[code]
//sql = "create table episodes (id integer primary key, season int, name
text);";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(2,3,'bob')";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);

sql = "BEGIN";   //you need to add newline here
sql += "create table episodes (id integer primary key, season int, name
text)";
sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
sql += "COMMIT";
rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), , );
rc = sqlite3_step(stmt);
[code]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users