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

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

[sqlite] help with query

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 201

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

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

[sqlite] help with query

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 =

[sqlite] order by not working in combination with random()

On 08/28/2015 09:36 PM, Scott Robison wrote: > On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail yahoo.com >> wrote: >> >> On 08/26/2015 09:03 PM, Richard Hipp wrote: >> >> Time stands still for multiple rows, as long as they are within the >>> same sql

[sqlite] order by not working in combination with random()

ith 10 rows only; it *should* have finished within milliseconds, but it repeated the same task as above, even the database size remained the same (28GB that is). On 08/28/2015 05:45 PM, R.Smith wrote: > > > On 2015-08-28 03:09 PM, Yahoo! Mail wrote: >> Where did you

[sqlite] order by not working in combination with random()

mith wrote: > > On 2015-08-28 01:17 PM, Yahoo! Mail wrote: >> Obviously you did not get my issue; something is wrong and your timer >> suggestion indicates this. During the execution of each command, I >> would monitor it with *watch "du test.db*"*. The j

[sqlite] order by not working in combination with random()

; create table t1(a datetime); vacuum t1; begin; with recursive c(x) as (values(1) union all select x + 1 from c where x < 100) insert into t1(a) select datetime('now') from c; commit; Run Time: real 209.612 user 4.724000 sys 21.588000* On 08/28/2015 01:48 PM, Clemens Ladisch wrote: >

[sqlite] order by not working in combination with random()

On 08/26/2015 09:03 PM, Richard Hipp wrote: > Time stands still for multiple rows, as long as they are within the > same sqlite3_step() call. For example, if you run: > > CREATE TABLE t1(a DATETIME); > WITH RECURSIVE > c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE

[sqlite] Why sqlite show qualified column names when selecting from views ?

e Aug 25 2015 19:45:27 CEST from "Simon Slavin" >Subject: Re: [sqlite] Why sqlite show qualified column names when selecting >from views ? > > On 25 Aug 2015, at 6:13pm, sqlite-mail wrote: > > >>When querying views sqlite shows qualified column names if they

[sqlite] Why sqlite show qualified column names when selecting from views ?

Hello ! When querying views sqlite shows qualified column names if they are specified individually. Is this the expected result or a bug ? This behavior breaks some of my code that uses column names for other purposes. Cheers ! output of "test-view-alias" SQL: SELECT a.* FROM

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

eric user defined functions. Anyone have any idea about this ? ? Cheers ! ? > Sat Aug 22 2015 13:01:32 CEST from "Richard Hipp" >Subject: Re: [sqlite] There is any reason to sqlite not expand "*" in >function calls ? > > On 8/22/15, sqlite-m

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

Hello ! I'm testing the new json functions and when I tried this: select json_array(*) as json from one_table; I've got : [] [] .. Then I tried with some custom functions accepting variable number of parameters and realize that "*" is not expanded for function calls. There

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

That's why I think that expose a basic SQL ANSI catalog would make this kind of work and others a lot easier. ? > Fri Aug 21 2015 22:13:00 CEST from "Simon Slavin" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 21 Aug 2015, at 9:07pm, Scott

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

d Hipp" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 8/21/15, sqlite-mail wrote: > >>Then do you think this is a bug ? >> > The word "bug" means it gets the wrong answer. > > The absenc

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

dependencies ? > > On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail >wrote: > >>Then do you think this is a bug ? >> >>>Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

Then do you think this is a bug ? > Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 21 Aug 2015, at 12:20pm, sqlite-mail wrote: > > >>Does anybody kno

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

t; > On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail > wrote: > > >>Hello ! >> >> Today I'm working with sqlite3 with attached databases and when I tried to >> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragma

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

Hello ! Today I'm working with sqlite3 with attached databases and when I tried to get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas do not work with qualified/prefixed names like: PRAGMA table_info(attached_db.one_table) ? Is this a bug ? Cheers !

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

Hello ! Here I'm fixing some typos and I also tested on postgresql and there all views are updated properly then I'll say is a bug in sqlite. Does anybody knows how to rename a table and all it's dependencies in one go ? ? The problem: a database has several tables and views that

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

