Re: [sqlite] How to group this?

Solved this now, nil to do with SQL, but just running a different search (other value code and then you can ask for a secondary value and no need anymore to find the matching pair). RBS On Mon, Feb 10, 2020 at 8:22 AM Bart Smissaert wrote: > I fully agree with you, but I sofar I h

Re: [sqlite] How to group this?

to do with blood pressure values. RBS On Mon, Feb 10, 2020 at 3:12 AM Richard Damon wrote: > On 2/9/20 7:24 PM, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127

[sqlite] How to group this?

ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013 Diastolic 81 701568 1308 27/Jun/2013 Systolic 132 701562 1308 27/Jun/2013 Systolic 141 701563 1308 27/Jun/2013 Systolic 143 701564 1308

Re: [sqlite] last occurrence of /*

I think it can be done. Just dealing with the forward slash. RBS On Fri, Dec 6, 2019 at 11:49 PM Simon Slavin wrote: > On 6 Dec 2019, at 11:00pm, Bart Smissaert > wrote: > > > How do I select the part of this statement starting with the last /* ? > > Not in SQLite

Re: [sqlite] last occurrence of /*

I can do it in code. This is B4A on Android phone. For that reason can't do UDF's or extensions. This postcode thing was just a simple example, not to do with comment issue. RBS On Fri, Dec 6, 2019 at 11:48 PM Scott Robison wrote: > On Fri, Dec 6, 2019, 4:31 PM Bart Smissaert > wrote: &

Re: [sqlite] last occurrence of /*

Robison wrote: > On Fri, Dec 6, 2019, 4:00 PM Bart Smissaert > wrote: > > > Have table with SQL statements and these statements may have comments, > > starting with /* > > How do I select the part of this statement starting with the last /* ? > > So if the statement

[sqlite] last occurrence of /*

Have table with SQL statements and these statements may have comments, starting with /* How do I select the part of this statement starting with the last /* ? So if the statement is: select field1 /*comment 1 */ from table1 /*comment 2*/ I would like to get: /*comment 2*/ RBS

Re: [sqlite] Why do these 2 updates give different results?

This looks to work fine and runs fast as well, combining the 2 queries I have now: UPDATE QR3PARAMS SET ED = CASE WHEN ID IN (SELECT ID FROM PROBLEMS WHERE READ_CODE GLOB 'Eu522*' OR READ_CODE GLOB 'E2273*' OR READ_CODE = '1777409015') OR ID IN (SELECT ID FROM CURRENT_MED WHERE TERM_TEXT GLOB

Re: [sqlite] Why do these 2 updates give different results?

faster) I would split it in 2. RBS On Wed, Nov 13, 2019 at 8:39 PM Keith Medcalf wrote: > > On Wednesday, 13 November, 2019 13:26, Bart Smissaert < > bart.smissa...@gmail.com> wrote: > > >Thanks, the second one does the job as I need 1 or 0 and no nulls. > >It saves

Re: [sqlite] Why do these 2 updates give different results?

