[sqlite] v2 and v3 differences - Help with some SQL
Hi, This SQL in a sqlite v2 database gives me 306 rows... This is what I would expect. SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t left outer join Category c on c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY date_trans I upgraded the db to a v3 db but I had some problems. In SQL v3 the same SQL on the same upgraded database now gives me 8743 rows (ie every single row in the Transn table) Does anybody know how I re-write the SQL to give me 306 items again. I thought this would have worked but it gave me zero rows. It appears that you can't join a table if one value is a NULL. SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t,Category c where c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID is null ORDER BY date_trans Even this didn't work: SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked, coalesce(t.categoryID, -1) as 't.categoryID' FROM Transn t, Category c where c.CategoryID=t.categoryID and AcctID in (3, 12, 11, 6, 28, 15) and t.categoryID = -1 ORDER BY date_trans Here are the two tables: CREATE TABLE Category (CatParent Integer, Description varchar, CategoryID integer NOT NULL DEFAULT '0', CategoryAbsolutePath varchar, PRIMARY KEY(CategoryID)) CREATE TABLE Transn (GST double DEFAULT '0', AcctID integer, TransID integer NOT NULL, CategoryID integer, Total double DEFAULT '0', Description varchar, Date_Trans date, Notes varchar, NeedsAttention boolean DEFAULT 'False', Reconciled boolean DEFAULT 'False', GSTClaimed boolean DEFAULT 'False', HasSplitTrans boolean DEFAULT 'False', Currency varchar(10) DEFAULT 'AUD', CurrConverter float DEFAULT '1.00', ForeignCurrencyAmount double DEFAULT '0', locked boolean DEFAULT 'false', PRIMARY KEY(TransID)) -- Cheers, Dr Gerard Hammond We are on the cutting edge of catching up. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transaction Check
Cool. Is there a way to ask the engine this? Some SELECT call? At 12:50 PM + 28/7/07, [EMAIL PROTECTED] wrote: RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: Hi, Is there any api which tell us if a transaction is already started or not? sqlite3_get_autocommit() -- Cheers, Dr Gerard Hammond MacSOS Solutions Pty Ltd [EMAIL PROTECTED] http://www.macsos.com.au Proofread carefully to see if you any words out. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
The first few words sound incorrect to me. Shouldn't it be. "SQLite is an in-process" and even then I don't know what 'in-process' actually means. On 15/11/2007, at 2:15 PM, John wrote: [EMAIL PROTECTED] wrote: The new look for the SQLite website is now in place, if you haven't already noticed: http://www.sqlite.org/ YUCK! What happened? Cheers. -- Dr Gerard Hammond Garvan Institute of Medical Research - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
I have reported it as a bug - ticket is http://www.sqlite.org/cvstrac/tktview?tn=2822 It appears as though the /src/select.c (Line1499) changed from: if( iCol<0 && mustComplete ){ to: }else if( mustComplete ){ in version 1.336 of this file - http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/select.c&v1=1.335&v2=1.336 And this change results in this bug. On 04/12/2007, at 4:59 AM, Joe Wilson wrote: --- Marco Bambini <[EMAIL PROTECTED]> wrote: Starting from version 3.4.2 I receive errors with queries like: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field or even SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field error is: ORDER BY term number 1 does not match any result column Tables are created by: CREATE TABLE a (field); CREATE TABLE b (field); Please note that the above queries worked fine with sqlite 3.2.x or 3.3.x. Any idea? You probably know the workarounds: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1; or SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER BY x; but it's odd that this one doesn't work as well: create table t1(a); create table t2(b); select t1.a from t1 union all select t2.b from t2 order by a; SQL error: ORDER BY term number 1 does not match any result column Cheers. -- Dr Gerard Hammond Garvan Institute of Medical Research - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?
On 14/12/2007, at 3:40 AM, [EMAIL PROTECTED] wrote: Ticket number #2822 http://www.sqlite.org/cvstrac/tktview?tn=2822 (2) If an ORDER BY term is a simple identifer (like "x", not "x.y" and not "x.y.z") and if there if the k-th column uses that same identifer as an AS alias, the sort by the k-th column. CREATE TABLE a(x,y); INSERT INTO a VALUES(1,8); INSERT INTO a VALUES(9,2); SELECT x AS y FROM a ORDER BY y; In older versions of SQLite, the SELECT statement above would return 9, 1 since the ORDER BY term evaluated to the expression a.y by rule (3) In the next release, because of the addition of rule (2) above, the result will be 1, 9. I don't understand. If I say "ORDER BY y" aren't I saying sort the result set based on the column " as y" of the result set, not the table "a.y"? ie they should come out y 9 1 That's what (2) says to me Cheers. -- Dr Gerard Hammond http://www.macsos.com.au Cheers. -- Dr Gerard Hammond http://www.macsos.com.au Cheers. -- Dr Gerard Hammond Garvan Institute of Medical Research - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] want to purchase
For a SQLite Server product, try http://www.realsoftware.com/products/realsql/index.php On 15/04/2008, at 2:20 AM, Arvind M wrote: > dear sir > > i want to purchase of sqlite server kinly give me guidance > > arvind > systime > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Cheers. -- Dr Gerard Hammond http://www.macsos.com.au The Macintosh may only have 10% of the market, but it is clearly the top 10%. Cheers. -- Dr Gerard Hammond http://www.macsos.com.au The Macintosh may only have 10% of the market, but it is clearly the top 10%. Cheers. -- Dr Gerard Hammond Garvan Institute of Medical Research ...No tequila to be had. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Stop multiple simultaneous users
Hi, How can I stop multiple simultaneous users accessing a SQLite database? Is there a SQLite function that tells me how many people/applications have the database open? I am using REALbasic on a OSX, Win32 and Linux. -- Cheers, Dr Gerard Hammond MacSOS Solutions Pty Ltd, 505/176 Glenmore Rd, Paddington, NSW, Australia [EMAIL PROTECTED] http://www.macsos.com.au It might look like I'm doing nothing, but at the cellular level I'm really quite busy. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Size of INDEX in database
Hi, Is there a SELECT call I can issue to the SQLite engine to determine the number of bytes that INDEXs occupy. -- Cheers, Dr Gerard Hammond PowerPC Mac the world's most advanced obsolete computer. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
Daniel Önnerby wrote: > So what you are saying is that opening a SQLite DB on a shared network > drive SHOULD work with multiple clients (if all servers and NFS-version > are updated to most recent version)? I have found that accessing a FileMaker Pro DB file, on a shared network drive, simultaneously from a Mac (via AFP over IP) and PC (via SMB), leads to an immediate corruption of the file. Nice. I hope the SQLIte db file doesn't suffer the same behaviour by assuming a single network protocol-level locking mechanism. -- Cheers, Dr Gerard Hammond MacSOS Solutions Pty Ltd, 505/176 Glenmore Rd, Paddington, NSW, Australia [EMAIL PROTECTED] http://www.macsos.com.au It might look like I'm doing nothing, but at the cellular level I'm really quite busy. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] suggest an index to make this faster
Hi Could somebody suggest an index(es) I could add to my sqlite v3 (REAL SQL database) to make this SELECT faster? SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t LEFT OUTER JOIN Category c on c.CategoryID=t.categoryID and AcctID in (12) and t.categoryID in (261, 262, 263, 264, 265, 266, 267, 268, 269) ORDER BY date_trans CREATE TABLE Category (CatParent Integer, Description varchar, CategoryID integer NOT NULL DEFAULT '0', CategoryAbsolutePath varchar, PRIMARY KEY(CategoryID)); CREATE TABLE Transn (GST double DEFAULT '0', AcctID integer, TransID integer NOT NULL, CategoryID integer, Total double DEFAULT '0', Description varchar, Date_Trans date, Notes varchar, NeedsAttention boolean DEFAULT 'False', Reconciled boolean DEFAULT 'False', GSTClaimed boolean DEFAULT 'False', HasSplitTrans boolean DEFAULT 'False', Currency varchar(10) DEFAULT 'AUD', CurrConverter float DEFAULT '1.00', ForeignCurrencyAmount double DEFAULT '0', locked boolean DEFAULT 'false', PRIMARY KEY(TransID)); TIA -- Cheers, Dr Gerard Hammond MacSOS Solutions Pty Ltd [EMAIL PROTECTED] http://www.macsos.com.au Proofread carefully to see if you any words out. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: suggest an index to make this faster
Thanks You Igor. As the PRIMARY KEY of the table Category is CategoryID, is it necessary to specifically create an index of a primary key? At 11:21 PM -0400 21/4/07, Igor Tandetnik wrote: Dr Gerard Hammond wrote: Could somebody suggest an index(es) I could add to my sqlite v3 (REAL SQL database) to make this SELECT faster? SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM Transn t LEFT OUTER JOIN Category c on c.CategoryID=t.categoryID and AcctID in (12) and t.categoryID in (261, 262, 263, 264, 265, 266, 267, 268, 269) ORDER BY date_trans An index on Category(CategoryID). Another one on Transn(AcctID) or Transn(AcctID, categoryID): the latter would be preferable over the former if you have many distinct values of categoryID for each AcctID. It may also help a little to replace the IN clause with 261<=t.categoryID and t.categoryID<=269 Good idea but... Unfortunately the t.categoryID will not be such a neat series like this one. Thanks! -- Cheers, Dr Gerard Hammond MacSOS Solutions Pty Ltd [EMAIL PROTECTED] http://www.macsos.com.au Proofread carefully to see if you any words out. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error: file is encrypted or is not a database
Hi Karl, There may be several. I don't know. I wrote one app for MacOS and Win32 clients called 'SyBrowser' (It was originally written to query Sybase db's but now does others as well) It is currently restricted to SQlite 2 db's http://www.macsos.com.au SyBrowser isn't freeware ;-( Am Sonntag, 17. Juli 2005 12:58 schrieb D. Richard Hipp: The Philips MP3 players use SQLite version 2.x. You probably downloaded version 3.x. Version 3.x will not read or write version 2.x database files. Try using version 2.8.16 instead of whatever version it is you downloaded. That's it. It works fine. Thank you. Is there any Graphical Frontend for SQLite available? -- Cheers, Dr Gerard Hammond Bioinformatic Analyst Garvan Institute of Medical Research, Sydney, Australia. Bioinformatics is the science of managing, mining and interpreting information from biological and clinical data, including sequences, structures, gene expression profiles, mass spectra and medical records.
Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i wrote.
At 9:29 AM +0200 16/8/05, Edwin Knoppert wrote: I only need a list what i need to correct like critical errors, not what to change like gui changes or similar. It's a freeware tool and not being released before it had a better test than i did. Mail me if you want to help. App consists from exe, hlp and sqlite3 dll. hope you'll look into the help as well. Here is an image: http://www.hellobasic.com/images/pbsdbms.jpg Thanks! Does it run under MacOS X. If so, then I'd be happy to test. -- Cheers, Dr Gerard Hammond Bioinformatic Analyst Garvan Institute of Medical Research, Sydney, Australia. Bioinformatics is the science of managing, mining, and interpreting information from biological and clinical data, including sequences, structures, gene expression profiles, mass spectra and medical records.
[sqlite] Date Difference - How do I subtract two dates
Hi, How do I subtract two dates and get the answer in minutes? -- Cheers, Dr Gerard Hammond Bioinformatic Analyst Garvan Institute of Medical Research, Sydney, Australia. If you're not part of the solution, you're part of the precipitate.