Re: [sqlite] calculating in the command line interface

"Igor Tandetnik" wrote... On 10/1/2014 10:34 AM, Stephan Beal wrote: You're doing integer math. You need floating point: select round(1/2,10) as t; You probably meant round(1.0/2, 10), or round(1/2.0, 10) or similar. Thanks, Igor. ___

Re: [sqlite] UTF support

"J Decker" wrote... I saw a few things go by about unicode... and understand that it should just work to store the data as characters... I'm getting a unrecognized token... and think this page isn't right... I was playing with greek translation of 'mary had a little lamb'

Re: [sqlite] UTF support

"J Decker" wrote... So, I guess it is technically not allowed to encode 11 bit unicode characters as 16. the greek characters are 0x3XX which is 10 bits... I checked what WideCharToMultiByte was doing and found it was using 11 bit encodings... fixed my encoder to use an appropriate size for

[sqlite] Will someone be able to explain this weird outcome...

Greetings! select 7,915 - 5,021; displays this result: 7|910|21 I was really looking to have 2,894 returned, but instead I received the above. Then, I added quotes, sqlite> select "7,915" - "5,021"; 2 sqlite> select '7,915' - '5,021'; 2 I was able to figure out that comma's are more

Re: [sqlite] Will someone be able to explain this weird outcome...

"Roger Binns" wrote... -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/2014 01:18 PM, jose isaias cabrera wrote: I was able to figure out that comma's are more important than just a 1000 number delemeter, so I received the right answer by taking the commas out: To

Re: [sqlite] Unsubscription

"Venkatarangan MJ" wrote... How do I unsubscribe from the group as I have stopped using SQLITE from quite some time now? Thanks. Regards Rangan. Look at the bottom of this email and click on the last link. I think you can figure it out the rest. jic

[sqlite] Getting an unexpected result

Greetings! I am trying to create a report with this query, select id, ProjID, PClass, PSubClass, bdate, lang, wDir From LSOpenJobs Where cust = 'PIPA' AND fromLang = 'EN-CA' AND (lang = 'DE-DE' OR lang = 'PT-BR') AND PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND (PClass =

Re: [sqlite] Getting an unexpected result

"jose isaias cabrera" wrote... Greetings! I am trying to create a report with this query, select id, ProjID, PClass, PSubClass, bdate, lang, wDir From LSOpenJobs Where cust = 'PIPA' AND fromLang = 'EN-CA' AND (lang = 'DE-DE' OR lang = 'PT-BR') AND PSubClass LIKE '%-Trans' OR PSub

Re: [sqlite] Getting an unexpected result

"Richard Hipp" wrote... On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf wrote: Although the parser does not care how you format your query, human readability is improved by using nice formatting. Indeed, the error would have likely been spotted much faster had the

[sqlite] Thoughts and expertise help

Greetings! I have a program that takes bilingual files and looks for the source strings and reports on the multiples target translations of that source string. For example: Hello, Hola Hello, Hola Hello, Hola! Hello, Dímelo! Hello, Y entoces! Hello, y que! Good Bye, Hasta luego Good Bye,

[sqlite] x64 vs x32 DLL

Greetings! I have an application that is written for x32 machines. However, we now have a few machines that are x64 and all is well when we are using the precompiled x32 DLLs provided by http://www.sqlite.org/download.html. Changing the x32 DLLs to x64 DLLs, will it show any difference in

Re: [sqlite] x64 vs x32 DLL

"Stephan Beal" wrote... On Tue, Nov 4, 2014 at 9:20 PM, RSmith wrote: miniscule. On the downside, a 64-bit system uses 64 bit values for all registers even where 32-bit values could have sufficed, and so all internal looping through bits or register shifts take

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

jus...@postgresql.org wrote... On 2014-11-06 03:12, nicolas riesch wrote: There is a bug in some Linux implementations (RedHat9 is the canonical example) that prevents fcntl() locks created by one thread from being modified in a different thread. As a data point, "Red Hat 9" is

[sqlite] Table names length and content

Greetings! When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://www.sqlite.org/lang_createtable.html but could not find any information regarding this. Would someone please bring light for

Re: [sqlite] Table names length and content

"Stephan Beal" wrote... On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera <jic...@cinops.xerox.com wrote: When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://w

Re: [sqlite] Table names length and content

"Richard Hipp" wrote... On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera <jic...@cinops.xerox.com wrote: "Stephan Beal" wrote... On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera < jic...@cinops.xerox.com wrote: When creating table names, are ther

Re: [sqlite] Table names length and content

"Richard Hipp" wrote... On Mon, Dec 8, 2014 at 1:36 PM, Roger Binns <rog...@rogerbinns.com> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 10:30 AM, jose isaias cabrera wrote: >>> Hmmm... what I am looking for it is not there. If the &q

Re: [sqlite] Whish List for 2015

My wish for the team is to enjoy the holidays, and may the Peace reign in your homes forever. Thanks for this wonderful tool and may this new 2015 year be one very prosperous! As we say in the Spanish community: Feliz Navidad y un prospero año nuevo! Gracias. josé

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-31 03:37 PM, jose isaias cabrera wrote: >> >> How much I owe you? :-) The holidays was the next question. ;P Thanks. >> This will help me understand CTE much deeper/better. Thanks. >> > > The help is a pleasure