Thanks, the second one does the job as I need 1 or 0 and no nulls. It saves me running 2 queries as before had: UPDATE QR3PARAMS SET ED = CASE WHEN ED = 1 THEN 1 ELSE (SELECT 1 FROM CURRENT_MED WHERE (TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB 'Tadalafil*' OR TERM_TEXT GLOB 'Vardenafil*')

Re: [sqlite] Why do these 2 updates give different results?

> The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users On > >Behalf Of Bart Smissaert > >Sent: Wednesday, 13 November, 2019 04:41 > >To: Gene

Re: [sqlite] Why do these 2 updates give different results?

My data is the same, except ED has affinity integer and term_text affinity text. RBS On Wed, 13 Nov 2019, 15:25 Jose Isaias Cabrera, wrote: > > Bart Smissaert, on Wednesday, November 13, 2019 06:41 AM, wrote... > > > > UPDATE QR3PARAMS SET ED = > > CASE WHEN ED = 1 THE

[sqlite] Why do these 2 updates give different results?

UPDATE QR3PARAMS SET ED = CASE WHEN ED = 1 THEN 1 ELSE (SELECT 1 FROM CURRENT_MED WHERE (QR3PARAMS.ED = 1 OR TERM_TEXT GLOB 'Sildenafil*' OR TERM_TEXT GLOB 'Tadalafil*' OR TERM_TEXT GLOB 'Vardenafil*') AND ID = QR3PARAMS.ID LIMIT 1) END UPDATE QR3PARAMS SET ED = (SELECT 1 FROM CURRENT_MED WHERE

Re: [sqlite] Different column items to fields (transpose)

Thanks Keith and Igor, that works both fine indeed. Should know this by know. RBS On Wed, Oct 23, 2019 at 6:27 PM Igor Tandetnik wrote: > On 10/23/2019 12:28 PM, Bart Smissaert wrote: > > Have a table created like this: > > > > create table

[sqlite] Different column items to fields (transpose)

Have a table created like this: create table num_values(id integer, entry_date integer, term_text text, numeric_value Real) For this problem I am only interested in the rows

Re: [sqlite] Can SQLite do this in a single query?

> Not a clue. I didn't write the schema. I know, I asked the OP. RBS On Mon, Oct 21, 2019 at 4:16 PM Keith Medcalf wrote: > > On Monday, 21 October, 2019 08:47, Bart Smissaert < > bart.smissa...@gmail.com> wrote: > > >Shouldn't there be field MODE_ID in the Employe

Re: [sqlite] Can SQLite do this in a single query?

Shouldn't there be field MODE_ID in the Employee table? RBS On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf wrote: > > On Monday, 21 October, 2019 08:31, Winfried wrote: > > >Using the following tables, I need to find how employees from each city > >come to work. > > >== Employees table: >

Re: [sqlite] Standard deviation last x entries

e) > > although the approximation is not very good. > > > > > > > > On Fri, Oct 18, 2019 at 6:41 PM Gabor Grothendieck > > wrote: > >> There is a stdev function for sqlite here: > >> https://www.sqlite.org/contrib//download/extension-function

Re: [sqlite] Standard deviation last x entries

019 at 6:41 PM Gabor Grothendieck > wrote: > > > > There is a stdev function for sqlite here: > > https://www.sqlite.org/contrib//download/extension-functions.c?get=25 > > > > On Wed, Oct 16, 2019 at 7:57 PM Olaf Schmidt > wrote: > > > > > > Am 12.10

Re: [sqlite] Standard deviation last x entries

Thanks for that and have tried this now (on Android app) and works fine. It is fast as well, although slightly slower than the previous version. I ran this on a column with 8000 values ranging from 0 to 1600 and this took about 140 milli-seconds on a fast Samsung S9 phone. Database is on a SD. I

Re: [sqlite] Standard deviation last x entries

> floating point numbers (the limit of computational precision in the last > binary place) > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-us

Re: [sqlite] Standard deviation last x entries

hmidt wrote: > > > > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > > Sorry, I forgot to tell that. It is date column with an integer number. > > > > > > ID xValue xDate > > > > > > 1 130 40123 > > > 1

Re: [sqlite] Standard deviation last x entries

ng point numbers (the limit of computational precision in the last > binary place) > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users On > >B

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Could you tell me what this is doing: ,(x>0)*(y+x/y)/2 yi Especially the yi after the 2 How does the yi relate to the preceding bit? RBS On Fri, Oct 18, 2019 at 8:26 PM Olaf Schmidt wrote: > Am 18.10.2019 um 19:45 schrieb Bart Smissaert: > > > Regarding: > >

Re: [sqlite] Standard deviation last x entries

1.NextRow cConn.SQL1.ExecNonQuery2(strSQL, Array As String(Sqrt(RS1.GetDouble2(1)), RS1.GetInt2(0))) Loop cConn.EndTransaction This is B4A code on Android with the SQLCipher driver. RBS On Fri, Oct 18, 2019 at 8:26 PM Olaf Schmidt wrote: > Am 18.10.2019 um 19:45 schrieb Bart Smissaert: > > &

Re: [sqlite] Standard deviation last x entries

her-tables-colum WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Created) AS Rnk FROM @Item AS i JOIN @ItemType AS it ON i.ItemTypeId = it.Id ) DELETE FROM cte WHERE Rnk > 1; RBS On Fri, Oct 18, 2019 at 8:26 PM Olaf Schmidt wrote: > Am 18.10.2019 um 19:45 schrieb Bart

