Since, and that's the problem, your selects will not display the expected results if you don't always cope with the " where x = (or ) '' or where x is (not) null" problem you will have got now.Īlso, traditional logic is different from SQL logic, and you must become aware of that phenomenon, too. This also implies two things: You should start your database experiences with data you intimately know, and/or you should always set the option "display Nulls" to "on" both measures will help with at least becoming aware there are problems. If you don't know databases at all, you will think that's the same thing, database experts will laugh about what I describe, but for database beginners, it's important information. Anyway, blank fields from the imported data were zero-length/empty strings, while the same for records I then created in SQLite "Expert" (fields I simply left blank, in newly created records) were/are Nulls. So the first problem described here may result from this 2-step setup. When I imported my csv data, I did that, as I said further down in this thread, in SQLiteBrowser 3/DB Browser for SQLite (they don't know how to name their tool, it's not 2 different tools here), then opened my databases in SLite "Expert" (see next post), since the latter's "Personal" edition doesn't allow for import or export (but you can do some select, then ^a, ^c, then treat the resulting csv clipboard by macros, for example for printing or for exporting the data into anything else). The web abounds of discussions if you should use null values or empty strings I won't add to add to that discussion but rather want to indicate some more beginners' traps caused around Null/empty values btw it's of interest that Oracle's and FileMaker's sql flavors which both try to do away with the Null problem both don't succeed at it, so switching over to them will not allow you to ignore what it's all about. In order to avoid lots of problems which, as the above use case has shown, are far from obvious and could have you get incomplete results without even discovering the problem.ĮDIT June 1, 2017: Title change see next post (if at least it was the other way round, I could memorize that better.) Which is double quotes for identifiers and single quotes for strings/literals Thus, it's absolutely necessary to write in the official syntax This works fine, except for my code "h" in column "c", since I also had another 1-single column called "h", and without the quotes, SQLite is unable to differentiate column names from values if they are identical, even if you would have thought that the "where c = h", by its syntax, must have told SQLite the first is a column and the second is a value, but no. "C" for Code, with 1-character codes in it, for several, different todo-categories, just 6 or 8 characters in all, all single ones, and so I wanted to have it neat and without sacrifying space, and so I called that "Code" column "C". You know ask why would anybody call many columns by single characters, and also many string values by single characters, to begin with? That's right, but I had just a few 1-character columns, incl. IF there is, SQLite will simply do not show the resulting records, no warning, no error message, nothing, so when you've got some other columns and your requests work fine in general, it'll be probably a lot of time before you realize SQLite will not display many wanted query results. IF there is not another column which by any chance is called V Will work fine and display all records where there is a "V" in column "T" Will work fine as long as there is no column (another column) which is called/homonym to somestring Select * from tablename where somecolumnname = somestring This thread does NOT intend to replicate the usual sql intros, but wishes to give additional practical advice for beginners those intros' authors did not think about.įirst, some quite obscure trap for beginners, stackoverflow helped me to find that out.įor many a command, not using quotes (single or double) will work fine, so you will not discover it's faulty syntax. (Previousoriginal titles: "SQLite, SQL, SQLite Expert", then "SQLite, SQL, SQLite "Expert" (ha, ha)", for lack of warnings when stacking up database updates which in combinations can get dangerous and which you would do one by one if you did them by hand)
0 Comments
Leave a Reply. |