Hello ! Does anybody knows how to rename a table and all it's dependencies in one go ? ? The problem: a database has several tables and views that reference/use other tables something simplified like this: CREATE TABLE a(id integer primary key, name text); CREATE TABLE

[sqlite] ATTACH DATABASE statement speed

is performed on all dependent tables and that can be a lot time consuming depending on the number of records on then. ? I've got this problem on a heavily foreign key constrained database and it took me a bit to realize that ! Cheers ! > @nameless person known as sqlite-mail, > Yes, I d

[sqlite] ATTACH DATABASE statement speed

Hello ! Do you have foreign keys on your tables ? And if so do you have indexes on then ? A database with foreign keys and no indexes can run very slow for mas insert/update/delete ? Cheers ! ? > Tue Aug 18 2015 12:38:51 CEST from "Paolo Bolzoni" > Subject: Re: [sqlite] ATTACH

[sqlite] Enhance the SELECT statement?

Hello ! The query you mention is not the same as: INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; Cheers ! > Mon Aug 17 2015 17:44:58 CEST from "John McKown" > Subject: [sqlite] Enhance the SELECT >statement? > > I use both SQLite3 and PostgreSQL. One thing

[sqlite] Patch to add sqlite3_trace_v2

Hello ! After seem the question about "how to sync sqlite3 databases" and proposed the creation/extension of sqlite3_trace function to make it easy to log changes to the database I implemented what I'll call sqlite3_trace_v2 that has an extra parameter to indicate if we only want to log sql

[sqlite] Database sybchronisation

Hello ! ? This request is a common requirement and in my opinion would be better solved by an extension to the actual sqlite3 api functions. ? Actually there is sqlite3_trace that can be used to watch all sql statements executed on a given session. ? One possible way would be to

[sqlite] Small bug in ".dump", ".schema" and ".fullschema"

Hello ! Working with sqlite3 I noticed that sqlite3 ".dump", ".schema" and ".fullschema" outputs the contents of the field "sql" stored in "sqlite_master" and if the sql statement ends with a comment the resulted dump will be invalid see example: === valid sql statement stored on

[sqlite] Patch that add ".dumpdata" and "BEGIN TRANSACTION; " command to shell.c

Hello again ! After sending the first patch I also realized that when sqlite3 dumps ".schema" or ".fullschema" it doesn't surround the dump with a transaction and that takes longer and makes the hard disk work hard. So I also surrounded ".schema" and ".fullschema" with a transaction with this

[sqlite] Patch that add ".dumpdata" command to shell.c

Hello ! Here is a small patch that adds ".dumpdata" for "shell.c" in sqlite3. This command should behave exactly like ".dump" but without the database schema. I 'm giving it with the same license as sqlite. Cheers !

[sqlite] Bugs/improvements to sqlite constraints

Hello ! After submitting several emails with subject "Bug in sqlite3_trace/trigger/delete" and only one reply to then so far I decided to look a bit more deep on the problem I'm facing using sqlite3 with one specific database and created a simpler database that can show several problems and

[sqlite] Bug in sqlite3_trace/trigger/delete

ype 0|0|0|SCAN TABLE account_account_template 0|0|0|SCAN TABLE account_account_template 0|0|0|SCAN TABLE account_account 0|0|0|SCAN TABLE account_account - > Fri Jul 31 2015 15:34:56 CEST from "sqlite-mail" > Subject: [sqlite] Bug in >sqlite3_trace/trigger/delete > > H

[sqlite] Bug in sqlite3_trace/trigger/delete

(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) ); COMMIT; - > Wed Aug 05 2015 12:16:09 CEST from "sqlite-mail" > Subject: Re: [sqlite] Bug in >sqlite3_trace/trigger/delete > > >>Hello ! >>

[sqlite] Bug in sqlite3_trace/trigger/delete

c_id) REFERENCES > ac(id), -- here we have the comma separating a constraint > CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) > ); > > COMMIT; > > - > > Thanks in advance for your time and attention ! > > Mon Aug 03 2015 13:08:08 CES

[sqlite] Bug in sqlite3_trace/trigger/delete