Re: [sqlite] Standard deviation last x entries

? RBS On Thu, Oct 17, 2019 at 12:56 AM Olaf Schmidt wrote: > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > > > > ID xValue xDate > > > > 1 130 40123 > > 1

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Tested all on the Windows app and works perfectly fine indeed. Thanks for that. RBS On Thu, Oct 17, 2019 at 12:56 AM Olaf Schmidt wrote: > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > >

Re: [sqlite] Standard deviation last x entries

Ignore the mentioned problem, must have been an e-mail artefact and view creates fine. Will test all later. RBS On Thu, Oct 17, 2019 at 11:02 AM Bart Smissaert wrote: > Hi Olaf, > > Thanks, will try that out. > I have a slight problem with your first create view on Android

Re: [sqlite] Standard deviation last x entries

, Oct 17, 2019 at 12:56 AM Olaf Schmidt wrote: > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > > > > ID xValue xDate > > > > 1 130 40123 > > 1 120 4

Re: [sqlite] Standard deviation last x entries

ulation is. > > On 10/12/19 12:13 PM, Bart Smissaert wrote: > > Thanks, I do know how to calculate the SD in code, but I thought in this > > particular case it might be faster to do this in SQL. > > Only problem is the square root and for that reason I will test this in >

Re: [sqlite] Standard deviation last x entries

the standard deviation of some set of four of them is > entertaining, but not useful in any way. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users

Re: [sqlite] Standard deviation last x entries

Igor Tandetnik wrote: > On 10/12/2019 10:08 AM, Bart Smissaert wrote: > > How do I get the standard deviation of the last 4 entries (there could be > > less than 4) of an integer column grouped by an integer ID entry in > another > > column in the same table. > > What

[sqlite] Standard deviation last x entries

How do I get the standard deviation of the last 4 entries (there could be less than 4) of an integer column grouped by an integer ID entry in another column in the same table. So data could be like this: ID xValue 1 130 1 120 1 140 1 100 1 110 2 140 2 130 2 150

Re: [sqlite] What is wrong with this SQL?

ne final result each. Generally the analysis timestamp is not > very useful (but it is usually recorded anyway). > > That is, there is exactly one final result per analysis per sample per > samplepoint. > > >-Original Message- > >From: sqlite-users On > >Beha

Re: [sqlite] What is wrong with this SQL?

Thanks, interesting stuff! Will study this. Don't comprehend it fully, but basically query 3 is no good then? RBS On Sun, 22 Sep 2019, 20:36 Keith Medcalf, wrote: > > On Sunday, 22 September, 2019 12:36, Bart Smissaert < > bart.smissa...@gmail.com> wrote: > > >Have

Re: [sqlite] What is wrong with this SQL?

04:10, Bart Smissaert < > bart.smissa...@gmail.com> wrote: > > >Have this query: > > >SELECT round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) AS AGE, > > AVG(BM.numeric_value) AS avg_Hb_Male, > > AVG(BF.numeric_value) as avg_Hb_Female > &

Re: [sqlite] What is wrong with this SQL?

Thanks. What changed was probably the SQLCipher version for Android. Changed to the 64 bits version. It definitely worked fine before. Will try your first query. RBS On Sun, 22 Sep 2019, 18:37 Keith Medcalf, wrote: > > On Sunday, 22 September, 2019 04:10, Bart Smissaert < >

