Logo Home    Page list

Four Little Queries #names #blanks #placedetails

Four Little Queries

Query Name
List Persons with Blank Names in the Address ListRMtrix_tiny_check.png
-- (a fault that may occur in a GEDCOM import).
List Persons with specified Surnames.
-- Example of creating a SQL View or Virtual Table and the explicit use of COLLATE NOCASE to override the RMNOCASE collation defined for certain fields and embedded in the RootsMagic application.

Lists Places having Place DetailsRMtrix_tiny_check.png

List of unused PlacesRMtrix_tiny_check.png

The queries above are included in the one file. Some SQLite managers can import a query file and some can also export a query file (e.g. SQLiteman does both). Others may require you to open the file with a text editor and copy/paste the query into the SQLite manager's query editor. With SQLiteman, you place the cursor anywhere in a query line and that is the one executed; SQLiteSpy requires you to highlight the selected command and execute using Ctrl+F9 rather than F9 which would run the whole listing. Each query must end with a semi-colon to demark it from the others.

Optionally, the queries can be expanded to include the CREATE VIEW command by deleting the semi-colon at the end of its line. When successfully executed, the query is embedded in the database as a virtual table which remains with the database file until it is dropped (deleted). Some SQLite managers allow you to save a query as a view from the user interface, putting a GUI in front an internal CREATE VIEW.