[sqlite] Sorting by greatest count amount

Greetings. I have this query, SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY pmuk; this works but it is not sorted. I tried things like SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY count(*) || '-' || pmuk; and SELECT pmuk, count(*) FROM

[sqlite] Sorting by greatest count amount

"Reinhard Max" wrote... > > On Thu, 6 Aug 2015 at 17:41, jose isaias cabrera wrote: > >> SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY >> pmuk; > > For ordered results you need an ORDER BY clause, and if you want to order > by a cal

[sqlite] Sorting by greatest count amount

"Reinhard Max" wrote... > On Thu, 6 Aug 2015 at 17:51, Reinhard Max wrote: > >> On Thu, 6 Aug 2015 at 17:41, jose isaias cabrera wrote: >> >>> SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY >>> pmuk; >> >> For or

[sqlite] WITH () AS (SELECT ) help

Greetings, perhaps this is not even possible... But I am trying to do something like this, WITH EmailData (name,contact,dstamp) AS ( SELECT 'last, first', 'first.last at xerox.com', '2015-08-25 11:11:11' ) UPDATE LSOpenProjects SET XtraB = EmailData.dstamp, pmuk = EmailData.contact WHERE

[sqlite] datetime start of day result

Greetings! if I run this command on the sqlite3 command tool, select datetime('now','localtime'); I get, sqlite> select datetime('now','localtime'); 2015-07-29 00:48:07 which is correct. When I run this command, sqlite> select datetime('now','start of day','localtime'); 2015-07-28 20:00:00

[sqlite] datetime start of day result

"Rafal Ponikwia" wrote... > On 2015-07-29 07:03, jose isaias cabrera wrote: >> sqlite> select datetime('now','start of day','localtime'); >> 2015-07-28 20:00:00 > > I think, in your case in first resets hours for UTC time, and then > changes to y

[sqlite] datetime start of day result