Re: [sqlite] What is wrong with this SQL?

Ok, what should the query be? RBS On Sun, 22 Sep 2019, 15:42 Clemens Ladisch, wrote: > Bart Smissaert wrote: > > I did try left joins, but no data returned. > > All filters for outer-joined rows must be specified in the JOIN itself; > in the WHERE clause, NULL values would m

Re: [sqlite] What is wrong with this SQL?

Thanks, I did try left joins, but no data returned. RBS On Sun, Sep 22, 2019 at 12:22 PM Richard Damon wrote: > On 9/22/19 6:10 AM, Bart Smissaert wrote: > > Have this query: > > > > SELECT round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) AS > AGE, > &g

[sqlite] What is wrong with this SQL?

Have this query: SELECT round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) AS AGE, AVG(BM.numeric_value) AS avg_Hb_Male, AVG(BF.numeric_value) as avg_Hb_Female FROM PATIENTS P INNER JOIN NUM_VALUES BM ON(P.ID = BM.ID AND P.SEX = 'Male') INNER join NUM_VALUES

Re: [sqlite] Instr(x, y, z) ?

tty kitty kitty 40 > > > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, May 14, 2019 3:02 PM > To: General Discussion of SQLite Database > Subj

[sqlite] Instr(x, y, z) ?

Is it possible with the existing SQL core string functions to find the position of occurrence z of string y in string x? The standard Instr function only does this for the first occurrence, but I would like to specify the second, third, fourth etc. occurrence of the specified string. As this is

Re: [sqlite] CTE to Get Path In a Tree

_folder_id, path > >from folderpath > > where id == 1 > >order by rank; > > > >--- > >The fact that there's a Highway to Hell but only a Stairway to Heaven > >says a lot about anticipated traffic volume. > > > >>-Original Message--

Re: [sqlite] CTE to Get Path In a Tree

Have the same table structure to represent a folder tree: CREATE TABLE FOLDERS([ID] INTEGER PRIMARY KEY, [PARENT_ID] INTEGER, [NAME] TEXT, [LEVEL] INTEGER, [RANK] TEXT) Data is as follows: ID PARENT_ID NAME LEVEL RANK

Re: [sqlite] SQL question, move union to second column

Ah, yes, thanks. RBS On Thu, Mar 14, 2019 at 12:55 AM Igor Tandetnik wrote: > On 3/13/2019 8:32 PM, Bart Smissaert wrote: > > Sorry, ignore that, can see now that all is a reserved word. > > You can enclose it in double quotes, as in "All", if you really want

Re: [sqlite] SQL question, move union to second column

Sorry, ignore that, can see now that all is a reserved word. RBS On Thu, Mar 14, 2019 at 12:30 AM Bart Smissaert wrote: > Thanks; this works fine: > > select PLACE, > sum(ID not in (select ID from ATTENDED)), > count(ID) > from PERSONS group by PLACE > > But if

Re: [sqlite] SQL question, move union to second column

ded, count(*) All from PERSONS group by PLACE Otherwise very neat indeed though! RBS On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik wrote: > On 3/13/2019 8:08 PM, Bart Smissaert wrote: > > But I would like the result to be in 3 columns, so result in this case > > would be: >

[sqlite] SQL question, move union to second column

Have 2 tables: PERSONS: CREATE TABLE [PERSONS]([ID] INTEGER PRIMARY KEY, [Place] TEXT) ATTENDED: CREATE TABLE [ATTENDED]([ID] INTEGER) Sample date like this: PERSONS: ID Place --- 1 A 2 A 3 B 4 A 5 A 6 A 7 B 8 B 9 A 10 A ATTENDED: ID - 1 5 6 1 1 8 9 5 1 5 8 1 6 8 9 9 1 5 6 1