Hello? ! ? I'm using sqlite for a project and with this specific database https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB uncompressed) this is happening: ? -1 Registering an sqlite3_trace function when trying to delete a record just inserted on the table "res_users" the registered

[sqlite] What software is deployed more than SQLite?

I run a simple search on a Windows XP (virtual) machine of mine and found the following programs using SQLite: * Adobe Reader * Adobe Acrobat Professional * Avira Antivir (almost 400 million use it based on their official website) * Apple Application Support On 05/03/2015 11:32

[sqlite] What software is deployed more than SQLite?

How about curl? This is taken directly from the official website: " curl is used in command lines or scripts to transfer data. It is also used in cars, television sets, routers, printers, audio equipment, mobile phones, tablets, settop boxes, media players and is the internet transfer backbone

Re: [sqlite] unique id for table

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) > &g

Re: [sqlite] unique id for table

f-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 speci

Re: [sqlite] unique id for table

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. > >

[sqlite] unique id for table

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

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

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 > &

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

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

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

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

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

m 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

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

ntax 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:3

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

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) )

Re: [sqlite] help with select

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 t

[sqlite] help with select

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 " &

[sqlite] help with select

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

[sqlite] help with select

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 " & _

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

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

[sqlite] match on single column but with multiple value

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.

Re: [sqlite] sorting two distinct groups

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:

Re: [sqlite] sorting two distinct groups

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 que

Re: [sqlite] sorting two distinct groups

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: > >>

[sqlite] sorting two distinct groups

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

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

<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 n

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

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

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

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

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

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

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

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

Re: [sqlite] ordering result sets

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

Re: [sqlite] ordering result sets

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

[sqlite] ordering result sets

>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

Re: [sqlite] ordering result sets

: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 earles

[sqlite] ordering result sets

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" & _ "

[sqlite] test

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

[sqlite] test ignore

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

[sqlite] tesing please ignore

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

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

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

[sqlite] just a test...please ignore

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

[sqlite] just a test

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

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

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

Re: [sqlite] Stricter parsing rules

Hi Dan, I had some free time and looked into your request. Bear in mind I don't really know what I'm doing, but I managed to whip up this: http://paste.nachsoftware.com/SQLite3/BrksDfe9d421e8ed00d34f0fc50a59956af21byK Results: > ./sqlite3 SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter

Re: [sqlite] Stricter parsing rules

Hello Everyone, I'm glad to see I'm not the only one who wants this. I'm thinking perhaps I wasn't clear enough in my first e-mail that I created a patch for the time being. You can download the patch, along with already modified source, static libraries and binaries for Linux i386, Linux

[sqlite] Stricter parsing rules

In accordance with SQL standards and SQLite developing guidelines, my entire team always uses double quotes around identifiers, and single quotes around string literals. Some relevant documentation is here: http://www.sqlite.org/lang_keywords.html "SQLite adds new keywords from time to time

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

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,

Re: [sqlite] sqlite3_column_name() contains quotes for views

In the example, I just realized something that makes matters worse. sqlite> SELECT "id" AS "id", "name" AS "name" FROM "names"; id|name 1|Linus 2|Bill 3|Steve 4|Richard 5|Ninjas Despite using a view, using AS seems to remove the quotes.

[sqlite] sqlite3_column_name() contains quotes for views

I believe I ran into a bug with SQLite, and would like to ensure the problem is not on my end. I created a simple table along with a view of it: SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .dump PRAGMA

Re: [sqlite] getting list of column names

> > 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

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

Re: [sqlite] multidimensional representation

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

Re: [sqlite] multidimensional representation

19901991 year <= dimension north sales108 cogs (5) (4) southsales 6 5 cogs (2) (1) regionline item <==

Re: [sqlite] multidimensional representation

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

Re: [sqlite] multidimensional representation

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

Re: [sqlite] multidimensional representation

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] multidimensional representation

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

[sqlite] multidimensional representation

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

[sqlite] multidimensional representation

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

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

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

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..

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

> 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

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

ot;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 >

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

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 > &g

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

gt; 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

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

> > 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&g

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

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

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

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

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

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

  1   2   >