"Hick Gunter" wrote... > You are converting the "start of day" (UTC) to "localtime". Your timezone > is obviously 4 hours behind UTC... > > asql> SELECT datetime('now'), > datetime('now','localtime'),datetime('now','start of > day'),datetime('now','localtime','start of

[sqlite] datetime result help

Greetings! I am trying to calculate a date using sqlite date and time functions. Perhaps, one of you GURUs can help this poor soul. I have been trying to figure it out, but I know I am lack the understanding. I read the documentation for it, https://www.sqlite.org/lang_datefunc.html but I

[sqlite] datetime result help

Doctor Hipp wrote... > On 7/29/15, jose isaias cabrera wrote: >> >> Greetings! >> >> I am trying to calculate a date using sqlite date and time functions. >> Perhaps, one of you GURUs can help this poor soul. I have been trying to >> figure it out, but

[sqlite] datetime result help

"R.Smith" wrote, > On 2015-07-29 06:34 PM, jose isaias cabrera wrote: >> Greetings! >> >> I am trying to calculate a date using sqlite date and time functions. >> Perhaps, one of you GURUs can help this poor soul. I have been trying to >> figure it

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-29 08:00 PM, jose isaias cabrera wrote: >> >> Doctor Hipp wrote... >> >>> On 7/29/15, jose isaias cabrera wrote: >>> >>> SELECT >>> CASE WHEN time('now','+5.5 hours','localtime

[sqlite] datetime result help

meToday ELSE > NewTimeTomorrow END > FROM DTVals; > > > (The difference between today 17:00 and tomorrow morning 08:30 is 15.5 > hours, hence...) Yes, this one works. Thanks. You guys are amazing. > On 2015-07-29 07:29 PM, R.Smith wrote: >> >> >> On 2015-07-29 06:34 PM,

[sqlite] datetime result help

"R.Smith" wrote... > Adapted to allow variable hours: > > Assuming you have in your code a variable set for the hours, let's assume > some PHP and call it $hrs: > > SELECT (CASE > WHEN datetime( 'now', '+".$hrs."' hours', 'localtime') <= (date( > 'now', 'localtime')||' 17:00:00') THEN

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-29 08:58 PM, jose isaias cabrera wrote: >> >> >> There will be entries longer than 1 day, so I will have to address than. >> Thanks for the help, but this is great, though. If I need more help, I >> will come

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-29 08:58 PM, jose isaias cabrera wrote: >> >> >> There will be entries longer than 1 day, so I will have to address than. >> Thanks for the help, but this is great, though. If I need more help, I >> will come

[sqlite] datetime result help

"R.Smith" wrote... > > On 2015-07-30 10:59 PM, jose isaias cabrera wrote: >> >> Ok, I have one more option for you, and since you think it's fun, I need >> to exclude Saturdays and Sundays. I have a hack in the programming side >> of things, but

Re: [sqlite] Database gets locked for other processes

"Martin Kropfinger" Am Tue, 29 Oct 2013 12:00:02 -0400 schrieb sqlite-users-requ...@sqlite.org: Date: Tue, 29 Oct 2013 14:51:08 +0100 From: Stephan Beal To: General Discussion of SQLite Database Subject: Re: [sqlite] Database gets locked for

[sqlite] PRAGMA cache_size when copying data from two DBs

Greetings! When copying data from two DBs, do the cache_size of PRAGMA needs to match both connection? What type of response, issues or problem would it cause when the two connection's PRAGMA cache_size are different? thanks, josé ___

Re: [sqlite] PRAGMA cache_size when copying data from two DBs

"Richard Hipp" asked... On Thu, Nov 7, 2013 at 10:13 AM, jose isaias cabrera <cabr...@wrc.xerox.com>wrote: Greetings! When copying data from two DBs, do the cache_size of PRAGMA needs to match both connection? What type of response, issues or problem would it ca

[sqlite] EXPLAIN QUERY PLAN

Greetings and salutations. I am trying to speed up our queries and normalize our DB and I am reading, http://www.sqlite.org/eqp.html But, I am missing a lot. Where do I read about the results and how to make changes to the DB to speed up/enhance the DB response? Thanks. josé

Re: [sqlite] EXPLAIN QUERY PLAN

"Jay A. Kreibich" wrote... On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the wall: On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am > reading, > > http://www.sqlite.o

Re: [sqlite] EXPLAIN QUERY PLAN

"Simon Slavin" wrote... On 15 Nov 2013, at 4:29pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: Is there any place that explain each of this entries? <http://www.sqlite.org/opcode.html> However, don't feel you should understand them. They're for geeks

Re: [sqlite] EXPLAIN QUERY PLAN

reitag, 15. November 2013 18:08 An: General Discussion of SQLite Database Betreff: Re: [sqlite] EXPLAIN QUERY PLAN On 15 Nov 2013, at 4:29pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: Is there any place that explain each of this entries? <http://www.sqlite.org/opcode.html> H

Re: [sqlite] sqlite does not order greek characters correctly

"Simon Slavin" wrote... On 8 Dec 2013, at 9:34pm, Nikos Platis wrote: I tried to order a table by a column containing greek strings and I found out that sqlite does not sort them correctly. It probably uses the order of greek characters in the Unicode table, while the

Re: [sqlite] sqlite does not order greek characters correctly

"Simon Slavin" wrote... On 10 Dec 2013, at 4:23pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: "Simon Slavin" wrote... If you're not already using it, please take a look at the International Components for Unicode: <http://www.sqlite.org/src/artifac

[sqlite] SELECTing from another SELECT

Greetings! I have these tables A and B: A id,projid,a,b 1,1,'a','h' 2,2,'b','i' 3,3,'c','j' 4,4,'d','k' 5,5,'e','l' ... ... B id,projid,ptask 101,1,'a' 102,2,'b' 103,3,'a' 104,4,'b' 105,5,'a' ... ... When I do this SELECT, SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask =

Re: [sqlite] SELECTing from another SELECT

Igor Tandetnik wrote... On 1/23/2014 2:11 PM, jose isaias cabrera wrote: SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a'); You want WHERE projid IN (SELECT ...) IN operator accepts a set on the right; = operator only accepts a scalar (which comes from the first row

Re: [sqlite] SELECTing from another SELECT

John McKown wrote... On Thu, Jan 23, 2014 at 1:11 PM, jose isaias cabrera <cabr...@wrc.xerox.com>wrote: Greetings! I have these tables A and B: A id,projid,a,b 1,1,'a','h' 2,2,'b','i' 3,3,'c','j' 4,4,'d','k' 5,5,'e','l' ... ... B id,projid,ptask 101,1,'a' 102,2,'b' 103,3,'a' 10

Re: [sqlite] SELECTing from another SELECT

St. B. wrote... SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' the join may optimize better than the in (select ...) Thanks.

Re: [sqlite] SELECTing from another SELECT

Igor Tandetnik wrote... On 1/23/2014 2:26 PM, St. B. wrote: SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' Careful - this

Re: [sqlite] SELECTing from another SELECT

"Igor Tandetnik" wrote on Friday, January 24, 2014 9:48 AM... On 1/24/2014 9:28 AM, jose isaias cabrera wrote: Igor Tandetnik wrote... On 1/23/2014 2:26 PM, St. B. wrote: SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill.

Re: [sqlite] SELECTing from another SELECT

St. B. wrote... SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' the join may optimize better than the in (select ...)

Re: [sqlite] SELECTing from another SELECT

Igor Tandetnik wrote... On 1/23/2014 2:26 PM, St. B. wrote: SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' Careful - this

Re: [sqlite] SELECTing from another SELECT

"Igor Tandetnik" wrote on Friday, January 24, 2014 9:48 AM... On 1/24/2014 9:28 AM, jose isaias cabrera wrote: Igor Tandetnik wrote... On 1/23/2014 2:26 PM, St. B. wrote: SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill.

[sqlite] Keeping Track of Records of IDs in one table. Possible?

Greetings! I have two tables: OpenProjects and OpenJobs. OpenJobs have jobs that belong to one unique project (ProjID). OpenProjects are projects that have one project fathers a bunch of jobs. The design below is found in a localDB on the user's PC and also on a SharedDB file in a server

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

Simon Slavin wrote... On 29 Jan 2014, at 10:42pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: The tables are created this way: CREATE TABLE OpenProjects (id integer primary key, ProjID integer, createDnT, unique(id)); CREATE TABLE OpenJobs (id integer primary key, ProjID i

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

James K. Lowden wrote... On Wed, 29 Jan 2014 23:37:43 + Simon Slavin wrote: By the way, once you have this working I would suggest (from my experience) that you change your database design a little. Instead of having a table containing just your open jobs, have a

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

Wow! thanks for this. I have to read it slowly to capture the understanding of some of the syntax. But this is great! thanks. Stephen Chrzanowski" wrote... Untested and only from the SQL compiler in my brain -- This compiler is known to have a few bugs -- It may also be too late to go

[sqlite] Error: cannot commit - no transaction is active

Greetings! Perhaps I have not right ask this, as I am a newbie to SQL (about 5 years), but, I would like to suggest that once a BEGIN is set, if there is a syntax error after a BEGIN, that BEGIN gets deactivated. For example: sqlite> begin; sqlite> select foo FROM; Error: near ";": syntax

[sqlite] Error: near "SELECT": syntax error

Greetings and salutations! I have two Databases: 1. LocalDB 2. SharedDB The SharedDB has the latest updates made from different machines and different users. I would like to update the localDB record based on a date field (UpdateDate) that the SharedDB has, which is the latest. I have

Re: [sqlite] Error: cannot commit - no transaction is active

Clemens Ladisch" wrote... jose isaias cabrera wrote: I would like to suggest that once a BEGIN is set, if there is a syntax error after a BEGIN, that BEGIN gets deactivated. This is not how the SQL standard says databases should work. sqlite> begin; sqlite> select foo FROM;

Re: [sqlite] Error: near "SELECT": syntax error

Clemens Ladisch wrote... To: <sqlite-users@sqlite.org> Sent: Friday, January 31, 2014 1:32 PM Subject: Re: [sqlite] Error: near "SELECT": syntax error jose isaias cabrera wrote: INSERT OR REPLACE INTO LSOpenProjects ( SELECT ... ) SQL does not allow parentheses he

Re: [sqlite] about "windows" downloadable versions

big stone" wrote... Hello, Wouldn't it be a good idea, for the "windows" download version, to propose also (or only) the "-o2 compiled" .dll and .exe versions ? (trading 300Ko of executable size for a 50% speed-up is a bargain, when you have a pc) I agree with this idea. If the

Re: [sqlite] BLOBs and NULLs

"Peter Aronson" wrote... If you want to use sqlite3_randomness to generate a Version 4 UUID according to RFC4122, the following code will can be used: unsigned char uuid_data[16]; /* We'll generate a version 4 UUID as per RFC4122. Start by generating 128 bits of randomness (we will use 122

Re: [sqlite] how to write this commands?

"Igor Tandetnik" wrote... On 5/16/2014 11:02 AM, Rob Richardson wrote: It took me a bit of looking, but I think I understand your query. One question remains: why did you use the max() function? It is, technically, not legal in SQL to use both an aggregate function ... [clip] matter

Re: [sqlite] SQLite version 3.8.5 beta

"Richard Hipp" wrote... Please download beta snapshots of SQLite 3.8.5 from http://www.sqlite.org/download.html and test them in your applications. We hope to release SQLite version 3.8.5 within the next few weeks. See http://www.sqlite.org/draft/releaselog/3_8_5.html for the current list

Re: [sqlite] SQLite version 3.8.5 beta

"Jan Nijtmans" wrote... 2014-05-27 5:57 GMT+02:00 jose isaias cabrera <cabr...@wrc.xerox.com>: "Richard Hipp" wrote... Please report any problems to this mailing list and/or directly to me. I am having problem using the pre-built DLL: http://www.sqlite.org

Re: [sqlite] SQLite version 3.8.5 beta

"Richard Hipp" wrote... On Tue, May 27, 2014 at 10:39 AM, jose isaias cabrera <cabr...@wrc.xerox.com wrote: "Jan Nijtmans" wrote... 2014-05-27 5:57 GMT+02:00 jose isaias cabrera <cabr...@wrc.xerox.com>: "Richard Hipp" wrote... Please r

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

"Richard Hipp" wrote... On Tue, May 27, 2014 at 10:50 AM, Richard Hipp <d...@sqlite.org> wrote: On Tue, May 27, 2014 at 10:39 AM, jose isaias cabrera < cabr...@wrc.xerox.com> wrote: H... I am running the original DLL created for 3.8.4.3 on the WinXP

Re: [sqlite] SQLite version 3.8.5 beta

"Jan Nijtmans" wrote... 2014-05-27 5:57 GMT+02:00 jose isaias cabrera <cabr...@wrc.xerox.com>: "Richard Hipp" wrote... Please report any problems to this mailing list and/or directly to me. I am having problem using the pre-built DLL: http://www.sqlite.org

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

"Richard Hipp" wrote... On Tue, May 27, 2014 at 10:50 AM, Richard Hipp <d...@sqlite.org> wrote: On Tue, May 27, 2014 at 10:39 AM, jose isaias cabrera < cabr...@wrc.xerox.com> wrote: H... I am running the original DLL created for 3.8.4.3 on the WinXP

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

"Richard Hipp" wrote... On Wed, May 28, 2014 at 12:56 PM, jose isaias cabrera <cabr...@wrc.xerox.com wrote: Just noticed something... It may be nothing, but the MinGW built DLL has a size of 645KB while the MSVC built one has a size of 962KB. Just under 33% b

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

"Richard Hipp" wrote... On Wed, May 28, 2014 at 1:20 PM, jose isaias cabrera <cabr...@wrc.xerox.com>wrote: 3. Is there a spot anywhere that has clear steps on creating the Sqlite3 DLL? http://www.sqlite.org/draft/howtocompile.html#dll The "draft" p

Re: [sqlite] sqlite4 Windows build

"Mickey" wrote... Hello, I've been tasked with integrating SQLite4 LSM for testing purposes into our project. I compiled the SQlite .a file under Ubuntu but I also need to create a Windows static library. Can someone please provide me some instruction on creating the Windows Static Library?

[sqlite] SELECTing WHERE calculation > 0

Greetings! I have this select, SELECT cust, ProjID, proj, A_No, bdate, CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END, sum(ProjFund), sum(ProjFund)-sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END), sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0

Re: [sqlite] SELECTing WHERE calculation > 0

case Xtra4 when 'y' then invoice else 0 end) > 0; On Thu, May 29, 2014 at 5:14 PM, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: Greetings! I have this select, SELECT cust, ProjID, proj, A_No, bdate, CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noem

Re: [sqlite] SELECTing WHERE calculation > 0

"Edward Lau" wrote... Hi Jose: In SQL, aggregate function cannot be used in the WHERE clause. Use the HAVING clause instead. Try this: SELECT cust, ProjID, proj, A_No, bdate, CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END, sum(ProjFund),

[sqlite] Help with SELECTing CASE problem

Greetings! I have this SELECT, SELECT cust, ProjID, proj, A_No, bdate, CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END, sum(ProjFund), sum(ProjFund)-sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END), sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0

Re: [sqlite] Help with SELECTing CASE problem

"Igor Tandetnik" wrote... On 5/30/2014 12:41 PM, jose isaias cabrera wrote: What should be returned is the value of vEmail of the first record that has Xtra4='y' What do you mean by "first record"? Records are processed in no particular order. Good point. I wa

Re: [sqlite] Help with SELECTing CASE problem

"Igor Tandetnik" wrote... On 5/30/2014 1:29 PM, jose isaias cabrera wrote: Good point. I was, wrongly, thinking that it was top to bottom with the id. So, the idea is that once Xtra4='y' has provided a value, that is what I want. So, I think that because I am always gettin

Re: [sqlite] Help with SELECTing CASE problem

"Igor Tandetnik" wrote... On 5/30/2014 1:29 PM, jose isaias cabrera wrote: "Igor Tandetnik" wrote... On 5/30/2014 12:41 PM, jose isaias cabrera wrote: What should be returned is the value of vEmail of the first record that has Xtra4='y' What do you mean by &qu

Re: [sqlite] Help with SELECTing CASE problem

"Luuk" wrote... On 30-5-2014 19:29, jose isaias cabrera wrote: "Igor Tandetnik" wrote... On 5/30/2014 12:41 PM, jose isaias cabrera wrote: What should be returned is the value of vEmail of the first record that has Xtra4='y' What do you mean by "first recor

Re: [sqlite] Help with SELECTing CASE problem

"Igor Tandetnik" wrote... On 5/30/2014 4:43 PM, jose isaias cabrera wrote: But I want, 1|d|270|190|80 2|f|298|248|50 3|i|140|115|25 or 1|d|270|190|80 2|g|298|248|50 3|i|140|115|25 Does it matter from which row (among those with Xtra4='y') vEmail comes? You were talking abo

Re: [sqlite] Understanding Sqlite

"dd" wrote... Dear Author/All, There are many things needs to understand from sqlite apart from RDBMS concepts. For example, pragmas, database connection in different scenarios, IPC, virtual tables..etc. Do sqlite team has any plan to start online university for training and issuing

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

to...@acm.org wrote... Hi all, ... So here’s my suggestion for what (I feel) is a significant improvement for the SHELL version of SQLite without being too much of a programming complication in my view. (Those who usually attack any new concept, please pause a moment and give it some

[sqlite] Updating one DB from another

Greetings and salutations. We are in a tough situation, where we are going to have two instances of our application running on two different server: Server1: The original server, will continue to handle projects that are already opened until they are all closed and will not be able to open

Re: [sqlite] Updating one DB from another

Simon Slavin wrote... On 19 Jun 2014, at 3:55pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: These servers will be in two different servers and in two different parts of the world, so network access will be very slow. What I am thinking in doing is to copy the data on S

Re: [sqlite] SQLite Database Browser v3.2 released

justin wrote... A quick FYI. SQLite Database Browser v3.2 has been released. This version has a OSX .dmg binary for download as well as the Windows .exe + Linux/BSD/etc compatible source. https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/sqlb-3.2.0 Hope that's useful for

[sqlite] Setting boundaries in a search

Greetings. Pardon the newbie question, but is there a way to set boundaries on a search? Imagine this scenario: startcodes id,code,date 1,a,2014-08-06 2,b,2014-08-06 3,z,2014-08-06 4,g,2014-08-06 5,g,2014-08-06 6,j,2014-08-06 7,p,2014-08-06 8,t,2014-08-06 9,e,2014-08-06 10,w,2014-08-06

Re: [sqlite] Setting boundaries in a search

"Igor Tandetnik" wrote... On 7/15/2014 10:21 PM, jose isaias cabrera wrote: SELECT * from startcodes where code = 'e'; but I want to search only from id >= 8 and <= 14. Just say so: SELECT * from startcodes where code = 'e' and id between 8 and 14; I know I can do a WH

Re: [sqlite] Setting boundaries in a search

"Simon Slavin" wrote... On 16 Jul 2014, at 3:21am, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: SELECT * from startcodes where code = 'e'; but I want to search only from id >= 8 and <= 14. Is there a way to set the boundary for that SELECT that will only sear

Re: [sqlite] Setting boundaries in a search

"Rob Willett" wrote... Hi, Can I add my very first piece of advice after listening and reading for the last 6-9 months :) I’ll second what Simon says, I use the very same technique for a table with 4M+ records in and its so fast I thought I had an error and looked for bugs in my code. I

Re: [sqlite] Setting boundaries in a search

"RSmith" wrote... On 2014/07/16 14:23, jose isaias cabrera wrote: "Simon Slavin" wrote... That way is not particularly slow. You just need to have a good index. A good index for that search would be CREATE INDEX sci ON startcodes (code,id) You will f

Re: [sqlite] Setting boundaries in a search

"Simon Slavin" wrote... On 16 Jul 2014, at 1:23pm, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: "Simon Slavin" wrote... CREATE INDEX sci ON startcodes (code,id) You will find that that SELECT will then be blisteringly fast even with millions of rows

[sqlite] Addressing columns in an attached db

Greetings. I have two DB identical and I am copying data from one to another. When I attach a DB, i.e.. ATTACH 'c:\db\mydb.sqlite' as client; how do I address the names of the column? for example, both DBs connected and disconnected have the columns: id,ProjID, Date, code, login. When I

Re: [sqlite] Addressing columns in an attached db

Richard Hipp wrote... On Wed, Jul 23, 2014 at 12:32 PM, jose isaias cabrera <cabr...@wrc.xerox.com wrote: Greetings. I have two DB identical and I am copying data from one to another. When I attach a DB, i.e.. ATTACH 'c:\db\mydb.sqlite' as client; how do I address the na

Re: [sqlite] Addressing columns in an attached db

"Keith Medcalf" wrote... BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects as A WHERE A.ProjID <= 133560 AND Date != A.Date AND A.login = 'user1'; END; I presume that LSOpenProjects in both databases has a unique constraint on

Re: [sqlite] Addressing columns in an attached db

"James K. Lowden" wrote... On Wed, 23 Jul 2014 20:00:52 -0600 "Keith Medcalf" wrote: BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects as A WHERE A.ProjID <= 133560 AND Date != A.Date AND A.login =

[sqlite] Slow response using WHERE - IN

Greetings. I have a slow response problem... This command, used in conjunction with an another much bigger attached DB (client), BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects as A WHERE id IN ( SELECT id from LSOpenProjects

Re: [sqlite] Slow response using WHERE - IN

Clemens Ladisch wrote... On 07/26/2014 06:22 AM, jose isaias cabrera wrote: INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects WHERE id = 1 AND Date != A.Date; What is A? Sorry, this should have been written like this. INSERT OR REPLACE

Re: [sqlite] ISO time leap second.

"Igor Tandetnik" wote... On 7/28/2014 11:49 AM, Jan Nijtmans wrote: 2014-07-28 17:10 GMT+02:00 Igor Tandetnik : All your fix does is have the parser accept "60" as valid seconds field. That's not very interesting. julianday('2012-06-30T23:59:60'), and how should it

  1   2   3   4   5   6   7   >