Re: [sqlite] Displaying hierarchical structure

Ignore this. Was mailed over 1w ago and only came through and I have figured this out after studying the CTE documentation on the SQLite site. RBS On Wed, Feb 6, 2019 at 7:24 PM Bart Smissaert wrote: > > I can select the rank as in the previous e-mail with this recursive

Re: [sqlite] Displaying hierarchical structure

1 directly from the ID. Any idea how to manage these two? RBS On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert wrote: > Looking at this approach of a hierarchical system: > https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql > > Given a table like

Re: [sqlite] Displaying hierarchical structure

What SQL should I use to update the field RANK if the first row is known to be 01, but all the next rows are null? I tried with a non-recursive query, but couldn't work it out. RBS On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert wrote: > This looks a nice and simple way to disp

Re: [sqlite] Displaying hierarchical structure

Thanks, will try that. > order by PATH So, where is this path coming from? RBS On Thu, Jan 31, 2019 at 4:08 PM Jean-Luc Hainaut wrote: > Recursive CTEs are the most obvious technique to solve this kind of > problems. > However, a less known technique can do the job: recursive triggers. > Here

Re: [sqlite] Displaying hierarchical structure

qlite-using-python-and-the-transitive-closure-extension/ > > On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert wrote: > > > Working on an Android app and part of that is storing SQL in a virtual > > folder system in SQLite. For this I want to use a so-called closure table > > as explain

Re: [sqlite] Displaying hierarchical structure

This looks a nice and simple way to display the tree in the right order without recursive SQL: https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql Will do some testing on large numbers to see how the 2 methods compare speed-wise. RBS On Tue, Jan 29, 2019 at 8:33 PM Keith

Re: [sqlite] Displaying hierarchical structure

Yes, thanks, -- breadth first does the job nicely indeed. Not sure the closure table is needed. There are some complex tasks though, that need thinking of, eg copying one folder into an other folder. I am not familiar with recursive queries and it looks complex to me. There might arise a problem

Re: [sqlite] Displaying hierarchical structure

ORDER BY > id|parent_id|name > 1||Folder1 > 2|1|Folder1\Folder2 > 5|2|Folder1\Folder2\Folder5 > 6|2|Folder1\Folder2\Folder6 > 3|1|Folder1\Folder3 > 4|1|Folder1\Folder4 > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sql

Re: [sqlite] Displaying hierarchical structure

lder2\Folder5 > 6|2|Folder1\Folder2\Folder6 > 3|1|Folder1\Folder3 > 4|1|Folder1\Folder4 > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 10:52

[sqlite] Displaying hierarchical structure

Working on an Android app and part of that is storing SQL in a virtual folder system in SQLite. For this I want to use a so-called closure table as explained nicely here: http://technobytz.com/closure_table_store_hierarchical_data.html I have a table holder the folder details: ID PARENT_ID

Re: [sqlite] Group by counting 2 different sub-groups

Ah, yes, of course. Thanks. RBS On Sun, Nov 25, 2018 at 12:24 PM R Smith wrote: > > On 2018/11/25 1:50 PM, Bart Smissaert wrote: > > Is it possible to use the aliases diab_count and drug_count directly in a > > fourth column to show the percentage? > > No.

Re: [sqlite] Group by counting 2 different sub-groups

/24/2018 8:51 PM, Bart Smissaert wrote: > > Ok, in the first count column I would like the grouped counts for > patients > > in the views on_non_insulin or on_insulin and > > in the second count column I would like the grouped counts for patients > the > > view diab

Re: [sqlite] Group by counting 2 different sub-groups

from object)) and there are lots of these not well known solutions that don't show in my SQL text books. Would you know any books that show all these options? RBS On Sun, Nov 25, 2018 at 2:47 AM Igor Tandetnik wrote: > On 11/24/2018 8:51 PM, Bart Smissaert wrote: > > Ok, in the fi

Re: [sqlite] Group by counting 2 different sub-groups

So, in other words in the second count column I would like the result of this: select p.gp_name as GP, count(d.emis_number) as pat_count from patients p inner join diabetics d on(p.emis_number = d.emis_number) group by GP order by pat_count asc RBS On Sun, 25 Nov 2018, 01:51 Bart Smissaert Ok

Re: [sqlite] Group by counting 2 different sub-groups

and on_insulin are smaller sub_groups. RBS On Sun, Nov 25, 2018 at 1:41 AM Igor Tandetnik wrote: > On 11/24/2018 7:59 PM, Bart Smissaert wrote: > > Thanks, was aware, but the SQL was indeed wrong as posted and should have > > brackets around the 2 or conditions. > > In this case, as

Re: [sqlite] Group by counting 2 different sub-groups

OK, will describe the data as done before. RBS On Sun, Nov 25, 2018 at 1:08 AM Simon Slavin wrote: > On 25 Nov 2018, at 12:59am, Bart Smissaert > wrote: > > > Could I post a little demo SQLite file? Not sure now if this is allowed > as an attachment. > > This mailing

Re: [sqlite] Group by counting 2 different sub-groups

wrote: > On 11/24/2018 7:44 PM, Bart Smissaert wrote: > > The very much simplified example works fine, but my real SQL is a bit > more > > complex: > > > > select gp_name, count(*) as pat_count, > > sum(emis_number in(select emis_number from diabetics)) as di

Re: [sqlite] Group by counting 2 different sub-groups

:59 PM, Bart Smissaert wrote: > > Have a table called Table1 like this: > > > > id Type > > -- > > 1 a > > 2 b > > 3 a > > 4 c > > 5 a > > 6 b > > 7 c > > 8 c > > 9 b > > 10 a > > > > Table create

Re: [sqlite] Group by counting 2 different sub-groups

Thanks for that! Very nice and simple. (note there is a superfluous comma after id_count_view1) RBS On Sun, Nov 25, 2018 at 12:17 AM Igor Tandetnik wrote: > On 11/24/2018 6:59 PM, Bart Smissaert wrote: > > Have a table called Table1 like this: > > > > id Type > >

[sqlite] Group by counting 2 different sub-groups

Have a table called Table1 like this: id Type -- 1 a 2 b 3 a 4 c 5 a 6 b 7 c 8 c 9 b 10 a Table create is this: CREATE TABLE [Table1]([id] INTEGER PRIMARY KEY, [Type] TEXT) Then there are 2 views, created like this CREATE VIEW View1 as select id from Table1 where id < 8 CREATE VIEW

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

versions, so maybe can't rely on that. RBS On Wed, Aug 1, 2018 at 8:27 PM, R Smith wrote: > On 2018/08/01 5:56 PM, Bart Smissaert wrote: > >> May I ask about your use-case and what specifically is needed? >>> >> Probably exactly the same as you are us

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

do it? > > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 11:56 AM > To: SQLite mailing list > Subject: Re: [sqlite] [EXTERNAL] Re: Is pragma index_list

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

be handled. RBS On Wed, Aug 1, 2018 at 4:39 PM, R Smith wrote: > On 2018/08/01 4:50 PM, Bart Smissaert wrote: > >> I think you might be right there, but for my practical purpose I need the >> result to be invalid. >> I just wonder if a Halt at row 2 and no further r

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Halt 0 0 000 > > sqlite> p

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

y ignored. This means if there is a typo in a pragma > statement the library does not inform the user of the fact." > > That way if there's a typo, or if you try a new pragma in an old version > then it won't complain, it just won't do anything. > > -Original Message- > F

Re: [sqlite] Is pragma index_list without supplied table name valid SQL?

s-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 10:13 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Is pragma index_list without supplied table name valid > SQL? > > Using SQLite 3.22.0 > > In my ap

[sqlite] Is pragma index_list without supplied table name valid SQL?

Using SQLite 3.22.0 In my app I have code to determine if a given SQL string is data-producing, non-data producing or invalid. It uses these 3 SQLite functions: sqlite3_prepare_v3 sqlite3_stmt_readonly sqlite3_column_count Have been using this code for a few years and sofar never failed, but

Re: [sqlite] How to insert this string?

Thanks for that. A QuotedString function will be useful indeed and will add that. In this case my language is B4A, which is similar to the old VB6. Very good and much recommended. RBS On Fri, Jul 27, 2018 at 11:32 AM, R Smith wrote: > On 2018/07/27 11:15 AM, Bart Smissaert wrote: >

Re: [sqlite] [EXTERNAL] How to insert this string?

> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Bart Smissaert > Gesendet: Freitag, 27. Juli 2018 10:38 > An: General Discussion of SQLite Database sqlite.org> > Betreff: [EXTERNAL] [sqlite] How to insert this string? > > Have

Re: [sqlite] How to insert this string?

') The problem is how to get the string: 'A', 'B', 'C' in the table. RBS On Fri, Jul 27, 2018 at 10:02 AM, R Smith wrote: > On 2018/07/27 10:38 AM, Bart Smissaert wrote: > >> Have a table like this: >> >> create table sql_replace(string_old text, string_new text) >>

[sqlite] How to insert this string?

Have a table like this: create table sql_replace(string_old text, string_new text) The idea is to do automatic string replacements in SQL. Say we have this SQL: select * from table1 where a in(xxx) then after the string replacement it should be this: select * from table1 where a in('A', 'B',

Re: [sqlite] Getting statement column data types on Android

Yes, I think the only solution might be to clean up the tables So if for example there is text in a real column, update it to 0.0 etc. RBS On Tue, May 8, 2018 at 5:46 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 8 May 2018, at 5:37pm, Bart Smissaert <bart.smissa...@gmai

Re: [sqlite] Getting statement column data types on Android

018 at 5:29 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 8 May 2018, at 4:19pm, Bart Smissaert <bart.smissa...@gmail.com> wrote: > > > Just tested that (TypeOf) on the Android phone and it doesn't do what I > > wanted. > > I tested on a column declared

Re: [sqlite] Getting statement column data types on Android

e3_stmt*, int iCol); > > ? > > On Mon, May 7, 2018 at 4:28 PM, Bart Smissaert <bart.smissa...@gmail.com> > wrote: > > > Yes, thanks, that might be the best way, but it can get a bit complicated > > with complex SQL. > > > > RBS > > > > > >

Re: [sqlite] Getting statement column data types on Android

at 12:05 AM, Simon Slavin <slav...@bigfraud.org> wrote: > On 7 May 2018, at 10:49pm, Bart Smissaert <bart.smissa...@gmail.com> > wrote: > > > Using B4A for a SQLite database app on an Android phone. > > B4A doesn't have functions like sqlite3_column_dec

Re: [sqlite] Getting statement column data types on Android

Yes, thanks, that might be the best way, but it can get a bit complicated with complex SQL. RBS On Tue, May 8, 2018 at 12:05 AM, Simon Slavin <slav...@bigfraud.org> wrote: > On 7 May 2018, at 10:49pm, Bart Smissaert <bart.smissa...@gmail.com> > wrote: > > > Using B4

[sqlite] Getting statement column data types on Android

Using B4A for a SQLite database app on an Android phone. B4A doesn't have functions like sqlite3_column_decltype and sqlite3_column_type and this is causing some difficulty getting the column datatypes of a row producing statement. If we have for example: create table Table1(ID Integer, Name

[sqlite] Android SQLite DB app with forms etc.

Looking for an android DB phone app (free) based on SQLite with configurable forms and buttons to setup a DB app. Is there such a thing? Not found it yet. I could develop such an app with something like B4A: https://www.b4x.com/b4a.html but that will take quite a bit of time. Thanks for any

Re: [sqlite] [EXTERNAL] Re: Strange concatenation result

If anything I would prefer it to return an empty string as returning Null could mess up the detection of the column data type. RBS On Tue, Feb 27, 2018 at 7:46 AM, Simon Slavin wrote: > > > On 27 Feb 2018, at 7:01am, Hick Gunter wrote: > > > What

Re: [sqlite] [EXTERNAL] sqlite3_set_last_insert_rowid

che Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Bart Smissaert > Gesendet: Dienstag, 30. Jänner 2018 16:04 > An: General Discussion of SQLite Database <sqlite-users@mailinglists. > sqlite.org> > Betreff:

[sqlite] sqlite3_set_last_insert_rowid

Just wonder what the possible use for this is. RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL frequency of names in 2 tables

oup by name > ) as table1counts > inner join > (select name, count(*) as Table2_Count > from table2 > group by name > ) as table2counts > using (name) > order by names;--optional > > > > -Original Message- > From: sqlite-users [mailto:sqli

[sqlite] SQL frequency of names in 2 tables

Say we have 2 tables, each with a text column, holding non-unique names. All names in table 1 are also in table 2 and vice-versa. The frequency of the names are different for both tables and this is the information I need to get. So output should be like this: Names Table1_Count Table2_Count

Re: [sqlite] Speed issue of SELECT in my application

No worries, I had figured you meant this applied to multiple read statements. RBS On Thu, Jan 18, 2018 at 9:24 AM, R Smith <ryansmit...@gmail.com> wrote: > > On 2018/01/17 4:26 PM, Bart Smissaert wrote: > >> 3. Start a transaction and hold the DB read locks for the du

Re: [sqlite] Speed issue of SELECT in my application

> 3. Start a transaction and hold the DB read locks for the duration of your application (again, if it won't need writing) I had a look at this but couldn't see a speed increase. This was for a single statement, so that is repeated (in a loop) sqlite3_step, sqlite3_column_xxx etc. In what

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

> I try very hard not to reinvent the wheel You don't have to, just need a different wheel. I did this recently both for .csv and also for .html and working very nicely and far more flexible than using the code in shell.c. RBS On Wed, Jan 17, 2018 at 10:54 AM, Shane Dev

Re: [sqlite] sqlite3_column_name with alias

> As you can see Should read: As you said RBS On Mon, Jan 8, 2018 at 11:17 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote: > > Best is to define type on output > > Yes, looks that way. As you can see the alias can help, eg: > select max(integer_date_column) as inte

Re: [sqlite] sqlite3_column_name with alias

ype: select max(integer_date) as int_date All this is only needed if sqlite3_column_decltype produces null. Looks kind of sorted now, thanks. RBS On Mon, Jan 8, 2018 at 10:29 AM, R Smith <ryansmit...@gmail.com> wrote: > > On 2018/01/08 12:00 AM, Bart Smissaert wrote: > >> OK, I

Re: [sqlite] sqlite3_column_name with alias

system. One option is that the user just tells (along with the statement) what the output formatting should be, but that would be cumbersome. RBS On Sun, Jan 7, 2018 at 9:10 PM, R Smith <ryansmit...@gmail.com> wrote: > > On 2018/01/07 10:11 PM, Bart Smissaert wrote: > >> Sure, i

Re: [sqlite] sqlite3_column_name with alias

<slav...@bigfraud.org> wrote: > > > On 7 Jan 2018, at 6:16pm, Bart Smissaert <bart.smissa...@gmail.com> wrote: > > > Would it be possible somehow to get the column full name if there is a > > column alias? > > > > For example if we have: > >

[sqlite] sqlite3_column_name with alias

Would it be possible somehow to get the column full name if there is a column alias? For example if we have: select max(ID) as MaxID from table1 then I need to get max(ID) from the statement pointer. One would think that if sqlite3_column_name can get the alias name then somehow it must be

  1   2   3   4   5   6   >